c# - 添加一批实体。如何确定调用 SaveChanges() 时哪些实体失败

Question

我有一个 Tools_NawContext 类扩展 DbContext 和一个 DbResult 类来调整 SaveChanges 方法的结果发生异常时的一点。抛出异常时，我会创建一条特定的错误消息，我知道它属于我尝试添加、删除或编辑的一个实体。用户可以根据错误信息采取适当的措施并重试。

public partial class Tools_NawContext : DbContext
{
    public Tools_NawContext(DbContextOptions<Tools_NawContext> options) : base(options) { }

    public DbResult TrySaveChanges()
    {
        try {
            int numberOfRowsSaved = SaveChanges();
            return new DbResult(numberOfRowsSaved);
        } catch(Exception ex) {
            return new DbResult(ex);
        }
    }
}

public class DbResult
{
    public DbResult(int numberOfRowsSaved) {
        this.Succeeded = true;
        this.NumberOfRowsSaved = numberOfRowsSaved;
    }

    public DbResult(Exception exception)
    {
        this.Exception = exception;
        if(exception.GetType() == typeof(DbUpdateException) && exception.InnerException != null) {
            if (exception.InnerException.Message.StartsWith("The DELETE statement conflicted with the REFERENCE constraint")) {
                this.DuplicateKeyError = true;
                this.DuplicateKeyErrorMessage = "There are other objects related to this object. First delete all the related objects.";
            } else if (exception.InnerException.Message.StartsWith("Violation of PRIMARY KEY constraint")) {
                this.DuplicateKeyError = true;
                this.DuplicateKeyErrorMessage = "There is already a row with this key in the database.";
            } else if (exception.InnerException.Message.StartsWith("Violation of UNIQUE KEY constraint")) {
                this.DuplicateKeyError = true;
                this.DuplicateKeyErrorMessage = "There is already a row with this key in the database.";
            }
        } else if(exception.GetType() == typeof(System.InvalidOperationException) && exception.Message.StartsWith("The association between entity types")) {
            this.DuplicateKeyError = true;
            this.DuplicateKeyErrorMessage = "There are other objects related to this object. First delete all the related objects.";
        }
    }

    public bool Succeeded { get; private set; }
    public int NumberOfRowsSaved { get; private set; }
    public bool DuplicateKeyError { get; private set; }
    public string DuplicateKeyErrorMessage { get; private set; }
    public Exception Exception { get; private set; }
    public List<string> ErrorMessages { get; set; }
    public string DefaultErrorMessage { get { if (Succeeded == false) return "Er is een fout in de database opgetreden."; else return ""; } private set { } }
}

但是我现在正在尝试导入一些 JSON 并想再次使用 TrySaveChanges 方法。然而这次经过一些检查后，我首先将多个实体添加到上下文中，而不仅仅是 1 个。添加完所有实体后，我将调用 TrySaveChanges 方法。它仍然有效，但如果出现问题，我无法确定哪些实体未能保存。如果我添加 1000 个实体，只有 1 个会失败，我无法确定哪里出错了。 我如何确定哪些添加的实体抛出错误？下面是我如何使用它的示例。

---

我有 2 个 EF 生成的类。 Testresultaten 和 Keuring

public partial class Testresultaten
{
    public int KeuringId { get; set; }
    public int TestId { get; set; }
    public string Resultaat { get; set; }
    public string Status { get; set; }
    public int TestinstrumentId { get; set; }

    public virtual Keuring Keuring { get; set; }
    public virtual Test Test { get; set; }
    public virtual Testinstrument Testinstrument { get; set; }
}

public partial class Keuring
{
    public Keuring()
    {
        Keuring2Werkcode = new HashSet<Keuring2Werkcode>();
        Testresultaten = new HashSet<Testresultaten>();
    }

    public int Id { get; set; }//NOTE: Auto-incremented by DB!
    public int GereedschapId { get; set; }
    public DateTime GekeurdOp { get; set; }
    public int KeuringstatusId { get; set; }
    public int TestmethodeId { get; set; }
    public DateTime GekeurdTot { get; set; }
    public string GekeurdDoor { get; set; }
    public string Notitie { get; set; }

    public virtual ICollection<Keuring2Werkcode> Keuring2Werkcode { get; set; }
    public virtual ICollection<Testresultaten> Testresultaten { get; set; }
    public virtual Gereedschap Gereedschap { get; set; }
    public virtual Keuringstatus Keuringstatus { get; set; }
    public virtual Testmethode Testmethode { get; set; }
}

我有一个 _KeuringImporter 类，它有一个方法可以将 newKeuring 和 testresultatenList 添加到 dbContext(_Tools_NawContext).

private Result<KeuringRegel, Keuring> SetupKeuringToDB2(KeuringRegel row, int rownr, Keuring newKeuring)
{
    _Tools_NawContext.Keuring.Add(newKeuring);

    List<string> errorMessages = new List<string>();
    List<Testresultaten> testresultatenList = new List<Testresultaten>();

    foreach (string testName in row.testNames.Keys.ToList())
    {
        string testValue = row.testNames[testName].ToString();
        Test test = _Tools_NawContext.Test.Include(item => item.Test2Testmethode).SingleOrDefault(item => item.Naam.Equals(testName, StringComparison.OrdinalIgnoreCase));

        //-----!!NOTE!!-----: Here KeuringId = newKeuring.Id is a random negative nr and is not beeing roundtriped to the db yet!
        Testresultaten newTestresultaten = new Testresultaten() { KeuringId = newKeuring.Id, TestId = test.Id, Resultaat = testValue, Status = row.Status, TestinstrumentId = 1 };
        testresultatenList.Add(newTestresultaten);
    }
    _Tools_NawContext.Testresultaten.AddRange(testresultatenList);

    return new Result<KeuringRegel, Keuring>(row, newKeuring, errorMessages);
}

就像我说的。我用它来导入 JSON。如果一个 JSON 文件包含 68 行，则该方法被调用 68 次。或者说:68 个新的 Keuring 项目被附加到 DbContext 并且每次 Testresultaten 列表被添加到 DbContext。

设置好所有内容后，我终于从我的 Controller 中调用了 SaveSetupImportToDB。 (此方法也是我的 _KeuringImporter 类的一部分。)

public DbResult SaveSetupImportToDB()
{
    DbResult dbResult = _Tools_NawContext.TrySaveChanges();
    return dbResult;
}

我如何实现我想要的？在上面的例子中，在我的 MS SQL 数据库中，Keuring 表有一个主键 Id，它由数据库自动递增。该表还具有 GereedschapId 和 GekeurdOp 的组合唯一键。

我可以在将 newKeuring 添加到上下文之前编写一些检查，如下所示:

private Result<KeuringRegel, Keuring> SetupKeuringToDB2(KeuringRegel row, int rownr, Keuring newKeuring)
        {
            List<string> errorMessages = new List<string>();
            var existingKeuring = _Tools_NawContext.Keuring.SingleOrDefault(x => x.Id == newKeuring.Id);
            if(existingKeuring == null) { errorMessages.Add("There is already a keuring with id "  + newKeuring.Id + " in the db."); }
            existingKeuring = _Tools_NawContext.Keuring.SingleOrDefault(x => x.GereedschapId == newKeuring.GereedschapId && x.GekeurdOp == newKeuring.GekeurdOp);
            if (existingKeuring == null) { errorMessages.Add("There is already a keuring with GereedschapId " + newKeuring.GereedschapId + " and GekeurdOp " + newKeuring.GekeurdOp + " in the db."); }
            //Some more checks to cerrect values of properties: 
            //-DateTimes are not in future
            //-Integers beeing greater then zero
            //-String lengths not beeing larger then 500 characters
            //-And so on, etc...

            _Tools_NawContext.Keuring.Add(newKeuring);

            List<Testresultaten> testresultatenList = new List<Testresultaten>();

            foreach (string testName in row.testNames.Keys.ToList())
            {
                string testValue = row.testNames[testName].ToString();
                Test test = _Tools_NawContext.Test.Include(item => item.Test2Testmethode).SingleOrDefault(item => item.Naam.Equals(testName, StringComparison.OrdinalIgnoreCase));

                //-----!!NOTE!!-----: Here KeuringId = newKeuring.Id is a random negative nr and is not beeing roundtriped to the db yet!
                Testresultaten newTestresultaten = new Testresultaten() { KeuringId = newKeuring.Id, TestId = test.Id, Resultaat = testValue, Status = row.Status, TestinstrumentId = 1 };
                testresultatenList.Add(newTestresultaten);
            }
            _Tools_NawContext.Testresultaten.AddRange(testresultatenList);

            return new Result<KeuringRegel, Keuring>(row, newKeuring, errorMessages);
        }

添加的第一个检查是简单的检查，以查看数据库中是否已经存在一个项目。我将不得不对添加到数据库中的每个实体进行这些检查。我更喜欢在不检查的情况下添加它们，在调用 SaveChanges 时捕获异常并告诉用户出了什么问题。通过我的应用程序为我节省了很多检查时间。我知道我无法检查所有情况，这就是为什么 DbResult 类还具有 DefaultErrorMessage 属性的原因。如果我当时“crud”1 个实体，这一切都很好。一次添加多个实体时就会出现问题。关于如何改进我的代码以便找出问题所在的任何建议？理想情况下，在调用 SaveChanges() 之后。但欢迎任何其他想法!可能会更改 DbContext 上的一个属性，如果实体被添加到上下文中，它会检查该实体是否已经存在。

Answer 1

如果您调用 SaveChanges 并且失败，则批处理中的所有操作都将回滚。最重要的是，您将获得一个 DbUpdateException，其属性 Entries 将包含导致错误的条目。上下文本身仍会保存跟踪对象的状态(包括失败)，您可以使用 ChangeTracker.Entries()(可能您不需要它)

try
{                
    model.SaveChanges();
}
catch (DbUpdateException e)
{
    //model.ChangeTracker.Entries();
    //e.Entries - Resolve errors and try again
}

我是你的情况，你可以做一个循环，继续尝试，直到所有的东西都被保存起来，比如

while (true)
{
    try
    {
        model.SaveChanges();
        break;
    }
    catch (DbUpdateException e)
    {
        foreach (var entry in e.Entries)
        {
            // Do some logic or fix
            // or just detach
            entry.State = System.Data.Entity.EntityState.Detached;
        }
    }
}