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
Solution
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.
Problem
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.