忽略实体框架重复键插入 [英] Ignore duplicate key insert with Entity Framework
问题描述
我使用ASP.NET MVC4与实体框架代码优先。我有一个名为用户表,其中主键用户ID。该表可能有200,000项。
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.
我需要插入另一50个用户。我可以这样做像
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();
这是可行的。问题是,则它必须在一个200,000+入口表运行查询50次。所以我的问题是,什么是插入这些用户的大多数性能有效的方法, 忽略的的重复?
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?
推荐答案
您可以这样做:
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();
这将执行在数据库中单个查询要找到已经存在的用户,然后筛选出来的你的 newusers使用
设置。
This will execute a single query in your database to find users which already exist, then filter them out of your NewUsers
set.
这篇关于忽略实体框架重复键插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!