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.
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.
Unchanged entities are not touched by SaveChanges. Updates are not sent to the database for entities in the Unchanged state.
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 .
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.
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;
}
}
}
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.
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.";
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With