.net 当我不知道记录是否存在时，如何使用实体框架进行合并？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23916453/
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
How can I use use Entity Framework to do a MERGE when I don't know if the record exists?
提问by Joshua Frank
In this SO answerabout Entity Framework and MERGE, the example for how to code it is this:
在这个关于实体框架和 MERGE 的SO 回答中，如何编码的示例是这样的：
public void SaveOrUpdate(MyEntity entity)
{
if (entity.Id == 0)
{
context.MyEntities.AddObject(entity);
}
else
{
context.MyEntities.Attach(entity);
context.ObjectStateManager.ChangeObjectState(entity, EntityState.Modified);
}
}
This assumes that you already know if the entity that you want to upsert exists or not; in this case you check entity.Id. But what if you don't know if the item exists or not? For instance, in my case, I'm importing records from a vendor into my database, and a given record may or may not have already been imported. I want to update the record if it exists, otherwise add it. But the vendor's id is already set in both cases.
这假设您已经知道要更新插入的实体是否存在；在这种情况下，您检查entity.Id. 但是如果您不知道该项目是否存在怎么办？例如，在我的情况下，我正在将供应商的记录导入到我的数据库中，并且给定的记录可能已经或可能尚未导入。如果记录存在，我想更新它，否则添加它。但是在这两种情况下都已经设置了供应商的 id。
I can't see any way to do this unless I simply ask the database if the record is there already, which defeats the whole purpose of MERGE.
我看不出有什么办法可以做到这一点，除非我只是简单地询问数据库记录是否已经存在，这违背了 MERGE 的全部目的。
采纳答案by mikebridge
If you want an atomic database UPSERT command without a stored procedure and you're not worried about the context being updated, it might worth mentioning that you can also wrap an embedded MERGEstatement in an ExecuteSqlCommandcall:
如果您想要一个没有存储过程的原子数据库 UPSERT 命令，并且您不担心上下文被更新，那么值得一提的是，您还可以MERGE在ExecuteSqlCommand调用中包装一个嵌入式语句：
public void SaveOrUpdate(MyEntity entity)
{
var sql = @"MERGE INTO MyEntity
USING
(
SELECT @id as Id
@myField AS MyField
) AS entity
ON MyEntity.Id = entity.Id
WHEN MATCHED THEN
UPDATE
SET Id = @id
MyField = @myField
WHEN NOT MATCHED THEN
INSERT (Id, MyField)
VALUES (@Id, @myField);"
object[] parameters = {
new SqlParameter("@id", entity.Id),
new SqlParameter("@myField", entity.myField)
};
context.Database.ExecuteSqlCommand(sql, parameters);
}
This isn't pretty because it works outside EF's abstraction over entities but it will allow you to leverage the MERGEcommand.
这不是很好，因为它在 EF 对实体的抽象之外工作，但它允许您利用MERGE命令。
回答by Robert Graves
I use AddOrUpdatein this situation. However, I believe it does query the database first in order to decide to issue an insert or update.
我在这种情况下使用AddOrUpdate。但是，我相信它会首先查询数据库，以便决定发出插入还是更新。
context.MyEntities.AddOrUpdate(e => e.Id, entity);
Update:
更新：
I ran through my debug log files. First it runs:
我浏览了我的调试日志文件。首先它运行：
SELECT TOP (2) ... WHERE 1 = [Extent1].[Id]
Then it runs either:
然后它运行：
INSERT [dbo].[TestTable](...) VALUES (...)
SELECT [Id]
FROM [dbo].[TestTable]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
OR:
或者：
UPDATE [dbo].[TestTable]
SET ...
WHERE ([Id] = @2)
Update 2: Here's an interesting extension method the uses MERGE: https://gist.github.com/ondravondra/4001192
更新 2：这是一个使用 MERGE 的有趣扩展方法：https: //gist.github.com/ondravondra/4001192
回答by Jonathan Magnan
AddOrUpdate is a good solution however it's not scalable. One database round-trip is required to check if the entity already exists and one round-trip to insert or update the entity. So, if you save 1000 entities, 2000 database round-trip will be performed.
AddOrUpdate 是一个很好的解决方案，但它不可扩展。需要进行一次数据库往返以检查实体是否已存在，并需要进行一次往返以插入或更新实体。因此，如果您保存 1000 个实体，将执行 2000 次数据库往返。
Disclaimer: I'm the owner of the project Entity Framework Extensions
免责声明：我是实体框架扩展项目的所有者
This library allows you to perform a merge operation within Entity Framework at the same time it dramatically improves the performance. Only 1 database round-trip will be required to save 1000 entities.
该库允许您在实体框架内执行合并操作，同时显着提高性能。只需 1 次数据库往返即可保存 1000 个实体。
// Easy to use
context.BulkMerge(customers)
// Easy to customize
context.BulkMerge(customers, operation => {
operation.ColumnPrimaryKeyExpression =
customer => customer.Code;
});
回答by Colin
The only way you can change INSERT or UPDATE sql that Entity Framework generates is when you configure your model to use stored procedures.
您可以更改实体框架生成的 INSERT 或 UPDATE sql 的唯一方法是将模型配置为使用存储过程。
You can then modify the sql generated in the Up migration for creating the insert and update procedures to use your MERGE sql rather than INSERT and UPDATE
然后，您可以修改 Up 迁移中生成的 sql 以创建插入和更新过程，以使用您的 MERGE sql 而不是 INSERT 和 UPDATE
CREATE PROCEDURE [dbo].[Blog_Insert]
@Name nvarchar(max),
@Url nvarchar(max)
AS
BEGIN
-- Your Merge Sql goes here
--And you need to use MERGE OUTPUT to get the primary key
--instead of SCOPE_IDENTITY()
--SELECT SCOPE_IDENTITY() AS BlogId
END
