Entityframework, Insert If Not Exist, Otherwise Update

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 best way to insert if not exist, otherwise update in entityframework core?

You should use Any() instead

Determines whether any element of a sequence exists or satisfies a
condition.

if (UserDBContext.UserStatus.Any(X => X.id == UM.id))
{
   UserDBContext.UserStatus.Update(UM);
}

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();
     }
}

Entity Framework Add if not exist without update

Have you tried to check if the entity exists and if not - add it? Like this :

UPDATE

using System.Linq.Expressions;
    public class ContextWithExtensionExample
    {
        public void DoSomeContextWork(DbContext context)
        {
            var uni = new Unicorn();
            context.Set<Unicorn>().AddIfNotExists(uni , x => x.Name == "James");
        }
    }

    public static class DbSetExtensions
    {
        public static T AddIfNotExists<T>(this DbSet<T> dbSet, T entity, Expression<Func<T, bool>> predicate = null) where T : class, new()
        {
            var exists = predicate != null ? dbSet.Any(predicate) : dbSet.Any();
            return !exists ? dbSet.Add(entity) : null;
        }
    }

You can use this method directly and remember to call DbContext.SaveChanges() after the call.

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).

Entity Framework 6: Insert or Update

The reason the update or insert pattern works with the primary key in the MSDN sample is because it is flagged as an Identity field. When you add an entity to a DbContext, EF gives the Id field a default value of 0, and the database will assign a unique value to the row when the INSERT is executed.

You've tried to incorrectly adapt the 'update or insert' pattern to use the property serviceName, and the solution is to use the pattern as provided by the MSDN article you referenced, or execute a query.

For completeness, WHY doesn't your adaption work?
Because you are passing in a ServiceMonitoring object and expecting context.Entry(sm) to query your database for the correct row of data. It does not sir. So in your case, EntityState.Added is basically unreachable code.

private void InsertOrUpdate(ServiceMonitoring sm)  
{
    try
    {
        //this brand new DbContext instance has not queried your database, and is not tracking any objects!
        using (var context = new MyDBEntities())  
        {
            //if (sm.serviceName is null) <- should never be null, as it's the ServiceMonitoring object you are passing into the function.
            //The DbContext.Entry() does not know whether or not this actually exists in the database, it only allows you to inform
            //EF about the state of the object.
            context.Entry(sm).State = sm.serviceName == null ?  //<- Always false (unless you *really* want a serviceName to not have a name)
                EntityState.Added : // <- code unreachable
                EntityState.Modified; //This is where the exception occurs, your code is always generating an UPDATE statement.
                                        //When the entry exists in the database, EF's generated UPDATE statement succeeds.
                                        //When the entry does not exist, the UPDATE statement fails.
            context.SaveChanges();
        }
        log.Info("ServiceMonitoring updated");
    }
    catch (Exception ex)
    {
        log.Error("Error updating ServiceMonitoring");
        log.Debug(ex.Message);
    }
}

How to write insert if not exist else update using entity framework?

One way you could do it is to batch up the queries for existence ... for example, using the .Contains method (like this), you can query for some or all of the items which may or may not exist at once. Then once you have the data locally, you can quickly check if it's there before inserting

Entity Framwork Core – Is a “Insert if not exists” possible?

Currently Upsert is not natively supported in EF Core (open GitHub issue here: https://github.com/aspnet/EntityFrameworkCore/issues/4526#issuecomment-366818031)

Open source library that extends EF Core to support this can be found here: https://github.com/artiomchi/FlexLabs.Upsert

---

Related Topics