Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 362
Kip ei Avatar asked Sep 08 '17 09:09

Kip ei


People also ask

How can we find entities in the DbSet find () method?

The Find method on DbSet uses the primary key value to attempt to find an entity tracked by the context. If the entity is not found in the context then a query will be sent to the database to find the entity there. Null is returned if the entity is not found in the context or in the database.

Which entities are not touched by the Entity Framework SaveChanges method?

Unchanged entities are not touched by SaveChanges. Updates are not sent to the database for entities in the Unchanged state.

How do I use SaveChanges in Entity Framework?

The SaveChanges method of the DbContext prepares the Insert , Update & Delete Queries. It does so by tracking the changes to each of the entities' Context is tracking. Whenever we query the database for entities, the Context retrieves them and mark the entity as Unchanged .

When would you use SaveChanges false AcceptAllChanges ()?

Sometimes though the SaveChanges(false) + AcceptAllChanges() pairing is useful. The most useful place for this is in situations where you want to do a distributed transaction across two different Contexts. If context1. SaveChanges() succeeds but context2.


2 Answers

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;
        }
    }
}
like image 191
ASpirin Avatar answered Sep 22 '22 10:09

ASpirin


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.";
            }
        }
like image 20
Barr J Avatar answered Sep 23 '22 10:09

Barr J