Updating many-to-many relationships with LinqToSQL
If I had, for example, a Many-to-Many mapping table called "RolesToUsers" between the "Users" and "Roles" table, this is how I would 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 have used the SQL profiler once and it seems to generate very intelligent SQL - it will only discard rows that are no longer associated with the mapping relationship and only add rows that did not exist in the relationship yet. He's not blindly doing a complete cleaning and rebuilding of the relationship, as I thought.
The internet is surprisingly quiet on this matter, and the "LinqToSQL many-to-many" query basically just reveals articles on how the LinqToSQL data mapper "doesn't support" it very well.
How does everyone else update many-to-many with LinqToSQL?
source to share
An attitude is a collection of facts. Users
, Roles
and UsersToRoles
- all relationships, and it makes no logical sense to regard it as more "first class" than the other. What you are doing makes perfect sense, which is why it seems to work so well.
The ORM world handles the relational model even worse than SQL. In particular, it institutionalizes the fallacy that relational databases store collections of objects. They don't; they store a lot of facts. Many of these facts are about entities, which is why fallacy is so tempting. But many facts relate to many other concepts such as memberships, events, statuses, opinions, transactions, changes, comparisons, histories, etc.
Also consider:
- At some point, you may need to indicate when a user has accepted a particular role, and then perhaps whether that role is pending approval or has been temporarily canceled. I have found that almost all "relationships" that I want to express in the data model end up carrying a payload that exceeds the two foreign keys that make up the many-to-many relationship. If your OR mapping has been set up to hide many-to-many (as they usually do), you will find the entity transition quite painful in terms of code breaking.
- Relationships are often more complex than binary relationships. "Professor P. uses textbook T in class C." can be an irreducible triple link that is not easily hidden behind some attribute of the Professor object's collection.
- I just noticed that this issue came up as an SO question a few minutes ago. From the question that JPA 2.0 had to explicitly add support for ternary relationships (does it handle quaternary, etc. Relationships?) The question arises.
source to share
This may not be an answer to your question, but an explanation as to why "the internet is surprisingly quiet on the topic": I think most people (myself included) just update / remove / add individual n-to-n elements, and therefore never didn't have to ask yourself the question you have.
Just like you have two "user" and "role" tables, and to add / remove a role for a user.
Just curious: in which application do you need to update the entire mapping, like your example?
source to share