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!