How can I use use Entity Framework to do a MERGE when I don't know if the record exists?

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

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.

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.

Solution 1:

I use AddOrUpdate in this situation. However, I believe it does query the database first in order to decide to issue an insert or update.

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

Solution 2:

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

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

Solution 3:

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.

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.

// Easy to use
context.BulkMerge(customers)

// Easy to customize
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});