Linq query assistance

Question

I have a screen in which the user can select which categories they want to subscribe to. They can select zero to many categories. These get stored in a UserCategory table, which has a userid and a category id.

So, if the user select 2 out of the 8 categories, I write two rows, with the category ids and the userid.

(He is now assigned to TWO categories)

However, if the user returns, and deselects one of the categories, and then adds 2 different categories, he is now assigned to 3 categories...

But I'm unsure of a good way to handle this in linq... Somehow, I need to do something like ... DELETE FROM UserCategories where userId = :UserId and CategoryId NOT IN the int[] of category ids passed in.... And then I need to INSERT INTO UserCategories ... the int[] category ids, but not the ones that already exist.

And I need to do it in Linq, using Entity Framework... Can someone assist?

Answer 1

The simplest solution might be to remove the user from all current categories and re-add to those that are still selected. While you are potentially removing and adding the same information again, it keeps the code simpler and simpler code is easier to maintain.

So you'd read the current information from the database and present that on the form. Then when the user hits "OK" clear current information in the database (keeping the copy in memory of course) and then add it back as though it was the first time through the code.