SQL EntityFramework，不存在则插入，否则更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6966207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EntityFramework, Insert if not exist, otherwise update
提问by Poul K. S?rensen
I'm having a Entity-Set Countries, reflecting a database table '<'char(2),char(3),nvarchar(50> in my database.
我有一个实体集国家，在我的数据库中反映了一个数据库表 '<'char(2),char(3),nvarchar(50>)。
Im having a parser that returns a Country[] array of parsed countries, and is having issues with getting it updated in the right way. What i want is: Take the array of countries, for those countries not already in the database insert them, and those existing update if any fields is different. How can this be done?
我有一个解析器，它返回一个 Country[] 已解析国家/地区的数组，并且在以正确的方式更新它时遇到问题。我想要的是：获取国家/地区数组，对于那些尚未在数据库中的国家/地区，插入它们，如果有任何字段不同，则插入现有的更新。如何才能做到这一点？
void Method(object sender, DocumentLoadedEvent e)
{
var data = e.ParsedData as Country[];
using(var db = new DataContractEntities)
{
//Code missing
}
}
I was thinking something like
我在想像
for(var c in data.Except(db.Countries)) but it wount work as it compares on wronge fields.
Hope anyone have had this issues before, and have a solution for me. If i cant use the Country object and insert/update an array of them easy, i dont see much benefict of using the framework, as from performers i think its faster to write a custom sql script that inserts them instead of ect checking if an country is already in the database before inserting?
希望有人以前遇到过这个问题，并为我提供解决方案。如果我不能使用 Country 对象并轻松插入/更新它们的数组，我看不出使用该框架有多大好处，因为从表演者那里，我认为编写插入它们的自定义 sql 脚本会更快，而不是检查一个国家/地区插入前是否已经在数据库中？
Solution
解决方案
See answer of post instead.
请参阅帖子的答案。
I added override equals to my country class:
我添加了覆盖等于我的国家/地区类：
public partial class Country
{
public override bool Equals(object obj)
{
if (obj is Country)
{
var country = obj as Country;
return this.CountryTreeLetter.Equals(country.CountryTreeLetter);
}
return false;
}
public override int GetHashCode()
{
int hash = 13;
hash = hash * 7 + (int)CountryTreeLetter[0];
hash = hash * 7 + (int)CountryTreeLetter[1];
hash = hash * 7 + (int)CountryTreeLetter[2];
return hash;
}
}
and then did:
然后做了：
var data = e.ParsedData as Country[];
using (var db = new entities())
{
foreach (var item in data.Except(db.Countries))
{
db.AddToCountries(item);
}
db.SaveChanges();
}
回答by Slauma
I would do it straightforward:
我会很简单：
void Method(object sender, DocumentLoadedEvent e)
{
var data = e.ParsedData as Country[];
using(var db = new DataContractEntities)
{
foreach(var country in data)
{
var countryInDb = db.Countries
.Where(c => c.Name == country.Name) // or whatever your key is
.SingleOrDefault();
if (countryInDb != null)
db.Countries.ApplyCurrentValues(country);
else
db.Countries.AddObject(country);
}
db.SaveChanges();
}
}
I don't know how often your application must run this or how many countries your world has. But I have the feeling that this is nothing where you must think about sophisticated performance optimizations.
我不知道你的应用程序必须多久运行一次，或者你的世界有多少个国家。但是我觉得这不是您必须考虑复杂的性能优化的地方。
Edit
编辑
Alternative approach which would issue only one query:
仅发出一个查询的替代方法：
void Method(object sender, DocumentLoadedEvent e)
{
var data = e.ParsedData as Country[];
using(var db = new DataContractEntities)
{
var names = data.Select(c => c.Name);
var countriesInDb = db.Countries
.Where(c => names.Contains(c.Name))
.ToList(); // single DB query
foreach(var country in data)
{
var countryInDb = countriesInDb
.SingleOrDefault(c => c.Name == country.Name); // runs in memory
if (countryInDb != null)
db.Countries.ApplyCurrentValues(country);
else
db.Countries.AddObject(country);
}
db.SaveChanges();
}
}
回答by Gábor
The modern form, using later EF versions would be:
使用更高版本的 EF 版本的现代形式是：
context.Entry(record).State = (AlreadyExists ? EntityState.Modified : EntityState.Added);
context.SaveChanges();
AlreadyExistscan come from checking the key or by querying the database to see whether the item already exists there.
AlreadyExists可以通过检查密钥或通过查询数据库来查看该项目是否已经存在。
回答by Tomislav Markovski
You can implement your own IEqualityComparer<Country>and pass that to the Except()method. Assuming your Country object has Idand Nameproperties, one example of that implementation could look like this:
您可以实现自己的IEqualityComparer<Country>并将其传递给Except()方法。假设您的 Country 对象具有Id和Name属性，该实现的一个示例可能如下所示：
public class CountryComparer : IEqualityComparer<Country>
{
public bool Equals(Country x, Country y)
{
return x.Name.Equals(y.Name) && (x.Id == y.Id);
}
public int GetHashCode(Country obj)
{
return string.Format("{0}{1}", obj.Id, obj.Name).GetHashCode();
}
}
and use it as
并将其用作
data.Countries.Except<Country>(db, new CountryComparer());
Although, in your case it looks like you just need to extract new objects, you can use var newCountries = data.Where(c => c.Id == Guid.Empty);if your Id is Guid.
虽然，在您的情况下，您似乎只需要提取新对象，但var newCountries = data.Where(c => c.Id == Guid.Empty);如果您的 Id 是 Guid ，则可以使用。
The best way is to inspect the Country.EntityStateproperty and take actions from there regarding on value (Detached, Modified, Added, etc.)
最好的方法是检查Country.EntityState财产并从那里采取有关价值的行动（分离、修改、添加等）
You need to provide more information on what your datacollection contains i.e. are the Country objects retrieved from a database through the entityframework, in which case their context can be tracked, or are you generating them using some other way.
您需要提供有关您的data集合包含的内容的更多信息，即通过实体框架从数据库中检索的 Country 对象，在这种情况下可以跟踪它们的上下文，或者您是否使用其他方式生成它们。
回答by Amir Ismail
I am not sure this will be the best solution but I think you have to get all countries from DB then check it with your parsed data
我不确定这将是最好的解决方案，但我认为您必须从数据库中获取所有国家/地区，然后使用您解析的数据进行检查
void Method(object sender, DocumentLoadedEvent e)
{
var data = e.ParsedData as Country[];
using(var db = new DataContractEntities)
{
List<Country> mycountries = db.Countries.ToList();
foreach(var PC in data)
{
if(mycountries.Any( C => C.Name==PC.Name ))
{
var country = mycountries.Any( C => C.Name==PC.Name );
//Update it here
}
else
{
var newcountry = Country.CreateCountry(PC.Name);//you must provide all required parameters
newcountry.Name = PC.Name;
db.AddToCountries(newcountry)
}
}
db.SaveChanges();
}
}
