More efficient way to perform a UPSERT with EF6

c# entity-framework-6

Question

I have the following code block that basically checks for the existance of a entity in the DB and loads it to context for updating or in case it does not exist adds the new entity.

using (var db = new Entities.DB.DConn())
{
    //...
    foreach (Account account in accounts)
    {
        bool isNewRecord = false;
        Entities.DB.Account dlAccount = new Entities.DB.Account();
        Entities.DB.Account exisitngAcct = db.Accounts.Where(x => x.GId == dlG.Id).FirstOrDefault(); //x.GId is NOT ad primary key
        if (exisitngAcct != null)
        {
            dlAccount = exisitngAcct;
            isNewRecord = true;
        }

        dlAccount.GId = dlG.Id;
        dlAccount.AccountName = account.NameAtFI;
        dlAccount.AccountNumber = account.AcctNumber;
        dlAccount.AcctType = account.AcctType;
        dlAccount.AsOfDate = account.DateCreated;
        dlAccount.IsDeleted = false;
        dlAccount.DateModified = DateTime.UtcNow.ToUniversalTime();

        if (isNewRecord)
        {
            dldb.Accounts.Add(dlAccount);
        }

        db.SaveChanges();
    }
}

I have been doing loads of research on attaching entities to context and using EntityState, but i'm just not getting how that code would be written in my example.

Is there any way someone can help to show me a better more efficient way to perform the same operation as above? I'm fairly new to EF and want to make sure that i am working with it properly.

Thanks for any help you can provide.

2
2
7/13/2017 4:57:53 PM

Accepted Answer

Working with disconnected entities has been left by the designers of the EF for the developers using it. Thus there is no "proper" way - everything depends on the usage case and the entity model.

Since you seem to be forcing update of the existing records (by setting the DateModified), there is no need to load the existing data into context. It would be sufficient to get the existing entity PKs with single database trip and use it as criteria for add or update:

using (var db = new Entities.DB.DConn())
{
    //...
    var accountIds = accounts.Select(x => x.GId); // variable required by EF6 Contains translation
    var existingAccountIds = new HashSet<GId_Type>(
        db.Accouns.Where(x => accountIds.Contains(x.GId).Select(x => x.GId));
    foreach (Account account in accounts)
    {
        var dlAccount = new Entities.DB.Account();
        dlAccount.GId = account.GId;
        dlAccount.AccountName = account.NameAtFI;
        dlAccount.AccountNumber = account.AcctNumber;
        dlAccount.AcctType = account.AcctType;
        dlAccount.AsOfDate = account.DateCreated;
        dlAccount.IsDeleted = false;
        dlAccount.DateModified = DateTime.UtcNow.ToUniversalTime();    
        if (existingAccountIds.Contains(dlAccount.GId))
            db.Entry(dlAccount).State = EntityState.Modified; // update
        else
            db.Accounts.Add(dlAccount); // insert
    }
    db.SaveChanges();
}

(replace the GId_Type with the type of the GId, e.g. int, Guid etc.)

This, along with moving the SaveChanges outside the loop should give you the best performance for this scenario.

3
7/13/2017 5:56:35 PM

Expert Answer

There is some way to improve performance. By example, using @Ivan solution to retrieve more than one account at a time to reduce the number of database round-trip

However, the SaveChanges methods will still make one database round-trip for every entity you need to Add or Update which is INSANELY slow.


Disclaimer: I'm the owner of the project Entity Framework Extensions

This library is not free. But it is the most efficient way to perform a upsert operation.

In addition of Bulk Merge (Upsert), this library allows you to perform all bulk operations:

  • BulkSaveChanges
  • BulkInsert
  • BulkUpdate
  • BulkDelete
  • BulkMerge
  • BulkSynchronize

Example:

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});
0
7/13/2017 7:15:20 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow