C# EF core context Execution Timeout Expired

Question

I develop a console application to transfert data in a API. The application is called via a SQL Trigger. I use a dbcontext to get the data. it worked, but suddenly it doesn't work anymore.

here my code :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        "Data Source=.\SQLEXPRESS;Initial Catalog=mydatabase;User ID=sa;Password=password;;Trust Server Certificate=true;Encrypt=true;Connection Timeout=300",
    providerOptions =>
    {
        providerOptions.CommandTimeout(180);
    });
}

then in my program.cs

using (DB_DONNNESContext  context = new DB_DONNNESContext())    
{
    ListeLot lot = new ListeLot();

    try 
    {
        lot = context.ListeLots.Where(e => e.Id == Id).First();
        Log.Information("Get lot " + Lot.Id);
        Log.CloseAndFlush();
        return;
        
    } 
    catch (Exception ex) 
    {
        Log.Error($"Error cannot get lot {Id} " + ex.Message + " " + " / "+ context.ContextId);
        Log.CloseAndFlush();
        return;
    }
}

what's wrong ? my query is very simple... yesterday it's worked once and then I had this message. I have 6Go on the hard disk.

When I test in debug mode, it work. but when I Try to update via a sql query I have this message in the log file

Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  / Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade

It's strange cause I get a contextID. (I try in Loggin it)

I really need your help :)

have a nice day.

Answer 1

You may be encountering a deadlock. Querying a table as part of a Trigger could be potentially dangerous depending on your database isolation level.

If your code represents all you need to do, then it would be completely unnecessary and likely contributing to a deadlock scenario.

lot = context.ListeLots.Where(e => e.Id == Id).First();
Log.Information("Get lot " + Lot.Id);

This code is telling EF to load an entity from the database, and there appears to be a typo or a reference issue given the "lot" being loaded is "lot" but your Log.Information call is referencing "Lot". This could be a typo getting your code into the StackOverflow question, or your code is attempting to reference a module level/global property called "Lot". C# is case sensitive but you can run into issues like this quite easily when reusing variable names varying by case.

If you just want to check if a Lot record exists without loading the entire thing into memory (triggering a read lock for the row which may be deadlocking with the trigger or other DB operations) then instead use the following:

var lotExists = context.ListeLots.Any(e => e.Id == Id);
if(lotExists)
    Log.Information($"Get lot {Id}");
else
    Log.Warning($"Lot {Id} not found.");
Log.CloseAndFlush();

Rather than loading the Log entity, doing an Any check will just return if the value exists. This should amount to an index scan/seek and unlikely to deadlock.

Edit: If you need to get a couple of columns from a query and want to improve the chance to avoid the deadlock, one option would be to add those columns as included fields on an index. For instance if you want to get a Name column from a lot, create an index on ID with an included column for Name. Then when fetching your Id and Name from the ListeLots:

var lot = context.ListeLots
    .Where(e => e.Id == Id)
    .Select(e => new { e.Id, e.Name })
    .SingleOrDefault();

When EF composes this to SQL and executes it on the server, SQL Server should optimize this to pull the values from the index rather than the table. A caveat though if you are searching for a data row that has been updated (I.e. you are searching for a row that the trigger is executing after an update on) then this will most likely still deadlock, especially if the Name had been updated. Indexes can help bypass deadlock scenarios due to row locks, but they aren't immune to them. This can generally improve query performance, but comes at a cost of storage/memory use on the DB server as well as update performance costs. Generally it can be a good trade off for a small number of commonly queried, small fields. However, as soon as you request a column not in the index it will return to the table data.