CODEWITHSUNDEEP
c#asp.net-coreentity-framework-coreasp.net-core-signalr
mode777
mode777

Reputation: 3197

Entity Framework Core: Unable to catch SqlException

I'm trying to implement some optimistic locking when inserting rows into a database table.

What I'm trying to do is, acquire a row from the database. If a row with given key does not exist, it is inserted first. I'm currently testing this for high concurrency scenarios.

The idea is: if the row was already added by another thread, the exception is caught and the entity is re-read from the database. However, I do not seem to be able to catch the exception.

Here's the method in question

    private async Task<Counter> AcquireCounterAsync(string resType)
    {
        var repo = _dbContext.Set<Counter>();

        while (true)
        {
            var ctr = await repo.FindAsync(resType);

            if (ctr != null)
                return ctr;

            ctr = new Counter(resType);

            try
            {
                Console.WriteLine("Trying to add " + resType);
                repo.Add(ctr);
                await _dbContext.SaveChangesAsync();
                Console.WriteLine("Created " + resType);
                return ctr;
            }
            catch (SqlException ex)
            {
                // this block is never entered
                Console.WriteLine("SqlException was thrown: " + ex.Message);
                continue;
            }
        }            
    }

Here's my log, the exception is in German but it's just the expected violation of primary key constraint:

Trying to add test/test0
Trying to add test/test0
Created test/test0
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (157ms) [Parameters=[@p0='?' (Size = 128), @p1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [counters] ([ResourceType], [Value])
      VALUES (@p0, @p1);
System.Data.SqlClient.SqlException (0x80131904): Verletzung der PRIMARY KEY-Einschränkung 'PK_counters'. Ein doppelter Schlüssel kann in das dbo.counters-Objekt nicht eingefügt werden. Der doppelte Schlüsselwert ist (test/test0).
Die Anweisung wurde beendet.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
ClientConnectionId:d1f14d55-c95b-4c19-b1bc-468241df225d
Error Number:2627,State:1,Class:14

The class in question is transiently injected into a signalr hub. I feel that I might be doing something fundamentally wrong, but I can't see it yet.

EDIT: I tried to make this method synchonous too, same result, just a slightly longer stack-trace going down to the invocation at the SignalR hub. So it's definitly not caught.

MY FIX: To clarify: I fixed this by catching "DbUpdateException" AND making all methods synchronous. I still can't catch the "DbUpdateException" or even "Exception" if I'm using the async methods. So this is only partially related to this Question: Cannot catch SqlException in Entity Framework

Upvotes: 2

Views: 1805

Answers (1)

J&#252;rgen R&#246;hr
J&#252;rgen R&#246;hr

Reputation: 949

I've set up a small demo like this

    var uid = Guid.NewGuid();
    using (var context = new DemoContext())
    {
       context.Stuff.Add(new Stuff { Uid = uid, Data = "stuff1" });
       await context.SaveChangesAsync();
    }
    using (var context = new DemoContext())
    {
       context.Stuff.Add(new Stuff { Uid = uid, Data = "stuff2" });
       await context.SaveChangesAsync();
    }

This is wrapped in a try ... catch block and I can catch the thrown exception, but it is of type Microsoft.EntityFrameworkCore.DbUpdateException with a System.Data.SqlClient.SqlException like your's wrapped inside.

Do you catch somewhere else and rethrow only the inner exception? Try catching Exception in your code, just to make sure.

Upvotes: 2

Related Questions