How to update a table using LINQ-to-SQL without having to delete all existing records?

Question

Let's say I have a simple table containing only two columns:

MailingListUser
- PK ID (int)
- FK UserID (int)

I have a method called .UpdateMailList(IEnumerable<int > userIDs)

How do I insert in LINQ inserts for user IDs that are present in the pass parameter but do not exist in db, delete those that are in db but no longer enter user IDs, and leave them that are already in db and in userIDs?

The user is provided with a list of checkboxes, and when he first loads, he has existing items selected for the maillist being checked.

Then the user can check and uncheck different users and click "save." As soon as this happens, I need to update the state of the database with the state of the checklist.

Here is what I am doing now:

        public void UpdateMailList(IEnumerable<int> userIDs)
        {
            using (MainDataContext db = new MainDataContext())
            {
                var existingUsers = (from a in db.MailListUsers
                                     select a);

                db.MailListUsers.DeleteAllOnSubmit(existingUsers);
                db.SubmitChanges();


                var newUsers = (from n in userIDs
                                select new MailListUser
                                {
                                    UserID = n
                                });

                db.MailListUsers.InsertAllOnSubmit(newUsers);
                db.SubmitChanges();
            }
        }
    }
}

Is there a better way than just deleting all the entries in the MailingListUser table and reinserting all the user ID values?

Answer 1

Something like this should work:

var existingUsers = from u in db.MailListUsers
                    select u;

var deletedUsers = from u in existingUsers
                   where !userIDs.Contains(u.UserID)
                   select u;

var newUsers = from n in userIDs
               let ids = from u in existingUsers
                         select u.UserID
               where !ids.Contains(n)
               select new MailListUser {
                    UserID = n
               };

db.MailListUsers.DeleteAllOnSubmit(deletedUsers);
db.MailListUsers.InsertAllOnSubmit(newUsers);
db.SubmitChanges();

Answer 2

To request users who need removal, do:

var delUsers = from u in db.MailListUsers where! userKeys.Contains (u.UserKey) select u;

db.MailListUsers.DeleteAllOnSubmit (delUsers);

, . , , :

var newUserKeys = from u userKeys                  (db.MailListUsers.Where(j = > j.UserKey == u.UserKey).Count() == 0)                  u;

100%, ; , , :

var newUserKeys = userKeys.Where(i = > ! existingKeys.Contains(i.UserKey));

, .

.