BolderImage Chicago Web Design Blog

Blog

LINQ Likes Foreign Keys
11/16/09

When developing applications, you’ll probably use LINQ mostly for accessing SQL/Server databases. LINQ to SQL provides an IntelliSense-friendly technique for writing database queries, and can return your data in an object hierarchy that models your application’s real world domain.

This graceful nesting of objects within objects is yours if, and only if, you’ve added foreign key relationships to the tables in your database.

To avoid confusion (and disappointment) let’s be precise: it’s not enough to have a field in one table that you can associate with a key in another. You need to actually create the foreign key relationship. SQL/Server Management Studio gives you a way to do that declaratively; or if you prefer, you can create the keys using T-SQL. One way or the other, you want to make sure they’re in place.

Can you write LINQ queries without the foreign keys being there? Yes, but you then take on the responsibility of drilling down your object hierarchy to get related objects. If you’ve taken the time to add the foreign keys, LINQ will do all the heavy lifting for you.

Let’s look at an example to see the difference foreign keys make.
The schema for our example is pretty straightforward:
• a Customer table holds a row for each customer, and has a primary key of CustomerID
• an Order table holds a row for each order, has a primary key of OrderID, and has a CustomerID field for the customer that placed the order
• an Item table holds a row for each item in an order, has a primary key of ItemID, and has an OrderID field for the order that contains the item.

For our first variation, we put no foreign keys on the tables.
Our goal is to produce a list of customers, their orders, and the items contained in the orders. The code to do that would look like this:

//    First, get the customers
IEnumerable<Customer2> customers = from c in dc.Customer2s
                select c;
foreach (Customer2 c in customers)
{
   Console.WriteLine(String.Format("Invoice for {0}", c.Name));
   //    For each customer, get the order.
   IEnumerable<Order2> orders = from o in dc.Order2s
                                where o.Customer2ID == c.Customer2ID
                                select o;

   foreach (Order2 o in orders)
   {
      Console.WriteLine(String.Format("- Order Date {0:d}", o.OrderDate));
      IEnumerable<Item2> items = from i in dc.Item2s
                                 where i.Order2ID == o.Order2ID
                                 select i;
      foreach (Item2 i in items)
         Console.WriteLine(String.Format(" *** Item: {0}, Price {1}", i.Description, i.Price));
   }
}

In this code block, we’ve queried for the customers; iterated through that list, and executed a second query for the orders; iterated through the orders list and executed a third query for the items. With a long list of customers and orders, that could be a lot of queries.

Now, you could have LINQ join the tables, and then iterate through the results building your own hierarchy of objects. This would require defining classes for your objects, a more complicated LINQ query, and then the code to populate the objects and nest them. I’ll leave that to the reader.

For the second variation, we tie the tables together with foreign keys. Aside from the benefits we get from referential integrity, we have a substantially smaller code block:

//    First, get the customers
IEnumerable<Customer1> Lcustomers = from c in dc.Customer1s
                                         select c;

   foreach (Customer1 c in Lcustomers)
   {
      Console.WriteLine(String.Format("Invoice for {0}", c.Name));
      foreach (Order1 o in c.Order1s)
      {
         Console.WriteLine(String.Format("- Order Date {0:d}", o.OrderDate));
         foreach (Item1 i in o.Item1s)
            Console.WriteLine(String.Format(" *** Item: {0}, Price {1}", i.Description,
               i.Price));

      }
}

To do the same job, we now use only one query, and have much less code. And, notice how the object hierarchy and dot notation make it easy to get to data in any object. Want an item’s price? customer.order.item.Price, and IntelliSense helps you all the way down.

Running these two bits of code produced identical results. Here’s a sample:

Invoices without Foreign Keys

Invoice for Fudd, Elmer
- Order Date 5/1/2009
*** Item: TV, Price 3500.0000
*** Item: TV Stand, Price 89.9500
---------------------------------------------
Invoice for Duck, Daffy
- Order Date 4/1/2009
*** Item: Shoes, Price 49.9500
*** Item: Purse, Price 79.9500
---------------------------------------------
- Order Date 5/4/2009
*** Item: Blouse, Price 24.9500
---------------------------------------------

Invoices without Foreign Keys

Invoice for Fudd, Elmer
- Order Date 5/1/2009
*** Item: TV, Price 3500.0000
*** Item: TV Stand, Price 89.9500
---------------------------------------------

Invoice for Duck, Daffy
- Order Date 4/1/2009
*** Item: Shoes, Price 49.9500
*** Item: Purse, Price 79.9500
---------------------------------------------
- Order Date 5/4/2009
*** Item: Blouse, Price 24.9500
---------------------------------------------

If the benefits of referential integrity haven’t been enough to persuade you to use foreign keys when building your database, consider the benefits you get in your LINQ queries. Less code, fewer database calls, and the data comes back in a hierarchy that matches the real world situation. Key it up!

- Ben Furino, .NET Programming Manager

Blog

LINQ Likes Foreign Keys
11/16/09

More Blog Articles

BOLDERIMAGE CHICAGO WEB DESIGN BLOG

Blog

LINQ Likes Foreign Keys11/16/09

More Blog Articles

LINQ Likes Foreign Keys
11/16/09