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 https://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.
Comments
Anonymous
July 04, 2007
Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQAnonymous
July 04, 2007
Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQAnonymous
July 22, 2007
Pingback from http://oakleafblog.blogspot.com/2007/07/linq-update-for-week-of-july-16-2007.htmlAnonymous
August 13, 2007
There are several good new blogs from members of the Microsoft C# team. Nevertheless, the most importantAnonymous
August 21, 2007
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.Anonymous
August 23, 2007
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) MitsuAnonymous
September 08, 2007
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?Anonymous
September 10, 2007
Hi Dav, Could you send me a sample at mitsufu@microsoft.com ? Thanks, MitsuAnonymous
November 08, 2007
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! :) JoelAnonymous
November 08, 2007
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? JoelAnonymous
November 08, 2007
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... MitsuAnonymous
December 01, 2007
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!Anonymous
December 02, 2007
Just a short post to tell that I have replaced the source code with the VS2008 RTM version : http://blogs.msdn.com/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspxAnonymous
December 02, 2007
Just a short post to tell that I have replaced the source code with the VS2008 RTM version : http://blogsAnonymous
December 18, 2007
Now that ScottGu blogged about it , we have received a number of great feedback and questions. Anonymous
January 22, 2008
Thanks for this. It worked like a charm, and has made my life not-insignificantly easier.Anonymous
February 13, 2008
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 ! MihaiAnonymous
February 23, 2008
Added this to a list of LINQ TO SQL Tutorials, Articles and OpinionsAnonymous
February 23, 2008
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.Anonymous
March 08, 2008
I am working on a solution for adding add/remove support. I hope to publish it quickly.Anonymous
March 09, 2008
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.Anonymous
March 11, 2008
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?Anonymous
March 14, 2008
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); } // ..... }Anonymous
March 20, 2008
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.Anonymous
April 17, 2008
The comment has been removedAnonymous
April 21, 2008
The comment has been removedAnonymous
April 26, 2008
Mitsu Furata explores many-to-many relationships in LINQ to SQL in these two posts: How to implementAnonymous
April 28, 2008
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.Anonymous
May 11, 2008
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.Anonymous
May 19, 2008
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-directionaAnonymous
May 20, 2008
Thanks for this. This helped me a lot.Anonymous
May 22, 2008
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.Anonymous
November 13, 2008
Thanks for this. This helped me a lot.Anonymous
November 14, 2008
The comment has been removedAnonymous
November 15, 2008
@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.Anonymous
May 25, 2009
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 AndersAnonymous
May 26, 2009
The comment has been removedAnonymous
May 26, 2009
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 AndersAnonymous
May 27, 2009
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) MitsuAnonymous
July 29, 2009
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>>Anonymous
August 31, 2009
Hi Mitsu, May I use "ListSelector" In EDM ?Anonymous
August 31, 2009
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.Anonymous
September 02, 2009
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 wengAnonymous
September 02, 2009
Weng, To make a extension method accessible, you must add a "using" to the namespace containing the method definition.Anonymous
September 08, 2009
Mitsu I have changed namespace for this class , so they are in same namespace , May I send the code to you ? best wengAnonymous
September 08, 2009
And the method is still not accessible ?Anonymous
September 08, 2009
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_ListSelectorAnonymous
April 14, 2010
Hi, I'm trying to implement one to many and using LINQ for nested repeater. Any thougts?Anonymous
February 27, 2011
this relation ship is very very many chance.Anonymous
March 23, 2011
The comment has been removedAnonymous
April 08, 2011
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?