• Home
• Entity Framework Core
• Common EF Core mistake: Non-cancellable queries

Common EF Core mistake: Non-cancellable queries

Queries with Entity Framework can be cancellable, or not. The difference is that a non-cancellable query will run until completion even if its result is no longer needed. This results in useless queries that are executed for nothing on the database server, and also in some memory on the application server that is lost, because when Entity Framework gets the result of a SQL query it maps it before checking whether the result is going to be used.

This is a diagram of what happens when a client makes then cancels an HTTP request and the EF Core query is not cancellable:

The question now is: why run a SQL query when you already know that the user won't consume its result anyway?

When you implement cancellable queries in EF Core, when a client cancels an HTTP request EF Core will propagate this event to the database provider, which will handle it by cancelling or stopping the SQL query, preventing it from using any useless runtime.

So how do you make the SQL queries from Entity Framework cancellable?

This is easy: by using a CancellationToken. The asynchronous methods from Entity Framework Core like ToListAsync, ToDictionaryAsync or ToArrayAsync have an optional argument named cancellationToken of type CancellationToken which is a structure that propagates a notification when an operation should be canceled.

public async Task> GetCustomerSummariesByCountry(int countryId, CancellationToken cancellationToken)
{
	var query = _context.Customers
								.AsNoTracking()
								.Where(x=> x.CountryId == 1)
								.Select(x => new CustomerSummary
								{
									FirstName = x.FirstName,
									LastName = x.LastName,
									CountryId = x.CountryId,
									CountryName = x.Country.Name
								});
								
	List customers = await query.ToListAsync(cancellationToken);
}

We see here that the CancellationToken argument is passed to the EF queryable extension method ToListAsync.

This cancellation token can have different providers, depending on what you are using.

MediatR

MediatR provides it in the Handle method of the IRequestHandler interface:

using System.Threading;
using System.Threading.Tasks;

namespace MediatR;

/// 

/// Defines a handler for a request
/// 
/// The type of request being handled
/// The type of response from the handler
public interface IRequestHandler
    where TRequest : IRequest
{
    /// 

    /// Handles a request
    /// 
    /// The request
    /// Cancellation token
    /// Response from the request
    Task Handle(TRequest request, CancellationToken cancellationToken);
}

/// 

/// Defines a handler for a request with a void response.
/// 
/// The type of request being handled
public interface IRequestHandler
    where TRequest : IRequest
{
    /// 

    /// Handles a request
    /// 
    /// The request
    /// Cancellation token
    /// Response from the request
    Task Handle(TRequest request, CancellationToken cancellationToken);
}

Minimal API

In a minimal API you can inject a CancellationToken into the endpoint handler, the minimal API infrastructure automatically binds any CancellationToken parameters in a handler method to the HttpContext.RequestAborted token.

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.MapGet("/customers", async (CancellationToken token, ILogger logger) =>
{
    app.Logger.LogInformation("/customers endpoint was hit");

    // Do some stuff here, for now we simulate it with a delay.
    await Task.Delay(10_000, token);

    return Results.Ok();
});

app.Run();

In this case, you can pass the token parameter to the methods you will call, like a database repository.

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.MapGet("/customers", async ([FromRoute]int countryId, CancellationToken token, ILogger logger, ICustomerRepository customerRepository) =>
{
    app.Logger.LogInformation("/customers endpoint was hit");

    var customers = await customerRepository.GetCustomerSummariesByCountry(countryId, token);

    return Results.Ok();
});

app.Run();

You can also check the cancellation token status by yourself if you perform some synchronous work like a loop:

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.MapGet("/customers", async ([FromRoute]int countryId, CancellationToken token, ILogger logger, ICustomerRepository customerRepository) =>
{
    app.Logger.LogInformation("/customers endpoint was hit");

    for(int i = 0; i < 10; i++)
    {
        token.ThrowIfCancellationRequested();
		
        // Do work here
        Thread.Sleep(1000);
    }

    return Results.Ok();
});

app.Run();

Now if the request is canceled an OperationCanceledException will be thrown. Note that you don't have to throw an exception, you can also check the cancellation token status more gracefully, by using its IsCancellationRequested property:

app.MapGet("/customers", async ([FromRoute]int countryId, CancellationToken token, ILogger logger, ICustomerRepository customerRepository) =>
{
    app.Logger.LogInformation("/customers endpoint was hit");

    for(int i = 0; i < 10; i++)
    {
		while (!cancellationToken.IsCancellationRequested)
		{
			token.ThrowIfCancellationRequested();
			
			// Do work here
			Thread.Sleep(1000);
		}
    }

    return Results.Ok();
});

app.Run();

.NET Core standard Web API

In the same way as with minimal APIs, it's possible to inject a CancellationToken in the methods:

[HttpGet(Name = "Customers")]
public async Task> Get(CancellationToken cancellationToken)
{
	try
	{
		// Do some stuff here
	}
	catch(TaskCanceledException tce)
	{
		throw;
	}
}

I put some interesting links below in case you want to learn more about this.