Compare two collections and add / remove them from one to match

Question

I have two database tables that represent lists of items belonging to parent objects.

I need to periodically update table B to match table A

using linq, I get a set of identifiers in table A.

Now I need to add and remove rows from table B in order to combine them with A.

What is the most efficient way to accomplish this with linq and EF 4.1?

I can loop into collections A, and in this loop, the loop through B checks the record that matches the current element in the outer loop, adding a new element if no match is found ... however it seems like I would then need to repeat through B a second time so that remove any items that are not in A. This seems ineffective. Did I miss something?

Answer 1

var toRemove = tableB.Except(tableA); var toAdd = tableA.Except(tableB); 

where tableA and tableB are identifier lists.

And then it’s easiest to foreach() go through both lists of results and perform the necessary actions.