Categories
ado.net entity-framework sql sql-server

Updating Multiple Many-to-Many Relationships

I have two tables, Cars and Drivers, that are joined by a third table, CarDrivers, in a many-to-many relationship.

My UI allows the user to check any number of drivers associated with the current car. Each check indicates that a row should be entered into the CarDrivers table.

My question is: what’s the most efficient way to update those rows when the user submits the form?

I need to go through and add a row into CarDrivers for each item that was checked and delete one for each item that was unchecked, while leaving those that have not changed.

The only way I see is to go through each combination, one at a time, and add those that don’t already exist or remove those that need to be removed. Is there a slicker way?

I can use Entity Frameworks 4, ADO.NET, straight SQL queries or stored procedures.