All geek questions in one place

Adding a batch of objects. How to determine which entities arose when calling SaveChanges ()

I have a Tools_NawContext class extending the DbContext and DbResult class to slightly fix the result of the SaveChanges method when an exception occurs. When an exception is thrown, I create a specific error message that, as I know, belongs to the same object that I am trying to add, delete or modify. The user can take the appropriate 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, now I'm trying to import JSon and again want to use the TrySaveChanges method. However, this time, after some checks, I first add several objects to the context, not just 1. After everything is added, I call the TrySaveChanges method. It still works, but if something fails, I cannot determine which objects could not be saved. If I add 1000 entities and only 1 does not work, I will not be able to determine where this happened. How to determine which added objects throw errors? Below is an example of how I use it.

I have 2 classes generated by EF. 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 that has a method that adds newKeuring and a testresultatenList to 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); }

As I already said. I use it to import JSON. If the JSON file contains 68 lines, the method is called 68 times. Or say: 68 new Keuring elements Keuring attached to the DbContext, and also every time a Testresultaten list is added to the Testresultaten .

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

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

How can I achieve what I want? In the above example, in my MS SQL database, the Keuring table has a primary key Id , which automatically increments by db. The table also has the combined unique key GereedschapId and GekeurdOp .

I can write some checks before adding newKeuring to the context, for example:

 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 introduced are simple checks to check if an element exists in db. I will need to do these checks for every object that I add in db. I prefer to just add them without checking, throw an exception when SaveChanges is called, and tell the user what went wrong. Saves me a lot of checks throughout my application. I know that I can’t check every situation, and therefore the DbResult class also has the DefaultErrorMessage property. All this works fine if I "crud" 1 object at a time. The problem occurs when adding multiple objects at the same time. Any suggestions on how I can improve my code so that I can find out where something went wrong? Ideal after calling SaveChanges() . But any other ideas are welcome! Perhaps change the property to DbContext , which checks if an entity exists if it is added to contexts.

+5
source share
2 answers

If you call SaveChanges and it fails, all actions in the package will be discarded. In addition, you will get a DbUpdateException with the Entries property, which will contain the record / entries that lead to the error. The context itself will still retain the state of the monitored objects ( including the failed one ), which you can use with ChangeTracker.Entries() (you may not need this)

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

In your case, you can make a loop that will continue trying until everything is 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; } } }
+3
source

Cause:

that when adding several records to the database, you create a list or, in the case of JSON, an array of data.

As you would expect, you will get the first item.

What you need to do:

create an array for error messages and click exceptions in the array.

Then query the array and check if the array has any messages or not, I would also consider a list of dictionaries, not an array, so you can have a fixed key for each entry, so you can keep track of which record had problems.

so you will have a method that looks 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."; } }
+2
source

Source: https://habr.com/ru/post/1271635/

More articles:


All Articles