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.

ListSelector.zip

Comments (51)

  1. Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQ

  2. Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQ

  3. There are several good new blogs from members of the Microsoft C# team. Nevertheless, the most important

  4. Neal Chamen says:

    Hi,

    I have downloaded the code from your article as I thought it looked a perfect solution for something I needed to do.

    Unfortunately, although my code builds and the intellisense works, I get an error when attempting a foreach iteration: "Binding Error: Member ‘Section.Questions’ is not a mapped member of ‘Section’ (in my code, Section and Question are the equivalent of Order and Product in yours).

    I also have the problem that after referencing Section.Questions in code, I often find Visual Studio crashes! I have had to write code in Notepad and paste it in. I am using Visual Studio 2008 Beta 2.

    Any thoughts much appreciated.

  5. mitsu says:

    Hi Neal,

    What UI technology are you using ? (web, winforms, wpf). Could you send me Section and Question classes by email ? (including partial definition adding .Questions property)

    Mitsu

  6. Dav Evans says:

    Hi,

    I’m having the same issue as Neal. When ever I reference a collection of type listSelector Visual Studio 2008 Beta crashes. It happens when I try and get intellisence from the collection to get a property such as ‘count’ or a methos like ‘Add’ from the collection. Pasting in from notepad only sometimes works for me. Im using ASP.NET on Windows Vista. The ListSelector property is sitting in a partial class.

    Any ideas?

  7. mitsu says:

    Hi Dav,

    Could you send me a sample at mitsufu@microsoft.com ?

    Thanks,

    Mitsu

  8. Joel Ephroni says:

    Mitsu,

    I am trying to use your excellent code but I am using 2008 beta2 and apparently, it is very broken in beta2. Do perhapshave a version that will work with beta2 that you can share with us? 🙂 Thank you for sharing your knowledge! 🙂

    Joel

  9. Joel Ephroni says:

    Specifically, when I try to run the sample code, I get Error

    "The type or namespace name ‘TableAttribute’ does not exist in the namespace ‘System.Data.Linq’ (are you missing an assembly reference?) C:…ListSelectorListSelectorNorthwind.designer.cs 715 31 ListSelector

    Do you have a thought about why this might be happening?

    Joel

  10. mitsu says:

    Hi,

    I think some things have changed in VS2008 latest builds (RC). TableAttribute is now available in System.Data.Linq.Mapping namespace.

    The simplest way is to recreate the dbml file, dropping the same tables.

    I will provide an updated source when VS gets RTM.

    A few weeks to wait…

    Mitsu

  11. Anders Borum says:

    Hi!

    I bet you’ve got a copy of VS 2008 RTM along with .NET 3.5. Any chance you’ve aligned the code in this blog entry with the final release yet?

    Thanks in advance!

  12. Just a short post to tell that I have replaced the source code with the VS2008 RTM version : http://blogs

  13. Now that ScottGu blogged about it , we have received a number of great feedback and questions.&#160;

  14. Tim S says:

    Thanks for this. It worked like a charm, and has made my life not-insignificantly easier.

  15. Mihai Fagadar says:

    Hi Mitsu,

    Thanks for the post, it has been very useful.

    I have one question: If you remove a Product from an Order, how can you ensure that the corresponding Order_Detail is also removed ?

    Thanks !

    Mihai

  16. Chris Rock says:

    Added this to a list of LINQ TO SQL Tutorials, Articles and Opinions

  17. El Guapo says:

    Yes I am also interested in how to extend this for functionality to add or remove items from the relationship. It is very nice for read-only collection, but that’s quite limited. Thanks.

  18. mitsu says:

    I am working on a solution for adding add/remove support. I hope to publish it quickly.

  19. John says:

    Your excellent extension is not terribly useful in a real application without the corresponding support for add/remove.  I can imagine a couple of ways to attack this, but I’m sure your solution will be more optimal.

    Any idea on when you might publish that?

    Thanks.  This is a critically important bridge until the EF gets here.

  20. Assaf Stone says:

    The ListSelector is a great idea. It is the best M:M solution I found so far.

    However, its inability to handle inserts and deletes is a big problem for me.

    Have you found any way to solve this yet?

  21. John says:

    I have a poor man’s insert that seems to work, although I haven’t used it too much other than some basic testing.  (I tried several options to delete, and couldn’t get anything to work – always get null key errors, even working from both directions in coordination).

    Below is a basic insert that seems to work.  Just call the method on the "parent" or containing class, passing in the contained object, and then call update on the context.  (See the Add…() method below)

    public partial class Case

    {

         // ….

       // Many-to-Many wrapping

       // Many thanks to Mitsu of MS, see his blog for List Selector (http://blogs.msdn.com/mitsu/)

       private ListSelector<CaseEvidence, Evidence> evidence = null;

        public ListSelector<CaseEvidence, Evidence> Evidence

           {

               get

               {

                   if (evidence == null)

                       evidence = this.CaseEvidences.AsListSelector(ce => ce.Evidence);

                   return evidence;

               }

           }

           public void AddEvidence(Evidence e)

           {

               CaseEvidence ce = new CaseEvidence();

               ce.Evidence = e;

               ce.Case = this;

               this.CaseEvidences.Add(ce);

           }

           // …..

    }

  22. mitsu says:

    Ok,

    Here is a possible solution 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

    I think it’s extensible enough to answer to many scenarios.

  23. msdn Austria says:

    n:m Beziehungen kommen in den meisten großen Datenbanken vor. Doch wie kann diese Beziehung mit LINQ-To-SQL

  24. Athman says:

    just want to ask

    why if we make a relation database we cant add to the table

    as we see here in the picture we saw the 3 tables is fixed which mean no more rows can be added

    how it can be fix

  25. Hot Topics says:

    Mitsu Furata explores many-to-many relationships in LINQ to SQL in these two posts: How to implement

  26. Fernando Arámburu says:

    Sorry but I´m misunderstanding something or the Order / Order Detail and Product is not a many to many relationship at all? There are two one to many relations  but no many to many…

    A good example would be one where the object in the middle of both many sides should not be modeled in object oriented programming. A good one… maybe a jobPost and a tag where one job are related with a collection of tags and also a tag is related with the collection of jobs that contains that tag.

  27. 江南白衣 says:

    In my previous post (http://blogs.msdn.com/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx), I had proposed a simple solution for implementing many-to-many relationships using Linq to Sql.

  28. 247Blogging says:

    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-directiona

  29. Dennie says:

    Thanks for this. This helped me a lot.

  30. Very nice stuff, much obliged. A few hours of tinkering to adapt to my database schema, and my second DataGridView started showing data pulled using your mechanism. Once I’ve got that foot in the door I’m happy.

  31. Srikanth says:

    Thanks for this. This helped me a lot.

  32. BACON says:

    I’m not understanding what exactly the ListSelector class offers that couldn’t be accomplished by implementing the Order class as follows:

    <pre>public partial class Order

    {

    private IList<Product> products = null;

    public IList<Product> Products

    {

    get

    {

    if (products == null)

    products = Order_Details.Select(detail => detail.Product).ToList().AsReadOnly();

    return products;

    }

    }

    }</pre>

    That would address the "we are losing the direct access to the elements that we had with OrderDetails[i] and many other features (add/remove, notifications, etc)" issue, wouldn’t it?

    Is it that, unlike returning a List<> object, a ListSelector offers delayed loading/deferred execution?  Although, I suppose even then the difference isn’t that great because my implementation at least uses lazy initialization; it’s not like the contents of the Products property will be retrieved when the Order is instantiated/loaded.

  33. mitsu says:

    @BACON:

    Visually you will get the same but it’s unusable.

    You will create a new collection at each time you will access the property !!!

    call order.Products[0] then order.Product[1] and you will get two products belonging to two different collections…

    The goal of the ListSelector class is to create a proxy over a unique collection, changing the item accessor. This allows not to recreate any extra collection while changing the element type of the resulting list.

  34. Anders Madsen says:

    I tried to use this pattern, but it makes Linq generate highly unusual SQL. The problem boils down to this:

    var order = db.Orders.First();

    foreach (var product in order.Order_Details.Select(x => x.Product)) {

    //…

    }

    I would expect this to generate two SQL statements, one to find the order, and the an inner join between Order_Details and Product. However, when done like the above Linq to SQL generates one statement to find the order, on to find all entries in Order_Details, and then one statement FOR EACH matching row in Products.

    I am unable to understand why this is happening, but I can clearly see it if I output the generated queries by using the datacontext’s Log property and the debug console.

    Strangely enough, if I do it like this:

    var q = db.Orders.Where(o => o.OrderID == 10248);

    var order = q.First();

    var products = q.SelectMany(o => o.Order_Details, (o, d) => d.Product);

    foreach (var product in products) {

    // …

    }

    I get the expected SQL. Can you shed any light on why I am getting this strange behaivor?

    — Sincerely Anders

  35. mitsu says:

    Hi Anders,

    I will try to explain. The SelectMany() is equivalent to:

    from od in order.Order_Details

    from p in od.Product

    select p;

    using this syntax, we are building a single query that will be analyzed by the Linq to Sql engine to generate a sql join.

    if you are writing:

    var product in order.Order_Details.Select()

    the "order.Order_Details" is not part of the Linq to Sql expression. It’s a classical Linq to object syntax that will raise the lazy loading system.

    I don’t know what you really want to query but you could even make just one query with something like:

    from o in

     db.Orders.Where(o => o.OrderID == 10248).Take(1)

    from od in order.Order_Details

    from p in od.Product

    select p;

    Take(1) returns an enumeration of 1 element which is different from First() which is returning the element itself >> no deferred execution.

  36. Anders Madsen says:

    Hi Mitsu,and thanks for the explanation!

    But perhaps I should try to explain a bit better. What I am trying to do is, to define a property on an object that can be used to iterate over a many-to-many relation, like the pattern you are describing in the post.

    With Northwind as an example, I am trying to define a property on an Order that returns a list of the products in it, basically like the one you have defined above:

    Public IEnumerable<Product> Products

    {

       return Order_Details.Select(od => od.Product);

    }

    However, as I described in my first post this particular statement doesn’t generate the SQL I would expect. I think I can follow your explanation, but I am unsure how to procede from here.

    Is the only way to have Linq generate inner join SQL statements to start fromv the datacontext each time? If so, the choice is simple – either use one global datacontext or a lot of locals and a lot of messing with the attach method. Neither options seem super apealing to me.

    But just to make sure, if I have an Order object from somewhere (fx. First()) the only way to define a property on it that generates a proper inner join sql statement is something like this:

    public IEnumerable<Product> Products2

    {

       get

       {

           var dc = new NorthwindDataContext();

           return dc.Order_Details

             .Where(x => x.OrderID == this.OrderID)

             .Select(x => x.Product);

       }

    }

    I just seems strange to me that the EntitySet doesn’t understand to do this by itself, but maybe thats just me…

    — Sincerely Anders

  37. mitsu says:

    I see,

    We try not to mix the model and the way we are loading data. For a single model you could have different ways to load data depending where you are in your application. You can just solve the model you want by creating properties and playing with EntitySets and then use LoadOptions to define how you want the data to be retrieved. (see DataContext.LoadOptions and DeferredLoadingEnabled)

    Mitsu

  38. Someone says:

    Nice technique, but for our use, there is one problem: the use of "Singleton<NorthwindDataContext>.Default". That will connect to the default database. In our case, the many-to-many mapping is in a different database.

    I will sort-of fix this by passing the data context to various accessors, and by changing the single-item cache to a Dictionary<DBDataContext, ListSelector<Order_Detail, Product>>

  39. weng3998@hotmail.com says:

    Hi Mitsu, May I use "ListSelector" In EDM ?

  40. mitsu says:

    Weng, two answers:

    1- EDM allows to map a many-to-many relationship. So you don’t need to use this code.

    2- If you do not map the relationship inside EDM, you can use the ListSelector. Actually, the ListSelector can be used to create n-n relations between any kind of collections, even just memory collections.

  41. weng3998@hotmail.com says:

    Thanks  Mitsu ,

    I want use ListSelector in   many to many with payload, even no foreign key in  database .

    When I add ListSelector.cs  as exist item ,  the “AsListSelector” is not apeared.

    That means  this.Order_Details.AsListSelector(od => od.Product) is not right .   I don’t know how can let AsListSelector for Order_Details.

    In LINQ to SQL , we use  EntitSet , in EDM we use EntityCollection.  

    The same operation for LINQ to SQL ,I changed nothing ,it is useful . But for EDM not useful .  Can you give me some help .  

    qian weng

  42. mitsu says:

    Weng,

    To make a extension method accessible, you must add a "using" to the namespace containing the method definition.

  43. weng3998@hotmail.com says:

    Mitsu

          I have changed namespace  for this class , so  they are in same namespace , May I send the code to you ?

    best

    weng

  44. mitsu says:

    And the method is still not accessible ?

  45. weng3998@hotmail.com says:

    Mitsu

    products = this.Order_Details.AsListSelector(od => od.Product);

    the error message

    Error 1 ‘d_ListSelector.Order’ does not contain a definition for ‘Order_Details’ and no extension method ‘Order_Details’ accepting a first argument of type ‘d_ListSelector.Order’ could be found (are you missing a using directive or an assembly reference?) C:tempd_ListSelectord_ListSelectorNorthwind.custom.cs 16 37 d_ListSelector

  46. Peter says:

    Hi,

    I’m trying to implement one to many and using LINQ for nested repeater. Any thougts?

  47. kk says:

    this relation ship is very very many  chance.

  48. Blake Niemyjski says:

    CodeSmith Generator's PLINQO (Extends Linq-to-SQL) templates support Many-to-Many relationships out of the box (http://www.codesmithtools.com/…/manytomany).

  49. Hunter Tarrant says:

    Thanks for the blog post, it was very informative.

    Is there any way to make the property available on the client side of a web service? On the server portion, it works great, but the client side cannot see the property, and I would like to be able to bind to it inside of a DataGrid.

    Is it even possible, or should I be looking for a different sort of solution?