当我不知道记录是否存在时，如何使用实体框架进行合并? [英] How can I use use Entity Framework to do a MERGE when I don't know if the record exists?

问题描述

在 this SO answer 关于实体框架和 MERGE，如何编码的示例如下:

In this SO answer about Entity Framework and MERGE, the example for how to code it is this:

public void SaveOrUpdate(MyEntity entity)
{
  if (entity.Id == 0)
  {
    context.MyEntities.AddObject(entity);
  }
  else
  {
    context.MyEntities.Attach(entity);
    context.ObjectStateManager.ChangeObjectState(entity, EntityState.Modified);
  }
}

这假设您已经知道要更新的实体是否存在；在这种情况下，您检查 entity.Id.但是，如果您不知道该项目是否存在怎么办?例如，在我的例子中，我正在将供应商的记录导入到我的数据库中，并且给定的记录可能已经导入，也可能尚未导入.如果记录存在，我想更新它，否则添加它.但是在这两种情况下都已经设置了供应商的 id.

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.

除非我简单地询问数据库记录是否已经存在，否则我看不到任何方法，这违背了 MERGE 的全部目的.

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.

推荐答案

如果你想要一个没有存储过程的原子数据库 UPSERT 命令，并且你不担心上下文被更新，可能值得一提的是你也可以 wrapExecuteSqlCommand 调用中嵌入的 MERGE 语句:

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 MERGE statement in an ExecuteSqlCommand call:

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);
}

这并不漂亮，因为它在 EF 对实体的抽象之外工作，但它允许您利用 MERGE 命令.

This isn't pretty because it works outside EF's abstraction over entities but it will allow you to leverage the MERGE command.

这篇关于当我不知道记录是否存在时，如何使用实体框架进行合并?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！