CODEWITHSUNDEEP
c#entity-framework-4.1
Eric Z Beard
Eric Z Beard

Reputation: 38424

How do I upsert a record in ADO.NET EF 4.1?

I'm trying to accomplish something really simple and I can't find how to do it using Entity Framework 4.1.

I want a controller method that accepts an object and then does an UPSERT (either an insert or update depending on whether the record exists in the database).

I am using a natural key, so there's no way for me to look at my POCO and tell if it's new or not.

This is how I am doing it, and it seems wrong to me:

[HttpPost]
public JsonResult SaveMyEntity(MyEntity entity)
{            
    MyContainer db = new MyContainer(); // DbContext
    if (ModelState.IsValid)
    {
        var existing =
            db.MyEntitys.Find(entity.MyKey);
        if (existing == null)
        {
            db.MyEntitys.Add(entity);
        }
        else
        {
            existing.A = entity.A;
            existing.B = entity.B;
            db.Entry(existing).State = EntityState.Modified;
        }
        db.SaveChanges();
        return Json(new { Result = "Success" });
    }
}

Ideally, the whole thing would just be something like this:

db.MyEntities.AddOrModify(entity);

Upvotes: 13

Views: 14319

Answers (5)

Salaros
Salaros

Reputation: 1452

I know this question is old and has an accepted answer, but I think there is a better solution: it doesn't require an extra interface to be implemented or key type to be defined.

public static class DbSetExtensions
{
    public static EntityEntry<TEnt> AddIfNotExists<TEnt, TKey>(this DbSet<TEnt> dbSet, TEnt entity, Func<TEnt, TKey> predicate) where TEnt : class
    {
        var exists = dbSet.Any(c => predicate(entity).Equals(predicate(c)));
        return exists
            ? null
            : dbSet.Add(entity);
    }

    public static void AddRangeIfNotExists<TEnt, TKey>(this DbSet<TEnt> dbSet, IEnumerable<TEnt> entities, Func<TEnt, TKey> predicate) where TEnt : class
    {
        var entitiesExist = from ent in dbSet
            where entities.Any(add => predicate(ent).Equals(predicate(add)))
            select ent;

        dbSet.AddRange(entities.Except(entitiesExist));
    }
}

So later it can be used like this:

using (var context = new MyDbContext())
{
    var user1 = new User { Name = "Peter", Age = 32 };
    context.Users.AddIfNotExists(user1, u => u.Name);

    var user2 = new User { Name = "Joe", Age = 25 };
    context.Users.AddIfNotExists(user2, u => u.Age);

    // Adds user1 if there is no user with name "Peter"
    // Adds user2 if there is no user with age 25
    context.SaveChanges();
}

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

Unfortunately there is no way to do this without querying database or using stored procedure. The minimalistic code should be:

public void AddOrModify<T>(T entity, string key) where T : class, IEntity // Implements MyKey 
{
     using (var context = new MyContainer())
     {
         if (context.Set<T>().Any(e => e.MyKey == key))
         {
              context.Entry(entity).State = EntityState.Modified;
         } 
         else
         {
              context.Entry(entity).State = EntityState.Added;
         }

         context.SaveChanges();
     }
}

Upvotes: 21

Tony O&#39;Hagan
Tony O&#39;Hagan

Reputation: 22728

To perform an UPSERT operation, You might want to consider creating an SP that performs a MERGE.

http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm

Whatever way you choose the operation must be atomic or you'll have a race condition. Your SP will probably need a HOLDLOCK to void this ...

http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Upvotes: 2

Rati_Ge
Rati_Ge

Reputation: 1270

actually there is a way to informa db context that entity you are trying to insert is changed and now the new one

_context.MyEntity.Attach(entity);
_context.MyEntity(entity).State = System.Data.EntityState.Modified;

Upvotes: 2

Ben Foster
Ben Foster

Reputation: 34820

In most cases you do not need to explicitly set the EntityState.Modified unless you've disabled change tracking.

The solution we took was to check the value of the entity identifier:

if (entity.Id == default(int)) {
    // transient entity so insert
} else {
    // update
}

Upvotes: 2

Related Questions