3

I am using AddOrUpdate method from EntityFramework 6.0 that by the doc should:

AddOrUpdate(IDbSet, TEntity[])
Adds or updates entities by key when SaveChanges is called. Equivalent to an "upsert" operation from database terminology. This method can be useful when seeding data using Migrations.

I want to do a "bulk upsert", I have a trigger that nightly pulls data from another server and inserts new values and updates existing. The code I am using is:

    private void InsertConfigurations(IEnumerable<Configuration> configs)
    {
        using (var context = new MyEntities())
        {
            using (var scope = new TransactionScope(TransactionScopeOption.Suppress))
            {
                    context.Configuration.AutoDetectChangesEnabled = false;
                    context.Configurations.AddOrUpdate(configs.ToArray());
                    context.SaveChanges();
                    scope.Complete();
            }

        }
    }

Now this code fails every now and then with:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Configuration_ID'. Cannot insert duplicate key in object 'dbo.Configuration'. The duplicate key value is (218383).

From the little I understand from UPSERT, shouldn't the key reason to use it be to avoid the exact problem I have having? Am I doing something wrong?

StackTrace:

   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
   at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at D.DataService.DataService.InsertConfigurations(IEnumerable`1 configs) in c:\localdev\DWeb\DWeb\D.DataService\DataService.cs:line 41
   at D.DataService.DataService.UpdateAllConfigurations() in c:\localdev\DWeb\DWeb\D.DataService\DataService.cs:line 27
   at D.DataService.Job.ConfigurationUpdaterJob.Execute(IJobExecutionContext context) in c:\localdev\DWeb\DWeb\D.DataService\Job\ConfigurationUpdaterJob.cs:line 14
Erki M.
  • 5,022
  • 1
  • 48
  • 74
  • See http://stackoverflow.com/questions/17712416/how-to-handle-primary-key-in-entity-framework-5-code-first/17719137#17719137 and http://thedatafarm.com/data-access/take-care-with-ef-4-3-addorupdate-method/ – Colin Apr 21 '15 at 09:52
  • Any luck with this? I was thinking the exact same thing as you "From the little I understand from UPSERT, shouldn't the key reason to use it be to avoid the exact problem I have having? Am I doing something wrong?" Just starting digging into it though. – Matt Klepeis Mar 18 '16 at 17:50
  • I managed to overcome this by using the extension suggested by @Colin , Check the link from his comment. – Erki M. Mar 20 '16 at 19:15

0 Answers0