05 June, 2008

Many-to-many relationships in linq to sql

Update: I wrote this post way back in 2008 to cover a widely-discussed limitation in LINQ to SQL. Since Entity Framework 4, LINQ to SQL is probably no longer the correct answer.

Entity Framework 4 is very easy to use and supports all the features I was looking for in LINQ to SQL – particularly the capability to hide a junction table from the object model that was present in contemporary versions of nHibernate. As Entity Framework has been Microsoft’s recommended data access solution for LINQ to relation scenarios for some time now, I recommend against using LINQ to SQL outside of legacy applications that would be too expensive to modify.

Original post: LINQ is a new component in .NET 3.5 that has a lot of .NET developers very excited. It provides a simple and flexible method of querying object-encapsulated data. The LINQ to SQL provider in particular has been getting a lot of attention since it can be used to query SQL Server databases using the same simple syntax that is used to query collections in memory – and furthermore can send updates made to the data back to the database. This feature, combined with very slick integration into Visual Studio 2008, provides a very strong motivation for developers to make the leap to .NET 3.5.

Unfortunatley, LINQ to SQL has a few limitations, the most obvious of which is the inability to directly handle the common representation of a many-to-many relationship. In this post I look at the issue and describe a simple way to circumvent it.

Let’s look at this simple example from a simplified version of the Northwind database:

Since LINQ defines only one-to-one and one-to-many relationships, you end up with two classes in your object model representing the two participating domain objects (Order and Product), and a third representing the relationship between them. Often we’ll want to write something like:-

foreach (Product p in order.Products) { ...

- as we would with a one-to-many relationship – but, since there isn’t a Products property of the Order object, we need to write:

foreach (Product p in order.Order_Details.Select(od => od.Product)) { ...

- which isn’t as clear as we’d like it to be.

In this simple case, we are able to alter a partial implementation of the Order class (right click on it in the design view and select “View Code”). To this class declaration we can add the following code:

public IEnumerable Products
 get { return Order_Details.Select(od => od.Product); }

This is ideal for most purposes. We can now write:-

foreach (Product p in order.Products)

- whenever we need to iterate through all the products in an order. We’ll need to add code to the Products class as well if we need to query all the orders that include a certain product, but that’s not too much hassle.

Beware of Select N+1 though. If you’ve got a collection of Orders and you want to iterate over it accessing their Products, you’ll definitely want to eager-load those Products!

No comments:

Post a Comment