How to implement a many-to-many relationship using Linq to Sql ?
In this article, I will show one possible solution to implement many-to-many relationship using Linq to Sql.
Let's begin with some definitions and what Linq to Sql offers.
A “many to many” relationship between two entities defines a kind of bi-directional “one to many” relationship for each of the entities. In this very well-known example, a single product can belong to many orders and a single order can contain many products.
Implementing this in a physical model, you have to define an intermediate table regrouping couples of ids from each of the two entities.
Linq to Sql does not support definition of “many to many” relationships. This means that there is no specific relation attribute that allows defining such a complex relation. So you have to create as many entities as you physical model does and define your intermediate table on the code side.
You will also have no solution to define query expressions against the “many to many” model.
You will have to define two EntitySets to the intermediate entity. (order.Order_Details[] and product.Order_Details[]).
Accessing all the products belonging to an order will look like:
Foreach (Order_Detail od in order.Order_Details)
Product p = od.Product;
Of course we would love to write: od.Products which is the real functional definition!
Even if the “many to many” relationship will remain unknow from the Linq to Sql model (no attribute, no query), we can manually create some shortcuts to make od.Product to appear.
order.Order_Details is not the product collection that we are expecting but going from an Order_Details[n] to a product is easy since it’s just a “one to one” relationship. So we just need to change the type of the Order_Details collection. Of course we cannot cast but we can just make a projection to the Product property.
Imagine we add the following property to the Order class:
…
Public IEnumerable<Product> Products
{
return Order_Details.Select(od => od.Product);
}
…
It’s almost done !
Just now we can write :
Foreach (Product p in order.Products) …
We can optimize this solution. IEnumerable<T> defines an enumerator which is a kind of very low level collection. In our case, we are calling Select() from EntitySets (OrderDetails) which are more than an enumerator. EntitySet implements ICollection<T>, IList<T> and even IBindingList.
So even if this first solution is fine for a foreach statement, we are losing the direct access to the elements that we had with OrderDetails[i] and many other features (add/remove, notifications, etc).
The idea is to write a proxy class translating the EntitySet IList<T> interface and changing the item type using the same projection we had in the first solution.
Here is this proxy class definition and its constructor:
public class ListSelector<TSource, T> : IList<T>, IList
{
public ListSelector(IList<TSource> source, Func<TSource, T> selector)
{
this.source = source;
this.selector = selector;
projection = source.Select(selector);
}
...
We will translate an IList<TSource> implementation to IList<T>. The selector Func<TSource,T> will define how to retrieve a T item from a TSource item.
I will not show the whole implementation here (the full source code is attached in this post) but let’s see the interesting part.
Here is the projection of the indexer:
public T this[int index]
{
get { return selector(source[index]); }
set { throw new Exception("The method or operation is not implemented."); }
}
Because we can’t guess what could be adding or removing, the collection is defined as read-only.
public bool IsReadOnly
{
get { return true; }
}
We can now extend the Order class using this proxy:
public partial class Order
{
private ListSelector<Order_Detail, Product> products = null;
public ListSelector<Order_Detail, Product> Products
{
get
{
if (products == null)
products = this.Order_Details.AsListSelector(od => od.Product);
return products;
}
}
}
For an easiest use, you can notice that I have created an extension method on IList<T>.
We now still have the advantage of the foreach:
Foreach (Product p in order.Products) …
But also the IList advantages that we had with the EntitySet (order.Products[n]).
“Products” is now a regular subcollection of the order entity that can be used by code and also data binding:
We can also notice that having such a proxy does not create any extra collection.
The last improvement to this solution is the IBindingList implementation. If your source collection is also an IBindingList (more than IList), we can inherit from ListSelector to add these features.
Here is the type definition and its constructor:
public class BindingListSelector<TSource, T> : ListSelector<TSource, T>, IBindingList
{
public BindingListSelector(IBindingList source, Func<TSource, T> selector)
: base(source as IList<TSource>, selector)
{
sourceAsBindingList = source;
}
[Update: the source code is now provided for VS2008 RTM]
[Update: see part II for add/remove support http://blogs.msdn.com/mitsu/archive/2008/03/19/how-to-implement-a-many-to-many-relationship-using-linq-to-sql-part-ii-add-remove-support.aspx]
The full code is attached to this post and requires Visual Studio Orcas Beta 1. You will also need a .\sqlexpress instance installed.
