More efficient way to perform a UPSERT with EF6

user_1034022 0
Viewed 3279

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.

entity-framework-6 c#
3 Answers

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://docs.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.

user_15440540
Related