How do I update a table with LINQ-to-SQL without having to delete all the existing records?
Lets say I have a simple table that only contains two columns:
MailingListUser
- PK ID (int) - FK UserID (int)
I have a method called UpdateMailList(IEnumerable
. <int
> 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?
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.DeleteAllOnSub开发者_运维技巧mit(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?
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();
To query users that need deleted, do:
var delUsers = from u in db.MailListUsers where !userKeys.Contains(u.UserKey) select 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;
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));
Again, don't know all the performance implications.
HTH.
精彩评论