Entity Framework 6: Insert or Update

Question

I want to insert or update a row in a table with Entity Framework depending if the primary key (string) already exists in the table.

I am using the following code to do that which I got from here: Insert or update pattern

private void InsertOrUpdate(ServiceMonitoring sm)
{
    try
    {
        using (var context = new MyDBEntities())
        {
            context.Entry(sm).State = sm.serviceName == null ?
                EntityState.Added :
                EntityState.Modified;
            context.SaveChanges();
        }
        log.Info("ServiceMonitoring updated");
    }
    catch (Exception ex)
    {
        log.Error("Error updating ServiceMonitoring");
        log.Debug(ex.Message);
    }

}

It is working fine when the row (e.g. serviceName = "MyService") already exists. Then an UPDATE is performed. But if the row does not exist, the INSERT fails and I get the following error message:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

Any help is highly appreciated!

https://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec?page=1&tab=votes#tab-top — M Bakardzhiev, Apr 12 '18 at 14:39
I don't think that this explains my issue. I am running this in our test system which is used rarely. Also, I have changed the "serviceName" to some weird random character sequence which will never show up in this table. — Malawirel, Apr 12 '18 at 15:04

Adam Vincent · Accepted Answer · 2018-04-13T14:23:19.943

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

Thank you. I think I understand what you explained and will try to optimize my code accordingly. The only thing I don't agree with is the following: "EF knows not to fill in the primary key on the insert statement because it will be auto-generated by the database" The primary key is not auto-generated by the database since it's not an IDENTITY column. The primary key is simply the serviceName column which I will fill with a unique string for each service I want to monitor in that table. — Malawirel, Apr 13 '18 at 08:03
Thanks for the feedback! You are correct, and I have adjusted my answer. — Adam Vincent, Apr 13 '18 at 14:24
Accepted the answer after 3 years, lol. Better late then never eh? — Adam Vincent, Jun 01 '21 at 12:36
Yep, I reviewed all my questions today, and found that I did not accept this pretty neat answer. Thanks again! — Malawirel, Jun 01 '21 at 14:36

Entity Framework 6: Insert or Update

1 Answers1