Update Row If It Exists Else Insert Logic with Entity Framework

Update Row if it Exists Else Insert Logic with Entity Framework

If you are working with attached object (object loaded from the same instance of the context) you can simply use:

if (context.ObjectStateManager.GetObjectStateEntry(myEntity).State == EntityState.Detached)
{
    context.MyEntities.AddObject(myEntity);
}

// Attached object tracks modifications automatically

context.SaveChanges();

If you can use any knowledge about the object's key you can use something like this:

if (myEntity.Id != 0)
{
    context.MyEntities.Attach(myEntity);
    context.ObjectStateManager.ChangeObjectState(myEntity, EntityState.Modified);
}
else
{
    context.MyEntities.AddObject(myEntity);
}

context.SaveChanges();

If you can't decide existance of the object by its Id you must execute lookup query:

var id = myEntity.Id;
if (context.MyEntities.Any(e => e.Id == id))
{
    context.MyEntities.Attach(myEntity);
    context.ObjectStateManager.ChangeObjectState(myEntity, EntityState.Modified);
}
else
{
    context.MyEntities.AddObject(myEntity);
}

context.SaveChanges();

What is the correct way to add or update an entity with Entity Framework

Try to retrieve the existing entity from the DB using its primary key. If it does not exist, create a new entity. Then map the properties to the retrieved/created entity and save it.

using (var db = new context()) {
    // get data from rest endpoint
    foreach (var item in array) {
        // try to retrieve existing entity
        var myEntity = db.MyEntity.Find(item.Id);

        // if entity does not already exist -> create new
        if (myEntity == null) {
            myEntity = new MyEntity();
            db.MyEntity.Add(myEntity);
        }

        // map received values
        myEntity.Property1 = item.Property1;
        myEntity.Property2 = item.Property2;
    }

    // EntityState should be set automatically by EF ChangeTracker
    db.SaveChanges();
}

Sidenote regarding API design: most of the time it is better to have explicit create and update methods, because these usecases tend to differ. For example: if some values can only be set when creating the entity (e.g. CreationTimestamp, CreatedBy), but not during update, you got a good handle on this if you split the methods. Else you have to resort to sending additional flags in the command (IsUpdateUsecase).

EntityFramework, Insert if not exist, otherwise update

I would do it straightforward:

void Method(object sender, DocumentLoadedEvent e)
{
    var data = e.ParsedData as Country[];
    using(var db = new DataContractEntities)
    {
        foreach(var country in data)
        {
            var countryInDb = db.Countries
                .Where(c => c.Name == country.Name) // or whatever your key is
                .SingleOrDefault();
            if (countryInDb != null)
                db.Countries.ApplyCurrentValues(country);
            else
                db.Countries.AddObject(country);
        }
        db.SaveChanges();
     }
}

I don't know how often your application must run this or how many countries your world has. But I have the feeling that this is nothing where you must think about sophisticated performance optimizations.

Edit

Alternative approach which would issue only one query:

void Method(object sender, DocumentLoadedEvent e)
{
    var data = e.ParsedData as Country[];
    using(var db = new DataContractEntities)
    {
        var names = data.Select(c => c.Name);
        var countriesInDb = db.Countries
            .Where(c => names.Contains(c.Name))
            .ToList(); // single DB query
        foreach(var country in data)
        {
            var countryInDb = countriesInDb
                .SingleOrDefault(c => c.Name == country.Name); // runs in memory
            if (countryInDb != null)
                db.Countries.ApplyCurrentValues(country);
            else
                db.Countries.AddObject(country);
        }
        db.SaveChanges();
     }
}

EF InsertOrUpdate issue

dbContext.DbSet<T>().AddOrUpdate(Entity)

problem solved

Compare List to Database records, if exists - update, if new - create (EF Core)

Can't you just use this pattern?

foreach(var item in updates) {

    // search for existing entity.
    Entity entity = db.Entities.Where(x => x.keyValue == item.keyValue).FirstOrDefault();

    if(entity == null) {

        // no existing entity with this key value, insert.
        entity = new Entity(item);
        db.Entities.Add(entity);

    } else {
        // update existing entity.
        entity.property = item.property;
    }
}

db.SaveChanges();

Update record if it exists

Short Answer

...if I make any updates to the customer model, I actually need to check EF if my key already exists, and if it does, run an update instead.

AddOrUpdate() does just this. From the docs:

Adds or updates entities by key when SaveChanges is called. Equivalent to an "upsert" operation from database terminology

Example

In other words, change your code to this:

context.Customers.AddOrUpdate(c => c.UserId, customer);
context.SaveChanges();

In the above example, the c => c.UserId is an expression specifying that the UserId should be used when determining whether an Add or Update operation should be performed.

Considerations

• AddOrUpdate() matches database tables based on an arbitrary, user supplied key value. In the example, this key is UserId. Be sure to use an appropriate key.
• AddOrUpdate() updates all entity values and sets database cells to null for properties that a lack value. Be sure to set all the property values of the object (e.g. customer) before using it.

See also

Update Row if it Exists Else Insert Logic with Entity Framework It has a few answers that talk about four or more different approaches.

Need Help On How To If Record Exists Update Else Insert

It is hard to give advice without knowing all the details such as probability of duplicates, how many requests per seconds etc. There are a few common techniques I use to achieve that:

• a stored procedure that encapsulates 'upsert' workflow. See Oracle: how to UPSERT

• implement upsert logic on the client (your case) but to guarantee consistency you need to start transaction and close it after you performed operations.

If existing row in the table is more probable, use update before insert to avoid frequent exceptions. Otherwise you can optimize workflow by using insert before update.

Pessimistic solution

String updCmdTxt = "UPDATE ....";
String insertCmdTxt = "INSERT ....";
using (var scope = new TransactionScope())
{ 
   using (var con = new OracleConnection(conStr)){
      int rows;
      using (var updcmd = new OracleCommand(updCmdTxt, con)){
         //Set parameters ...
         rows = updcmd.ExecuteNonQuery();
      }
      //If update command did not affect any rows, insert
      if (rows == 0) {
         using (var insertcmd = new OracleCommand(insertCmdTxt, con)){
            // Set parameters...
            insertcmd.ExecuteNonQuery();
         }
      }
      scope.Complete();
   }
}

Optimistic solution

String updCmdTxt = "UPDATE ....";
String insertCmdTxt = "INSERT ....";
using (var scope = new TransactionScope())
{
    using (var con = new OracleConnection(conStr))
    {
        bool isDuplicate = false;
        var insertcmd = new OracleCommand(insertCmdTxt, con);
        try
        {
            // Set parameters...
            insertcmd.ExecuteNonQuery();
        }
        catch (OracleException x)
        {
            isDuplicate = ...; //determine if the exception is about duplicates.
            if (!isDuplicate)
            {
                throw;
            }
        }
        finally
        {
            insertcmd?.Dispose();
        }
        if (isDuplicate)
        {
            using (var updcmd = new OracleCommand(updCmdTxt, con))
            {
                //Set parameters ...
                updcmd.ExecuteNonQuery();
            }
        }
        scope.Complete();
    }
}

---

Related Topics