how can I validate if there are no errors when executing SaveChangesAsync?

Question

I am testing a scenario where I am trying to save a duplicate element and it shows me the following error since the element must be unique (the name and fields of my table are in Spanish, for the example of my code, I preferred to put it in English):

`Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.Generos' with unique index 'IX_Generos_Nombre'.` **The duplicate key value is (Terror).**`
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---`

I want to do that if there is any error to save, I can customize the output message and not show the error message.

I also don't understand why the result variable is taken as an int.

[HttpPost]  // I am sending { Nombre: 'Terror'}
public async Task<ActionResult> Post([FromBody] GenreCreationDTO genreCreationDTO)
{
    var entity = mapper.Map<Genre>(genreCreationDTO);
    context.Add(entity);
    var result = await context.SaveChangesAsync(); ///////////////////// problem in this line
    //result is taken as int, why?
    if (!result)
    {
     //show my custom message
    }
     return Ok();
}

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/try-catch? — GSerg, Aug 21 '20 at 21:23
@GSerg is it the only way? I am new to c # I thought there was another way. What kind of exception do you recommend putting when I get some kind of problem related to the database? — yavg, Aug 21 '20 at 21:27
The `SaveChangesAsync` method returns the number of affected rows. — Alexander Petrov, Aug 21 '20 at 22:11
I guess you have an Id-property in both your GenreCreationDTO class and your Genre class. If this is e.g. an int or long and value > 0, this will give you a hint on what to do next. — Roar S., Aug 21 '20 at 23:33

Ivan Vargas · Answer 1 · 2020-08-21T23:05:22.347

-2

For a simple application, you could query the database before the insert to check if the name (or any other fields you want to be unique) already exists, and if there are results that match that criteria, then return some type of response telling so. Something like this

[HttpPost]
public async Task<ActionResult> Post([FromBody] GenreCreationDTO genreCreationDTO)
{
    var entity = mapper.Map<Genre>(genreCreationDTO);
    var matches = await context.Where(e => e.Name == entity.Name).Select(e => 1).ToListAsync();
   if(matches.Count == 0)
   {
       context.Add(entity);
       var result = await context.SaveChangesAsync();
       return Ok();
   }
   else
   {
     // show custom message
   }  
}

However, keep in mind the above approach will not work 100% for an application used by many clients at the same time. The problem is that there is no guarantee that the thread a request is running on will be suspended after checking if that name already exists but before the insert is done, and while that thread is suspended, another request comes in with the same name and insert the data into the database. Possibly the best thing to do is to handle the exception you are currently receiving in addition to check before the insert if that data already exists. The exception should be rare, it should only occur in a situation similar to the one described above, so, you can choose to not handle it and let the client receive a 500 error, then when the request is retried they will see why the prior request failed, or handle it and determine if the exception indeed occurred because of duplicate data.

As to why the result of SaveChangesAsync is an int, that represents the number of affected rows. In your case, how many rows were inserted.

EDIT: Modified query to include a projection to do SELECT 1 .... instead of SELECT *

edited Aug 21 '20 at 23:05

answered Aug 21 '20 at 21:48

Ivan Vargas

703
3
9

`var matches = await context.Where(e => e.Name == entity.Name).ToListAsync();` Imagine having a table with 500000 rows - What will this line of code do on the SQL server? Even though the database will only have one record based on the Validation - it will still need to check every single row for `e.Name == entity.Name` and then `.ToListAsync()` will load all those into Memory. – Dawood Awan Aug 21 '20 at 21:56
@DawoodAwan. I see your point, but how would you validate if the name exists to return a custom response? The exception thrown is a bit generic. The data does not have to be loaded, one could project and load a 1 for each matched row, and if data grows that much, possibly `Name` could be indexed to avoid checking every row. Or what would you propose instead? – Ivan Vargas Aug 21 '20 at 22:31
1

`var matches = await context.Where(e => e.Name == entity.Name).ToListAsync();` would execute something like `SELECT * FROM table WHERE name = 'entity.name'` and is easily and fast handled by all common SQL servers. Especially if the `name` column is indexed... – abto Aug 21 '20 at 22:31
@IvanVargas better to use `ANY` - the SQL generated is based on the `TOP (1)` - https://stackoverflow.com/questions/648795/what-is-the-fastest-way-to-determine-if-a-row-exists-using-linq-to-sql – Dawood Awan Aug 22 '20 at 07:07
@abto it maybe Fast for small tables with less number of columns and less number of Rows - but once that number goes up your App will take a performance hit – Dawood Awan Aug 22 '20 at 07:07
@DawoodAwan, in this particular case, there will be at most one row with the specified name because of the DB constraint. I haven’t done any stress test on this, but my intuition tells me the performance will be almost the same when the name does not exist and pretty close when the name does exist, especially if there is an index for it. On a different note, why downvote (if you were one of the downvoters) just because there might be a performance improvement to the proposed solution? – Ivan Vargas Aug 22 '20 at 09:03

how can I validate if there are no errors when executing SaveChangesAsync?

1 Answers1