Updating Many-to-Many relationship with LinqToSQL

Question

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?

Answer 1

This may not be an answer to your question, but and explanation why 'the internet is surprisingly quiet on the subject': I guess most people (including me) just update/delete/add single items of a n-to-n relationship, and therefore never had to ask themselves the question you have.

Like when you have two tables 'users' and 'roles' and the usecases are just to add/delete a role to/from the user.

Just beeing curious: In which application do you need to update the whole mapping, like your example?

Answer 2

A relation is a set of facts. Users, Roles and UsersToRoles are all relations, and it makes no logical sense to treat one as more "first-class" than another. What you are doing makes perfect sense, which is why is seems to work so nicely.

The ORM world butchers the relational model even worse than SQL does. In particular, it institutionalises the fallacy that relational databases store collections of objects. They don't; they store sets of facts. Many of those facts are about entities, which is why the fallacy is so alluring. But plenty of facts are about many other kinds of concepts, such as memberships, events, statuses, opinions, transactions, changes, comparisons, histories, and so on.

Also consider:

1. At some point, you may want to indicate when a user adopted a certain role, and then perhaps whether that role is pending approval, or temporarily revoked. I've found that just about all the "relationships" I want to express in a data model end up carrying a payload in excess of the two foreign keys that make up the many-to-many relationship. If your OR-mapping has been setup to hide the many-to-many (as they tend to do), you'll find the transition to an entity quite painful in terms of code breakages.
2. Often relationships are more complex than binary. "Professor P uses textbook T in class C." may be an irreducible ternary relationship that isn't so easy to hide behind some collection attribute of the Professor object.
   • I just noticed that precisely this issue popped up as a SO question a few minutes ago. It appears from the question that JPA 2.0 has had to explicitly add support for ternary relationships (does it handle quaternary, etc., relationships?).