CODEWITHSUNDEEP
c#asp.netentity-framework
user2489102
user2489102

Reputation: 25

SqlException cause all subsequent db call to fail with same exception

I have an application on ASP.NET Core 2.2 and using Entity Framework Core 2.2.6.

I just encountered one problem. I am updating entities in the cycle, like this

   foreach (EntityDTO entityDTO in entityList)
   {
       try
       {
            DBOperationOne(entityDTO)
            DBOperationTwo(entityDTO)
       }
       catch (Exception e)
       {
           Console.WriteLine(e.ToString());
       }
   }

And for one entity I get this SQL exception

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

System.Data.SqlClient.SqlException: String or binary data would be truncated.

This is ok, I know why this happened, but the problem is next: after this error, all subsequent db calls for another entity result in the same exception! Even if it tries to update other fields with absolutely correct data! and if you try to run generated SQL for subsequent call outside of Entity Framework it will be completed successfully! But inside this loop all subsequent calls fails

My questions is: why this is happening and how I can avoid this behavior?

and here DBOperation methods example:

pulic Entity DBOperationOne(entityDTO)
{
    Entity entity = _mapper.Map<Entity>(entityDTO);
    _context.Entity.Add(entity);
    _context.SaveChanges();
    _context.Entry(entity).State = EntityState.Detached;
     return entity;
}

pulic Entity2 DBOperationTwo(entityDTO)
{
    Entity2 entity = _mapper.Map<Entity2>(entityDTO);
    _context.Entity2.Add(entity);
    _context.SaveChanges();
    _context.Entry(entity).State = EntityState.Detached;
     return entity;
}

Thanks for your help!

I've tried to place everything in the TransactionScope with RequiresNew transaction, but this do not help

Upvotes: 1

Views: 548

Answers (2)

James S
James S

Reputation: 3588

Well your line _context.Entry(entity).State = EntityState.Detached; happens after the error is thrown in .SaveChanges

So that will never happen (and hence the context still thinks it needs to be added the next time)

If you move the line _context.Entry(entity).State = EntityState.Detached; to a finally block of a try/finally then it'll be ok, as it will still happen in the event of an error in the SaveChanges().

EG

public Entity DBOperationOne(entityDTO)
{
    Entity entity = _mapper.Map<Entity>(entityDTO);
    try
    { 
        _context.Entity.Add(entity);
        _context.SaveChanges();
    }
    finally
    {
        _context.Entry(entity).State = EntityState.Detached;
    }
    return entity;
}

Upvotes: 0

Peter Dongan
Peter Dongan

Reputation: 2308

Your entity model still has the changes that you made that are causing the sql exception when you try to apply them to the database. So when you try to update the database with your next changes, it is trying to make the problematic update as well.

You can avoid it by adding validation to prevent your entity model being changed in a way that will break when you try to apply the changes to the database.

Upvotes: 1

Related Questions