Ignore duplicate key insertion with Entity Framework

advertisements

I'm using ASP.NET MVC4 with Entity Framework Code First. I have a table called "users", with primary key "UserId". This table may have 200,000+ entries.

I need to insert another 50 users. I might do this like

foreach(User user in NewUsers){
    context.Add(user);
}
dbcontext.SaveChanges();

The problem is, one or more of those new users might already exist in the DB. If I add them and then try to save, it throws an error and none of the valid ones get added. I could modify the code to do this:

foreach(User user in NewUsers){
    if(dbcontext.Users.FirstOrDefault(u => u.UserId) == null)
    {
        dbcontext.Users.Add(user);
    }
}
dbcontext.SaveChanges();

which would work. The problem is, then it has to run a query 50 times on a 200,000+ entry table. So my question is, what is the most performance efficient method of inserting these users, ignoring any duplicates?


You can do this:

var newUserIDs = NewUsers.Select(u => u.UserId).Distinct().ToArray();
var usersInDb = dbcontext.Users.Where(u => newUserIDs.Contains(u.UserId))
                               .Select(u => u.UserId).ToArray();
var usersNotInDb = NewUsers.Where(u => !usersInDb.Contains(u.UserId));
foreach(User user in usersNotInDb){
    context.Add(user);
}

dbcontext.SaveChanges();

This will execute a single query in your database to find users which already exist, then filter them out of your NewUsers set.