C# LINQ to SQL insert-if-non-existent
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/100068/
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
LINQ to SQL insert-if-non-existent
提问by core
I'd like to know if there's an easier way to insert a record if it doesn't already exist in a table. I'm still trying to build my LINQ to SQL skills.
如果表中不存在记录，我想知道是否有更简单的方法来插入记录。我仍在努力培养我的 LINQ to SQL 技能。
Here's what I've got, but it seems like there should be an easier way.
这是我所拥有的，但似乎应该有一种更简单的方法。
public static TEntity InsertIfNotExists<TEntity>
(
DataContext db,
Table<TEntity> table,
Func<TEntity,bool> where,
TEntity record
)
where TEntity : class
{
TEntity existing = table.SingleOrDefault<TEntity>(where);
if (existing != null)
{
return existing;
}
else
{
table.InsertOnSubmit(record);
// Can't use table.Context.SubmitChanges()
// 'cause it's read-only
db.SubmitChanges();
}
return record;
}
采纳答案by Mark Cidade
public static void InsertIfNotExists<TEntity>
(this Table<TEntity> table,
TEntity entity,
Expression<Func<TEntity,bool>> predicate)
where TEntity : class
{
if (!table.Any(predicate))
{
table.InsertOnSubmit(record);
table.Context.SubmitChanges();
}
}
table.InsertIfNotExists(entity, e=>e.BooleanProperty);
回答by Panos
Agree with marxidad's answer, but see note 1.
同意marxidad 的回答，但请参阅注释 1。
Note 1: IMHO, it is not wise to call db.SubmitChanges()in a helper method, because you may break the context transaction. This means that if you call the InsertIfNotExists<TEntity>in the middle of a complex update of several entities you are saving the changes not at once but in steps.
注 1：恕我直言，调用db.SubmitChanges()辅助方法是不明智的，因为您可能会破坏上下文事务。这意味着，如果您InsertIfNotExists<TEntity>在多个实体的复杂更新过程中调用 ，则您不是一次而是分步保存更改。
Note 2: The InsertIfNotExists<TEntity>method is a very generic method that works for any scenario. If you want to just discriminate the entities that have loaded from the database from the entities that have been created from the code, you can utilize the partial method OnLoadedof the Entity class like this:
注 2：该InsertIfNotExists<TEntity>方法是一种非常通用的方法，适用于任何场景。如果您只想区分从数据库加载的实体和从代码创建的实体，您可以使用OnLoadedEntity 类的部分方法，如下所示：
public partial class MyEntity
{
public bool IsLoaded { get; private set; }
partial void OnLoaded()
{
IsLoaded = true;
}
}
Given that (and note 1), then InsertIfNotExists functionality is reduced to the following:
鉴于（和注 1），然后 InsertIfNotExists 功能减少到以下内容：
if (!record.IsLoaded)
db.InsertOnSubmit(record);
回答by Jamal
Small modification for Mark's answer:
马克的回答的小修改：
If you only care about checking if the entity exists by its primary key, Marke's answer can be used like this:
如果您只关心通过其主键检查实体是否存在，则可以像这样使用 Marke 的答案：
public static void InsertIfNotExists<TEntity>
(this Table<TEntity> table
, TEntity entity
) where TEntity : class
{
if (!table.Contains(entity))
{
table.InsertOnSubmit(entity);
}
}
回答by Michael Kropat
As others have pointed out, the if (!Any()) { InsertOnSubmit(); }solutions all have a race condition. If you go that route, when you call SubmitChanges, you have to take into account that either a) a SqlExceptioncould be raised for a duplicate insert, or b) you could have duplicate records in the table.
正如其他人指出的那样，if (!Any()) { InsertOnSubmit(); }所有解决方案都存在竞争条件。如果你走那条路，当你打电话时SubmitChanges，你必须考虑到 a) aSqlException可能会因重复插入而引发，或者 b) 表中可能有重复的记录。
Fortunately, we can use the database to avoid the race condition by enforcing uniqueness. The following code assumes that there is a primary key or unique constraint on the table to prevent the insertion of duplicate records.
幸运的是，我们可以使用数据库通过强制唯一性来避免竞争条件。以下代码假定表上存在主键或唯一约束以防止插入重复记录。
using (var db = new DataContext()) {
// Add the new (possibly duplicate) record to the data context here.
try {
db.SubmitChanges();
} catch (SqlException ex) {
const int violationOfPrimaryKeyContraint = 2627;
const int violationOfUniqueConstraint = 2601;
var duplicateRecordExceptionNumbers = new [] {
violationOfPrimaryKeyContraint, violationOfUniqueConstraint
};
if (!duplicateRecordExceptionNumbers.Contains(ex.Number)) {
throw;
}
}
}
Now... things get a fair bit more complicated if you have to perform the insert in a batch transaction with other database updates.
现在...如果您必须在批处理事务中使用其他数据库更新执行插入，事情会变得更加复杂。
