Introduction to LINQ - Page 4
       by Granville Barnett  |  15 September 2007

In the previous page, you learned how to use LINQ with SQL. In this page, let's learn how to do more with LINQ using Visual Studio.

Tools
As we have seen, defining entities is simply a case of mapping an attributed type to a table. Although this process is very straightforward it can be time-consuming, especially when you have many tables in your database schema with many relationships. For this reason the LINQ May CTP includes two tools, one is a designer hosted in Visual Studio 2005 (DLINQ objects) and the other is a command line utility, SqlMetal. We will take a quick look at using the DLINQ objects item to create a conceptual view of our database schema.

For Visual Studio 2008 information, click here to see how the following instructions may be different.

First go into Visual Studio 2005 and create a new LINQ Console Application, when the solution has been created add a DLINQObjects item to the solution. At the moment you will see a blank canvas, drag the tables of your database from the server explorer window onto the canvas – you have just created the conceptual view of your database schema.

The main advantage of using the designer is that you are presented with an entity diagram (as shown in the image on the previous page) describing your conceptual model, however you will find the designer becomes very slow (it’s a CTP remember!) when creating a conceptual view for a large database schema. For that reason I recommend you use the SqlMetal command line utility if that is the case.

Querying Entities
Just like we can query in memory collections, we can also query entities in our conceptual database model. To interact with our conceptual database model we need to create a DataContext – this is a very important type in the System.Data.DLinq namespace. The DataContext object is in charge of converting rows to objects and vice versa when interacting with our conceptual model, a DataContext object takes a connection string, or any type that implements IDbConnection as an argument (e.g. SqlConnection). Here is the code for getting all book titles in the database:

// ...

BooksDataContext db = new BooksDataContext(_conn);

IEnumerable<Book> query = from b
                        in db.Books
                        select b;
 

foreach(Book item in query)

{

Console.WriteLine(item.Title);

}

// ...

This code can be associated with the SQL statement:

Select * From Books

Because we only want to select the Title property of the Book entity we can explicitly define this by returning a new anonymous type in our query which comprises of just the Title property of the Book entity.

Because we do not know the type of an anonymous type we can use the new variant type in C# 3.0. The type of a variant is inferred by its value, this allows us to use anonymous types very easily. Below we create a query variable whose type is inferred by the anonymous type passed back as a result of the query.

Anonymous types are types which are created at run time; of what type we do not know, however, the type created has CLR type safe properties. This code will pass back an anonymous type with a single property Title which is of type string:

// ...

var query = from b
          in db.Books

            select new {b.Title};

 

foreach(var item in query)

{

Console.WriteLine(item.Title);

}

// ...

Querying Related Entities
Next we will look at how we query related entities and look at the SQL generated by the DataContext to enable these queries. Because we defined the relationships between our entities using EntityRef<TEntity>, and EntitySet<TEntity> we can access related entities by using dot notation just like we would do to access methods, or properties of a normal type. The following query gets the Title of a Book and that book’s associated PublisherName.

// ...

var books = from b

            in db.Books select

            new {b.Title, b.Publisher.PublisherName};

 

foreach(var book in books)

{

Console.WriteLine("Title: {0}

Publisher: {1}", book.Title, item.PublisherName);

}

// ...

We can access the PublisherName property of the Publisher entity from the Book entity as we have defined the Book entity as being the parent in the relationship to the Publisher entity.

For our final query we will get all the authors associated with each Book. Because there is a set of Author types associated with a Book we will need to create an inner loop using a foreach statement to iterate through the authors associated with any particular Book. The queries required to achieve this are:

var books = from b
          in db.Books
          select new {b.Title, b.Publisher.PublisherName, b.BookID};

 

foreach(var book in books)

{

Console.WriteLine("Title: {0} Publisher: {1}", book.Title, book.PublisherName);
 

var authors = from a
            in db.Authors

              where a.BookID == book.BookID

              select new {a.AuthorName};

}

 

foreach(var author in authors)

{

Console.WriteLine(author.AuthorName);

}

Summary
LINQ provides a simple set of standard operators to query in-memory collections as well as entities. LINQ for SQL allows us to create DAL’s quickly but more importantly they are more flexible and robust than the common approach we would take now as demonstrated in the first code snippet. Hopefully the code examples we have gone through in this article will provoke you into trying LINQ out for yourself!

If you have any questions, please contact me or post on the forums.

Granville Barnett Explorations in Programming

1 | 2 | 3 | 4