Compare two collections and add/remove from one to make them match
I have two database tables that represent lists of items that belong to parent entities.
I need to periodically update table B to match table A
using linq, I get a collection of the IDs in table A.
I now need to add and remove rows from table B to make it match up with A.
What is the most efficient way to accomplish this using linq and EF 4.1?
I can loop through the A collection and, within this loop, loop through B checking for a record that matches the current item in the outer loop, adding the new item if no match is found... however it seems like i would then need to loop through B a second time to remove any items that are not in A. This seems inefficient. Am I missing something?
var toRemove = tableB.Except(tableA); var toAdd = tableA.Except(tableB);
where tableA and tableB are lists of IDs.
And then it's easiest to foreach() trough both result lists and perform the necessary actions.