Prevent synchronous API usage with EF Core

The solution is to use a command interceptor.

public class AsyncOnlyInterceptor : DbCommandInterceptor
{
    public bool AllowSynchronous { get; set; } = false;

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        ThrowIfNotAllowed();
        return result;
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        ThrowIfNotAllowed();
        return result;
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        ThrowIfNotAllowed();
        return result;
    }

    private void ThrowIfNotAllowed()
    {
        if (!AllowSynchronous)
        {
            throw new NotAsyncException("Synchronous database access is not allowed. Use the asynchronous EF Core API instead.");
        }
    }
}

If you're wanting to write some tests for this, you can use a Sqlite in-memory database. The Database.EnsureCreatedAsync() method does use synchronous database access, so you will need an option to enable this for specific cases.

public partial class MyDbContext : DbContext
{
    private readonly AsyncOnlyInterceptor _asyncOnlyInterceptor;

    public MyDbContext(IOptionsBuilder optionsBuilder)
        : base(optionsBuilder.BuildOptions())
    {
        _asyncOnlyInterceptor = new AsyncOnlyInterceptor();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.AddInterceptors(_asyncOnlyInterceptor);
        base.OnConfiguring(optionsBuilder);
    }

    public bool AllowSynchronous
    {
        get => _asyncOnlyInterceptor.AllowSynchronous;
        set => _asyncOnlyInterceptor.AllowSynchronous = value;
    }
}

Here are some helpers for testing. Ensure you aren't using sequences (modelBuilder.HasSequence) because this is not supported by Sqlite.

public class InMemoryOptionsBuilder<TContext> : IOptionsBuilder
    where TContext : DbContext
{
    public DbContextOptions BuildOptions()
    {
        var optionsBuilder = new DbContextOptionsBuilder<TContext>();
        var connection = new SqliteConnection("Filename=:memory:");
        connection.Open();
        optionsBuilder = optionsBuilder.UseSqlite(connection);
        return optionsBuilder.Options;
    }
}

public class Helpers
{
    public static async Task<MyDbContext> BuildTestDbContextAsync()
    {
        var optionBuilder = new InMemoryOptionsBuilder<MyDbContext>();
        var context = new MyDbContext(optionBuilder)
        {
            AllowSynchronous = true
        };
        await context.Database.EnsureCreatedAsync();
        context.AllowSynchronous = false;
        return context;
    }
}

How can I prevent synchronous database access with Entity Framework Core?

You can not. Period. THere is also no reason for this ever. You basically assume programmers using your API either are idiots or malicious - why else would you try to stop them from doing something that is legal in their language?

I have tried using a DbCommandInterceptor, but none of the interceptor methods are called when testing with an in-memory database

There are a TON of problems with the in memory database. I would generally suggest not to use it - like at all. Unless you prefer a "works possibly" and "never actually use advanced features of the database at all". It is a dead end - we never do unit testing on API like this, all our unit tests actually are integration tests and test end to end (vs a real database).

In memory has serious no guarantee to work in anything non trivial at all. Details may be wrong - and you end up writing fake tests and looking for issues when the issue is that the behavior of the in memory database just is a little different than the real database. And let's not get into what you can do with the real database that in memory has no clue how to do to start with (and migrations also do not cover). Partial and filtered indices, indexed views are tremendous performance tools that can not be properly shown. And not get into detail differences for things like string comparisons.

But the general conclusion is that it is not your job to stop users from calling valid methods on EfCore etc. and you are not lucky to actually do that - not a scenario the team will ever support. There are REALLY good reasons at time to use synchronous calls - in SOME scenarios it seems the async handling is breaking down. I have some interceptors (in the http stack) where async calls just do not work. Like never return. Nothing I ever tried worked there - so I do sync calls when I have to (thank heaven I have a ton of caching in there).

You can prevent it at compile-time to some degree by using the Microsoft.CodeAnalysis.BannedApiAnalyzers NuGet package. More information about it here.

Methods that end up doing synchronous queries can then be added to BannedSymbols.txt, and you will get a compiler warning when attempting to use them. For example adding the following line to BannedSymbols.txt gives a warning when using First() on an IQueryable<T>:

M:System.Linq.Queryable.First`1(System.Linq.IQueryable{``0});Use async overload

These warnings can also be escalated to become compiler errors by treating warnings as errors as explained here: https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/compiler-options/errors-warnings

Unfortunately not all synchronous methods can be covered by this approach. For example since ToList() is an extension on IEnumerable<T> (and not on IQueryable<T>), banning it will not allow any use of ToList() in the same project.

I can't really find a good Google answer for you. So my suggestion in the meantime is that you start doing peer-review, aka Code Reviews and any time you find a .Tolist(), you change it to await .ToListAsync().

It's not the most high tech solution, but it does keep everyone honest, but it also allows others to become familiar with your work should they ever need to maintain it while you're booked off sick.