50

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.

Community
  • 1
  • 1
Joshua Frank
  • 13,120
  • 11
  • 46
  • 95
  • 1
    I think the simple answer is that EF just isn't able to perform a T-SQL MERGE statement. Add and update are the only options, which always requires a database roundtrip. You could make this roundtrip more efficient by fetching all IDs before starting an import. – Gert Arnold Nov 18 '14 at 21:54

4 Answers4

29

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

Robert Graves
  • 2,290
  • 3
  • 20
  • 24
  • Do you mean that it issues two queries, one to check existence, and one to do the insert or update? – Joshua Frank Nov 17 '14 at 17:27
  • I updated my response to show the queries I'm seeing EF generate. Yes it checks for existence in the first query and then decides on insert or update. – Robert Graves Nov 18 '14 at 16:32
  • 2
    Interesting. I think if it's going to issue two statements, I'd rather just manage this myself. I will check into that extension method, because that seems like it would do the trick. (But why wouldn't EntityFramework work that way in the first place, which seems like a clear performance win?) – Joshua Frank Nov 18 '14 at 17:14
  • 7
    Take care with the AddOrUpdate method: http://thedatafarm.com/data-access/take-care-with-ef-4-3-addorupdate-method/ – Colin Nov 24 '14 at 09:05
19

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.

mikebridge
  • 4,209
  • 2
  • 40
  • 50
  • 1
    This is very similar to what I wound up doing, so I'll mark it as the answer. I also wrapped it in a generic extension method for ease of reuse. – Joshua Frank Jan 07 '17 at 13:03
  • Is there any way we can mock this ExecuteSqlCommand, instead of mocking the method itself? – Prachi Mar 02 '20 at 07:07
  • @mikebridge How can this be done for a list without calling this method n times? – pantonis Apr 19 '22 at 07:57
16

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;
});
Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • 18
    This would have been a great solution. But it is not free. It cannot be used for Production environment as the free downloadable version needs to be updated every month.... – user007 Apr 02 '18 at 18:56
  • 2
    @user007 - if you want equally fast and free, then you will have to write something like a Stored procedure that takes a TableValuedParameter and that contains a correctly transacted and locked MERGE statement, then effectively change your DAL to use that instead of EF for saving changes. If you dont want to be bothered with that work and need/want to keep EF as the DAL then this fairly inexpensive library is a **great** solution because of the time it will save you. – StingyJack Sep 08 '20 at 21:11
0

The only way you can change INSERT or UPDATE sql that Entity Framework generates is when you configure your model to use stored procedures.

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

 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
Colin
  • 22,328
  • 17
  • 103
  • 197
  • 1
    I don't think the insert sproc is going to work, because it doesn't have a parameter for the existing primary key. But yes, maybe the update sproc can be used for this. To try this, all entities should be marked as `Modified` and in the sproc the decision for insert or update should be made. I hope that inserting records through an update sproc doesn't cause any unexpected side-effects. – Gert Arnold Nov 19 '14 at 19:50
  • @GertArnold I suspect that the insert doesn't need the existing primary key because the MERGE is using the "vendor's id" - which is not the primary key - otherwise you wouldn't need to merge would you? – Colin Nov 20 '14 at 10:48
  • That's not entirely clear from the question. The Id may be the primary key. Anyway, I think that using one of these procedures is a viable option. – Gert Arnold Nov 20 '14 at 11:05