Updating Many-to-Many relationship with LinqToSQL
- by Noffie
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?
