Ignore duplicate key insert with Entity Framework

Question

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?

Answer 1

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.