How to implement a many-to-many relationship using Linq to Sql ?

Quick LINQ link list

Linq in Action News — Thu, 05 Jul 2007 00:39:57 GMT

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

Quick LINQ link list

Fabrice's weblog — Thu, 05 Jul 2007 00:39:58 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Roger Jennings — Sun, 22 Jul 2007 17:36:47 GMT

Pingback from http://oakleafblog.blogspot.com/2007/07/linq-update-for-week-of-july-16-2007.html

Community Convergence XXIX

Charlie Calvert's Community Blog — Tue, 14 Aug 2007 03:44:17 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Neal Chamen — Wed, 22 Aug 2007 14:38:22 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Fri, 24 Aug 2007 02:09:03 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Dav Evans — Sun, 09 Sep 2007 10:41:15 GMT

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?

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Tue, 11 Sep 2007 04:03:37 GMT

Hi Dav,

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

Thanks,

Mitsu

re: How to implement a many-to-many relationship using Linq to Sql ?

Joel Ephroni — Fri, 09 Nov 2007 00:52:32 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Joel Ephroni — Fri, 09 Nov 2007 09:43:14 GMT

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:\...\ListSelector\ListSelector\Northwind.designer.cs 715 31 ListSelector

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

Joel

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Fri, 09 Nov 2007 13:27:13 GMT

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

.NET 3.5 released .. any news?

Anders Borum — Sat, 01 Dec 2007 23:52:39 GMT

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!

Source updated for VS2008 RTM: how to implement a many-to-many relationship using Linq to Sql ?

Mitsu's blog — Mon, 03 Dec 2007 02:58:26 GMT

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.aspx

Source updated for VS2008 RTM: how to implement a many-to-many relationship using Linq to Sql ?

Noticias externas — Mon, 03 Dec 2007 03:15:56 GMT

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

Answers to frequently asked ASP.NET Dynamic Data questions

David Ebbo's ASP.NET blog — Tue, 18 Dec 2007 21:51:31 GMT

Now that ScottGu blogged about it , we have received a number of great feedback and questions. 

re: How to implement a many-to-many relationship using Linq to Sql ?

Tim S — Tue, 22 Jan 2008 19:36:23 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Mihai Fagadar — Wed, 13 Feb 2008 16:56:18 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Chris Rock — Sat, 23 Feb 2008 17:32:12 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

El Guapo — Sat, 23 Feb 2008 18:10:31 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Sat, 08 Mar 2008 21:20:47 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

John — Sun, 09 Mar 2008 21:49:38 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

Assaf Stone — Wed, 12 Mar 2008 10:24:11 GMT

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?

re: How to implement a many-to-many relationship using Linq to Sql ?

John — Fri, 14 Mar 2008 19:05:36 GMT

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);

}

// .....

}

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Thu, 20 Mar 2008 19:19:40 GMT

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.

LINQ-To-SQL N:M Beziehungen

msdn Austria — Fri, 18 Apr 2008 14:50:39 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Athman — Mon, 21 Apr 2008 18:52:20 GMT

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

Implement a many-to-many relationship in LINQ to SQL

Hot Topics — Sat, 26 Apr 2008 21:06:12 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Fernando Arámburu — Mon, 28 Apr 2008 21:22:13 GMT

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.

How to implement a many-to-many relationship using Linq to Sql ? Part II : add/remove support (转)

江南白衣 — Sun, 11 May 2008 19:15:57 GMT

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.

Mitsu's blog : How to implement a many-to-many relationship using Linq to Sql ?

247Blogging — Tue, 20 May 2008 01:10:21 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Dennie — Wed, 21 May 2008 11:28:59 GMT

Thanks for this. This helped me a lot.

re: How to implement a many-to-many relationship using Linq to Sql ?

Arved Sandstrom — Fri, 23 May 2008 11:19:37 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

Srikanth — Fri, 14 Nov 2008 13:15:35 GMT

Thanks for this. This helped me a lot.

re: How to implement a many-to-many relationship using Linq to Sql ?

BACON — Sat, 15 Nov 2008 02:11:10 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Sat, 15 Nov 2008 16:45:19 GMT

@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.

re: How to implement a many-to-many relationship using Linq to Sql ?

Anders Madsen — Tue, 26 May 2009 12:05:25 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Wed, 27 May 2009 00:51:31 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

Anders Madsen — Wed, 27 May 2009 11:34:47 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Wed, 27 May 2009 21:17:20 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

Someone — Thu, 30 Jul 2009 12:38:15 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

weng3998@hotmail.com — Mon, 31 Aug 2009 17:50:49 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Mon, 31 Aug 2009 18:28:05 GMT

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.

re: How to implement a many-to-many relationship using Linq to Sql ?

weng3998@hotmail.com — Wed, 02 Sep 2009 17:45:19 GMT

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

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Thu, 03 Sep 2009 00:43:08 GMT

Weng,

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

re: How to implement a many-to-many relationship using Linq to Sql ?

weng3998@hotmail.com — Tue, 08 Sep 2009 19:09:50 GMT

Mitsu

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

best

weng

re: How to implement a many-to-many relationship using Linq to Sql ?

mitsu — Tue, 08 Sep 2009 19:27:31 GMT

And the method is still not accessible ?

re: How to implement a many-to-many relationship using Linq to Sql ?

weng3998@hotmail.com — Tue, 08 Sep 2009 19:39:08 GMT

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:\temp\d_ListSelector\d_ListSelector\Northwind.custom.cs 16 37 d_ListSelector