More efficient way to perform a UPSERT with EF6

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.

Answer 1

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 to DateTime.UtcNow), 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.Accounts.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.

Answer 2

We can make Ivan's answer even better performing using upserting (updating or inserting) all the accounts in one call.

I'm using Free package named EFCore.BulkExtensions that includes BulkInsertOrUpdate(list) method: https://github.com/borisdj/EFCore.BulkExtensions

(This package is listed in Microsoft ef extensions page: https://learn.microsoft.com/en-us/ef/core/extensions/)

Ivans code with ONE upsert call for all accounts:

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

    var dlAccounts = new List<Entities.DB.Account>();   

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

        //Add the updated account to a list
        dlAccounts.Add(dlAccount);          

    }

    //upsert dlAccounts in ONE call
    db.BulkInsertOrUpdate(dlAccounts);

    db.SaveChanges();
}

Note: BulkInsertOrUpdate assuming that the account has a primary key set. in the above example it is probably AccountNumber.