Adding batch of entities. How to determine which entities failed when calling SaveChanges()

Question

I have a Tools_NawContext class extending DbContext and a DbResult class to tweak the outcome of the SaveChanges method a bit when an exception occures. When an exception is throwed I create a specific error message which I knows belongs to the one entity I try to add, delete or edit. The user can take appropiate action based on the error message and try again.

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 { } }
}

However I am now trying to import some JSon and want to use the TrySaveChanges method again. However this time after some checks, I first add multiple entities to the context, not just 1. Once all added, I call the TrySaveChanges method. It still works but if somethings fails I can not determine which entities failed to be saved. If I add 1000 entities and just 1 will fail I can not determine where it went wrong. How can I determine which added entities are throwing errors? Below is an example of how I use it.

I have 2 EF generated classes. Testresultaten and 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; }
}

I have a _KeuringImporter class which has a method which adds a newKeuring and a testresultatenList to the 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);
}

Like I said. I use it to import JSON. If a JSON file contains 68 rows, the method is called 68 times. Or to speak: 68 new Keuring items are attached to the DbContext and also every time a list of Testresultaten is added to the DbContext.

Once everything is setup I finally call the SaveSetupImportToDB from my controller. (This method is also part of my _KeuringImporter class.)

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

How do I achieve what I want? In the above case in my MS SQL database the Keuring table has an primary key of Id which is auto incremented by the db. The table also has a combined unique key of GereedschapId and GekeurdOp.

I can write some checks before a newKeuring is added to the context, like this:

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

The first checks added are simple checks to see if an item already exists in the db. I will have to do these checks for every entity I add to the db. I prefer to just add them without checks, catch the exception when SaveChanges is called and tell the user what went wrong. Saves me a lot of checking all through my application. I know I cann't check for every situation and thats why the DbResult class also has the DefaultErrorMessage property. This all works fine if I "crud" 1 entity at the time. The problem starts when adding multiple entities at once. Any suggestions on how i can improve my code so I can find out where something went wrong? Ideally after calling SaveChanges(). But any other ideas are welcome! Maybe changing a property on the DbContext which checks if an entity already exists if it is added to the contexts.

Is there a way when a single entity is beeing added the context can determine if it already exists in the context? — Kip ei, Sep 08 '17 at 11:21

score 7 · Answer 1 · answered Sep 10 '17 at 19:53

7

In case you'll call SaveChanges and it'll fails all the actions in the batch will be rolled back. On top of that you'll get an DbUpdateException with the property Entries that would contains entry/entries that causes an error. Context itself will still save the state of tracked objects (including failed) that you can get using ChangeTracker.Entries() (probably you wouldn't need it)

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

I your case, you can make a loop that will continue attempts till all will be saved something like

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

answered Sep 10 '17 at 19:53

ASpirin

3,601
1
23
32

1

In my experience, `DbUpdateException.Entries` always *only* contains the first entry that caused trouble, so this isn't suitable for batches. As for objects in the change tracker: sure, they're still there, but the state doesn't show which objects failed. If 100 were added and 50 had errors, all entities are still in the `Added` state. – Gert Arnold Sep 14 '17 at 06:28
The failed objects you still can define from the Exception and do something with it (e.g. detach as in a sample code). I'm agree that is it's the only first entity it can leads to repeating of the request in worst case as much as elements are marked to be saved. So the Developer should analyze the probability of invalid data and define the size of the batches. – ASpirin Sep 14 '17 at 06:39
On top of that we have [GetValidationErrors](https://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext.getvalidationerrors(v=vs.113).aspx) function, that allows to prevalidate our data before the actual call, but it's not saving from the db constraints for example. Maybe it's a good CR for EF team to improve support of batches – ASpirin Sep 14 '17 at 06:41
The loop is an interesting idea, but only if you are sure that continuing to update/insert is ok after the first failure. Any dependencies are obviously broken. Also, you can get the information about the specific failure from the `entry.Entity.GetType().Name` Also check out [this stack overflow question](https://stackoverflow.com/questions/5400530/validation-failed-for-one-or-more-entities-while-saving-changes-to-sql-server-da) for more example code parsing the errors. – biznetix Sep 15 '17 at 21:33
1

All the above comments and the answer itself seem to apply for EF6 - `System.Data.Entity`, `GetValidationErrors()` etc. EF Core (which is the target of the OP question) builds and executes SQL batches, so although there is `DbUpdateException.Entries`, in my tests it's **always empty**. – Ivan Stoev Sep 16 '17 at 10:49
Add the constraint to the database and `GetValidationErrors()` will not returns validation errors, but update still would fail, as it's said in comments above. As well as other types of system errors – ASpirin Sep 16 '17 at 11:15
1

@ASpirin There is no `GetValidationErrors()` method (and validation at all) in EFC. – Ivan Stoev Sep 16 '17 at 15:57
@IvanStoev ahh, true it's belongs to EF v6. ef core has only `DbUpdateException` – ASpirin Sep 16 '17 at 16:54

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

The reason is:

that when you add multiple entries to the DB, you create a list, or in the case of JSON an array of data.

As you can expect, you will receive the first element.

What you will need to do:

create an array for error messages, and push the exceptions into the array.

Then query the array and check whether the array has any messages or not, I would also consider a dictionary list rather then array, so you can have a fixed key for every entry, this way you can track which entry had problems.

so you will have a method that will look like this:

     public DbResult(Exception exception, ref List<string> exceptionArray)
        {
            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.";
exceptionArray.Add(this.DuplicateKeyErrorMessage);
                } 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.";
            }
        }

Adding batch of entities. How to determine which entities failed when calling SaveChanges()

2 Answers2

The reason is:

What you will need to do: