Apply EF global query filter in an async way

Question

I need to apply a global query filter in my EF DbContext.

The value to check against is retrieved by an async operation.

So something like this.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>().HasQueryFilter(async u => u.CustomerId == await GetIdAsync());
    }

Of course this doesn't work, as HasQueryFilter expects a different lamda expression.

And I guess, that adding async to the OnModelCreating will bring me into troubles, too.

    protected override async void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        var id = await GetIdAsync();
        modelBuilder.Entity<User>().HasQueryFilter(u => u.CustomerId == id);
    }

Any ideas, how to solve this?

This is an async operation, not a query filter. Are you trying to create a multi-tenant application? Why would you need to find the customer's ID this way then? Isn't that already known when the DbContext is first created? — Panagiotis Kanavos, Nov 23 '22 at 18:06
You can't use *any* calls in a query filter anyway. That's a LINQ-to-EF `Where` call that must be translatable to SQL. Even a `GetId()` couldn't be translated — Panagiotis Kanavos, Nov 23 '22 at 18:10
Yes and no. It is indeed a multi-tenant approach. But retrieving the CustomerId unfortunately includes also an asynchronous step. — Thomas Mutzl, Nov 23 '22 at 18:13
I would have to run it synchronously, at least that's the solution I am aware of — Nikita Chayka, Nov 23 '22 at 19:20
If you cannot create a synchronous version of GetId (depending on how complex/computational this method is, it could be argued that maybe it should have been left synchronous) then have a read and understand options to call async code safely from synchronous. (https://stackoverflow.com/questions/9343594/how-to-call-asynchronous-method-from-synchronous-method-in-c) — Steve Py, Nov 23 '22 at 20:49
you should avoid `async void` because of the bad exception handling. In EFCore, OnModelCreating is called one time and the Model is cached by EFCore, you should not design it dynamically. — t.ouvre, Nov 24 '22 at 08:52
OK. According to the comments above, I cannot continue with setting Global Query Filters in an async way. So maybe a `DbCommandInterceptor` or `DbConnectionInterceptor` can solve my problem? — Thomas Mutzl, Nov 24 '22 at 13:12

score 2 · Accepted Answer · answered Nov 24 '22 at 15:28

Define property CustomerId in your DbContext:

public class MyDbContext: DbContext
{
    public int? CustomerId { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>().HasQueryFilter(u => CustomerId == null || u.CustomerId == CustomerId);
    }
}

Assign property in controller call:

context.CustomerId = await context.GetIdAsync();

And then you can sefely use Query Filter.

Apply EF global query filter in an async way

1 Answers1