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();
}

learn.microsoft.com/en-us/dotnet/csharp/language-reference/…? — GSerg
– GSerg, Commented Aug 21, 2020 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
– yavg, Commented Aug 21, 2020 at 21:27
The SaveChangesAsync method returns the number of affected rows. — Alexander Petrov
– Alexander Petrov, Commented Aug 21, 2020 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.
– Roar S., Commented Aug 21, 2020 at 23:33

Ivan Vargas · Accepted Answer · 2020-08-21 23:05:22Z

-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, 2020 at 23:05

answered Aug 21, 2020 at 21:48

Ivan Vargas

7033 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dawood Awan Over a year ago

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.

Ivan Vargas Over a year ago

@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?

abto Over a year ago

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...

Dawood Awan Over a year ago

@IvanVargas better to use ANY - the SQL generated is based on the TOP (1) - stackoverflow.com/questions/648795/…

Dawood Awan Over a year ago

@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

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related