使用EF6执行UPSERT的更有效方法 [英] More efficient way to perform a UPSERT with EF6

问题描述

我有以下代码块，基本上检查数据库中是否存在实体，并将其加载到上下文中进行更新，或者如果不存在则添加新实体.

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

我一直在进行大量研究，以将实体附加到上下文并使用EntityState，但是我只是不了解在我的示例中如何编写该代码.

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.

有人可以帮助我向我展示一种执行上述相同操作的更好，更有效的方法吗?我是EF的新手，请确保我使用的是正确的

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.

推荐答案

EF的设计者已将使用断开连接的实体的工作留给了使用EF的开发人员.因此，没有适当"的方法-一切都取决于使用情况和实体模型.

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.

由于您似乎正在强制更新现有记录(通过设置DateModified)，因此无需将现有数据加载到上下文中.只需一次数据库访问就可以获取现有的实体PK，并将其用作添加或更新的条件就足够了:

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

(将GId_Type替换为GId的类型，例如int，Guid等)

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

这以及将SaveChanges移到循环外应该为您提供最佳的性能.

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

这篇关于使用EF6执行UPSERT的更有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！