Geeks With Blogs
Andrew Siemer's Blog Enterprise Web Applications, ASP.NET MVC, C#, SQL Server, Architecture, & Writing
I felt the need to vent a little bit of frustration today as I spent an entire weekend trying my best to figure out how to get LINQ to SQL to support what I consider to be a fairly standard database design concept - many to many relationships.  In my case I have a table of Permissions and a table of Accounts.  Rather than storing a list of permissions in my Accounts table I simply wanted to store both Accounts and Permissions separately and then link them together with a linking table.  I don't think this could be any easier!  I drug my tables on to my design surface and verified that all the links were in place as they should be.  Built it.  All is good so far.  I then created an AccountRepository class where I have several ways to retrieve an account, a way to insert a new account, a way to save an account, and a way to delete an account.  Everything worked with the exception of saving an existing account.  Initially I thought this was due to my DataContext being different than the one that I used to initially load my Account.  I didn't want to have to use a single connection.  I am in a tiered environment so I want everything to be totally disconnected and not have to worry about who is doing what when.  So I went down the research path of trying to figure out how to write Detach() methods and how the Attach() methods work.  I tried to add a Timestamp column to all of the tables that I am working with.  I wrote methods for Detach.  I tried attaching everything individually.  I tried and tried and tried everything I read in every post on GOOGLE that had anything to do with all the errors I got.
System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

Point is...at the moment...LINQ does not allow you to have a "many to many" relationship defined in the way that I would like too.  By removing the relationship in the database designer - my updates worked.  This means that from a design point of view I will have to implement some constraints when adding and updating records.  But technically, even though the relationships are removed as far as LINQ is concerned - they are still in place on the database side.  The data is still safe.  LINQ just has me baffled for the time being! Posted on Tuesday, January 15, 2008 6:39 PM ASP.NET , C# , Architecture , LINQ | Back to top


Comments on this post: LINQ to SQL - many to many relationships not supported! (yet?)

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
I have the same problem. I consider to use stored procedures for this kind of stuf... did you found any solution yet?
Left by Boah on Jan 31, 2008 6:20 AM

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
What I have found that works really well is to remove all the relationships from all the entities(tables). And then manage the relationships myself. This works very well. As I am working in a thoroughly disconnected environment, I almost prefer this to having LINQ keep track of the state of all my objects with the connections left open the entire time. I am really accustomed to opening the connection, taking care of business, and moving on with my bad self! If you would like I can show you an example of this...let me know.
Left by Andrew Siemer on Feb 02, 2008 7:52 PM

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
just us Nhibernate
Left by Boca de PEz on Aug 01, 2008 3:09 AM

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
Hi Andrew,

Could you show a sql update statement for updating a many to many relationship?
Thx. JC
Left by JC on Jun 18, 2009 9:33 PM

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
The last time I checked LINQ to SQL did not support many to many operations! I don't think that this has been changed. For me I have found it most effective to remove all relationships in LINQ to SQL. This removes the complexity of many to many, one to many, and all other relationships. This means that you can use the short hand query concepts where L2S knows what object is a child of another object, but is also simplifies my design and workings with L2S. What this means is that I have to specify the joins in my LINQ queries...not a problem I have been doing that for years in SQL any ways! This allows me to stay fast with L2S and avoid features that are not supported by that framework. Works great!
Left by Andrew Siemer on Jun 19, 2009 12:50 PM

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
Try out PLINQO at http://plinqo.com. The PLINQO framework has added and enhanced many of the features of LINQ to SQL.
Left by Shannon Davidson on Aug 05, 2009 4:14 PM

# re: LINQ to SQL - many to many relationships not supported! (yet?)
Requesting Gravatar...
This really helped and fixed the problem. Other web references had you doing a lot of manual things. I didn’t see the ability to set the authentication to mixed mode or to enable remote connections in the installation dialogs. If it wasn’t there, it should have been.Thanks for the valuable post
Left by nederlandstalige casino’s on Dec 17, 2009 6:02 AM

Your comment:
 (will show your gravatar)


Copyright © Andrew Siemer - www.andrewsiemer.com | Powered by: GeeksWithBlogs.net