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

asp.net-core-mvc c# entity-framework-core

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.

1
6
9/8/2017 12:08:30 PM

Popular Answer

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;
        }
    }
}
4
9/10/2017 7:53:22 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow