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.