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

.net entity-framework entity-framework-6 upsert

Accepted Answer

It might be worth noting that you can also wrap an embedded UPSERT command if you want an atomic database UPSERT command without a stored procedure and you're not concerned about the context being updated.MERGE declaration in anExecuteSqlCommand 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 is ugly since it violates EF's entity abstraction, but it will let you take advantage of theMERGE command.

12
1/6/2017 10:05:07 PM

Expert Answer

Although AddOrUpdate is a decent option, it is not scalable. It takes one round-trip to the database to insert or update an entity and another to determine whether it already exists. As a result, 1000 entities saved will result in 2000 database round trips.

I am the project's owner, according to Disclaimer.

This module significantly boosts efficiency while enabling you to do a merging operation inside the Entity Framework. The saving of 1000 entities will only need one roundtrip to the database.

// Easy to use
context.BulkMerge(customers)

// Easy to customize
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});
10
8/22/2018 10:07:21 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow