如何使用linqto - sql更新表，而不需要删除所有现有记录?

Lets say I have a simple table that only contains two columns:

假设我有一个简单的表，只包含两个列:

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

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

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

我有一个方法叫UpdateMailList(IEnumerable userIDs)。

How do I, in LINQ, make inserts for the userIDs that are present in the passed in parameter but don't exist in the db, delete the ones that are in the db but no longer in UserIDs, and leave the ones that are already in both the db and userIDs alone?

LINQ,我怎么让插入的用户id在传入参数但是不存在在db,删除那些db但不再用户id,并且把那些已经在数据库和用户id孤独?

A user is presented with a checkbox list, and when it first loads it has the existing members selected for the maillist checked.

一个用户被呈现为一个复选框列表，当它第一次加载时，它的现有成员会被选中，以进行检查。

The user can then check and uncheck various users and hit "save". Once this happens, I need to update the state of the database with the state of the checklist.

然后用户可以检查和取消不同的用户，点击“保存”。一旦发生这种情况，我需要使用检查表的状态更新数据库的状态。

Here is what I'm 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 simply deleting all entries in the MailingListUser table, and re-inserting all the userIDs values?

是否有比简单地删除MailingListUser表中的所有条目并重新插入所有userIDs值更好的方法?

2 个解决方案

#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();

#2

To query users that need deleted, do:

要查询需要删除的用户，请执行以下操作:

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

var delUsers =来自db中的u。用户界面包含(uuserkey)选择u;

db.MailListUsers.DeleteAllOnSubmit(delUsers);

I'm not sure the best way to do the new records. You could do this, but I'm not sure that's the most efficient:

我不确定做新记录的最佳方法。你可以这样做，但我不确定这是最有效的:

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

var newUserKeys =来自u中的userKeys，其中(db.MailListUsers)。(j = >。UserKey == u.UserKey).Count() = 0)选择u;

I'm not 100% sure that would work; alternatively, you could select all of the existing user keys, and then cross reference against this:

我不是百分之百地确定这行得通;或者，您可以选择所有现有的用户密钥，然后对其进行交叉引用:

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

var newUserKeys = userKeys。我在哪里(= > ! existingKeys.Contains(i.UserKey));

Again, don't know all the performance implications.

同样，不要知道所有的性能影响。

HTH.

HTH。

#1