Introducing LINQ with XML and Databases - Querying the Database
(Page 2 of 4 )
Now that we have everything ready, we can begin to use LINQ to query the Adventure Works database. Let's begin with an easy task: querying the database to get every customer:
var customers = from c in db.Customers
select c;
This is made remarkably simple. It's just like querying the array in the last article, a similarity which is, of course, intended.
We now have access to each customer's information. We can access each of the properties defined in the Customer class we created earlier, which correspond to the database table's fields. For example, let's display the first and last names of each customer:
foreach (var c in customers)
{
Console.WriteLine("{0} {1}", c.FirstName, c.LastName);
}
Again, there are no surprises. LINQ behaves the same here with an SQL database as it did in the last article with an array of Person objects.
Since, above, we only use the first and last name of the customer, we could have narrowed down the properties we obtained from the database by using an anonymous type containing only the properties for the first and last names:
var customers = from c in db.Customers
select new { c.FirstName, c.LastName };
Of course, if we run the loop again, the exact same results are displayed.
This article is not intended to be a comprehensive, in-depth guide to LINQ to SQL, but we'll look at one final thing before moving on to LINQ to XML: associations. The Adventure Works database contains a table called SalesOrderHeader. This table contains general details about sales orders. (Details about the contents of orders are stored in the SalesOrderDetails table, with one entry per type of product ordered). Notice how the table contains a CustomerID field, which, of course, corresponds to a particular entry in the Customer table. So, each entry in the SalesOrderHeader corresponds to exactly one entry in the Customer table. Given an entry in the SalesOrderHeader table, we should be able to, for example, print the name of the customer involved.
Visual Studio makes this easy. Go to the LINQ to SQL Classes item we created earlier and drag the SalesOrderHeader onto the Object Relational Designer. A graphical representation of the table will appear, and so should an arrow between the Customer table and that SalesOrderHeader table. This arrow represents an association between the two tables. In this case, the association is built around the CustomerID field, which appears in both tables. If you double click the arrow, the Association Editor dialog will appear, showing the properties of the association. Customer is listed as the parent class, since the CustomerID is the primary key of this class, and SalesOrderHeader is listed as the child class. Visual Basic was able to automatically determine this relationship (though it is possible to manually add associations through the Designer's context menu).
Now, let's display the order number of each order, along with the customer's name. This is actually very easy:
var orders = from o in db.SalesOrderHeaders
select o;
foreach (var o in orders)
{
Console.WriteLine("Order Number: {0}", o.SalesOrderNumber);
Console.WriteLine("\tCustomer: {0} {1}", o.Customer.FirstName, o.Customer.LastName);
}
As you can see, there is nothing special about the query itself. We simply retrieve every element in the SalesOrderHeader table. There is also nothing complex in the loop where we print out the information. The corresponding entry in the Customer table is added as a property to the SalesOrderHeader object we pulled from the database, and we access the FirstName and LastName properties of the Customer object just as we did earlier. This is a very nice capability, especially considering that all we did was drag and drop a table.
Next: LINQ to XML >>
More .NET Articles
More By Peyton McCullough
