Updating Many-to-Many relationship with LinqToSQL

Posted by Noffie on Stack Overflow See other posts from Stack Overflow or by Noffie
Published on 2010-04-10T11:50:59Z Indexed on 2010/04/10 11:53 UTC
Read the original article Hit count: 232

Filed under:

linq-to-sql

|

many-to-many

|

.NET

If I had, for example, a Many-to-Many mapping table called "RolesToUsers" between a Users and an Roles table, here is how I do it:

// DataContext is db, usr is a User entity
// newUserRolesMappings is a collection with the desired new mappings, probably 
//   derived by looking at selections in a checkbox list of Roles on a User Edit page
db.RolesToUsers.DeleteAllOnSubmit(usr.RolesToUsers);
usr.RolesToUsers.Clear();
usr.RolesToUsers.AddRange(newUserRolesMappings);

I used the SQL profiler once, and this seems to generate very intelligent SQL - it will only drop the rows which are no longer in the mapping relationship, and only add rows which did not already exist in the relationship. It doesn't blindly do a complete clearing and re-construction of the relationship, as I thought it would.

The internet is surprisingly quiet on the subject, and the query "LinqToSQL many-to-many" mostly just turns up articles about how the LinqToSQL data mapper doesn't "support" it very well.

How does everyone else update many-to-many with LinqToSQL?

© Stack Overflow or respective owner

Related posts about linq-to-sql

how can i page my linq to sql result set

as seen on Stack Overflow - Search for 'Stack Overflow'
Remembering MySQL could use the instruction "limit" to indicate where I was starting my result set and how many wanted to have included. Select * FROM Users Limit [start], [Length] How can I do this in LinqToSql >>> More
LINQ to SQL and missing Many to Many EntityRefs

as seen on West-Wind - Search for 'West-Wind'
Ran into an odd behavior today with a many to many mapping of one of my tables in LINQ to SQL. Many to many mappings aren’t transparent in LINQ to SQL and it maps the link table the same way the SQL schema has it when creating one. In other words LINQ to SQL isn’t smart about many to many mappings… >>> More
Comparing LINQ to SQL vs the classic SqlCommand

as seen on Dot net Slackers - Search for 'Dot net Slackers'
tweetmeme_url = 'http://alpascual.com/blog/comparing-linq-to-sql-vs-the-classic-sqlcommand/';tweetmeme_source = 'alpascual';When you are coming from using SqlCommand and SqlConnection is difficult to move to another library for your database needs. For those people still in the limbo to make the decision… >>> More
Comparing LINQ to SQL vs the classic SqlCommand

as seen on Dot net Slackers - Search for 'Dot net Slackers'
tweetmeme_url = 'http://alpascual.com/blog/comparing-linq-to-sql-vs-the-classic-sqlcommand/';tweetmeme_source = 'alpascual';When you are coming from using SqlCommand and SqlConnection is difficult to move to another library for your database needs. For those people still in the limbo to make the decision… >>> More
LINQ to SQL Profiler

as seen on Dot net Slackers - Search for 'Dot net Slackers'
In this article we will be taking a look at the new LINQ to SQL Profiler from HibernatingRhinos. This tool gives you a view into the goings on of LINQ to SQL. Not only does it allow you to see the SQL that is generated by your LINQ queries but it also shows you information about your connections… >>> More

Related posts about many-to-many

Self referencing symmetrical Hibernate Map Table using @ManyToMany

as seen on Stack Overflow - Search for 'Stack Overflow'
I have the following class public class ElementBean { private String link; private Set<ElementBean> connections; } I need to create a map table where elements are mapped to each other in a many-to-many symmetrical relationship. @ManyToMany(targetEntity=ElementBean.class) @JoinTable( … >>> More
Django ManyToMany join query

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm sure this is really simple, but I can't for the life of me find any documentation explaining how to do this. How do I get the results of a ManyToMany field inside a join as opposed to doing this: {% for tag in article.tags.all %} Which results in an extra query? What I'd like to do is fetch… >>> More
django ManyToMany through help

as seen on Stack Overflow - Search for 'Stack Overflow'
Hay I've got a question about relationships. I want to Users to have Friendships. So a User can be a friend with another User. I'm assuming i'll need to use the ManyToManyField, through a Friendship table. But i cannot get it to work. Any ideas? Here are my models. class User(models.Model): … >>> More
Limiting choices from an intermediary ManyToMany junction table in Django

as seen on Stack Overflow - Search for 'Stack Overflow'
Background I've created three Django models—Inventory, SalesOrder, and Invoice—to model items in inventory, sales orders for those items, and invoices for a particular sales order. Each sales order can have multiple items, so I've used an intermediary junction table—SalesOrderItems—using the through… >>> More
What's best practice to check if an object is part of a ManyToMany relationship in Django

as seen on Stack Overflow - Search for 'Stack Overflow'
from an instance of Site with a ManyToMany relationship to Kiosk, i'd like to check if a Kiosk object is part of the relationship. I could do self.apps.get(id=app_id).exists() and check if True or self.apps.get(id=app_id) and catch the ObjectDoesNotExist error or self.apps.filter(id=app_id)… >>> More