Linq To Sql ManyToMany Update Existing Records

I have a many to many relationship between 2 entities. UserInfo and Campaign and the third table UserInfoCampaigns

On the front end a user should be able to update the user campaigns by using multiple checkboxes.

So I have:

var selectedCampaignsIds = new int[] {1,2,3}

var user = _extendedUserService.GetUser(new Guid(userId));

var newCampaigns =_campaignrepository.GetAllCampaignsByIds(selectedCampaignsIds);

I can assign newly selected campaigns by following:

foreach (var campaign in newCampaigns)
{
    var userInfoCampaign = new UserInfoCampaign { UserInfoId = user.Id, CampaignId = campaign.Id };
    _campaignrepository.SaveUserInfoCampaign(userInfoCampaign);
}

My question is how do I update UserInfo campaigns taking into consideration that a user might have or might not have e开发者_JAVA技巧xisting campaigns?

Do I delete all existing UserInfoCampaigns and then reassign with new ones? Or there is a more optimal way of updating the UserInfo campaigns without deleting them first using LINQ to SQL?

Thanks a lot.

Generally, you'd delete them and recreate them. That's the way I handle this kind of situation of many to many.

If you went for (Delete all) solution then you may consider that the ids will increase in your table in none continues form (I'm assuming that your PK is an int), if you dont care about that then delete them then re-add

if you want the identity to be in a continues form you may set a flag (IsDeleted) in your campaigns table that refers to the campaign as deleted, you set it to ture when user delete a campaign and set it false when user re-select it from the UI this insure less more records but take a more bit work in your code

Generally, we just let SQL handle it. You can specify ON DELETE on the relationships in SQL, you can CASCADE or SET DEFAULT or SET NULL depending on what behaviour you want.

Here's an example for ON DELETE CASCADE: http://www.codeproject.com/KB/cs/CascadingDeletes_cs.aspx