Support multiple HasQueryFilter calls on same entity type

[nphmuller]

As of 2.0 multiple HasQueryFilter() calls on EntityTypeBuilder result in only the latest one being used. It would be nice if multiple query filters could be defined this way.

Example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyEntity>()
        .HasQueryFilter(e => e.IsDeleted == isDeleted)
        .HasQueryFilter(e => e.TenantId == tenantId);
}

// In application code 
myDbContext.Set<MyEntity>().ToList() // executed query only has the tenantId filter.

Current workaround is to define all filters in single expression, or rewrite the expression to concat multiple filters.

[nphmuller]

I've set up a Gist which shows my query filter use-case and the code I had to write to get it working. https://gist.github.com/nphmuller/8891c315d79aaaf720f9164cd0f10400 https://gist.github.com/nphmuller/05ff66dfa67e1d02cdefcd785661a34d

[anpete]

This is currently by-design as it is usually pretty easy to combine filters with '&&'. Filters can also be unset by passing null and so it is not just a matter of allowing HasQueryFilter to be called multiple times.

[nphmuller]

@anpete - Sure, than consider this a feature request. ;)

When the filters can't be combined easily with &&, you have to use the Expression API to combine filters. Basically like the Gist I posted. Although the Gist can probably be simplified quite a bit, especially when ReplacingExpressionVisitor doesn't have to be used anymore.

However, say I want to combine multiple filters based on different base/interface types. Like a type that implements both ISoftDeletableEntity and ITenantEntity. I don't think it's possible to wire both of these up in a single HasQueryFilter call, since TEntity in Expression<Func<TEntity, bool>> is different.

[challamzinniagroup]

I would second a vote for this feature as I just hit it myself with the exact two scenarios mentioned; soft deletes and multi-tenancy. I was able to combine filters for my current use-case; but there can certainly be issues where the code to calc filters based on model inheritance could get awful "gummy" in a single filter declaration.

[nphmuller]

Here's a gist of the workaround we have implemented: https://gist.github.com/nphmuller/05ff66dfa67e1d02cdefcd785661a34d

Edit: Woops, looks like I've already posted a Gist a year ago. The code in this one is a bit more cleaned up though. :)

[Levitikon217]

Interestingly the "Global Query Filters" documentation written 9 days before this bug was opened suggests calling HasQueryFilter multiple times. However I am still seeing this issue today in 2.1, 1 year later. Is there any updates on this? Please remove this documentation as it clearly doesn't work.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");

    // Configure entity filters
    modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
    modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}

https://docs.microsoft.com/en-us/ef/core/querying/filters

[smitpatel]

@Felixking - Those HasQueryFilter calls are on different entity types. You can call HasQueryFilter once per entity type. This issue talks about calling it multiple times on same entity.

[YZahringer]

My workaround with extension methods:

internal static void AddQueryFilter<T>(this EntityTypeBuilder entityTypeBuilder, Expression<Func<T, bool>> expression)
{
    var parameterType = Expression.Parameter(entityTypeBuilder.Metadata.ClrType);
    var expressionFilter = ReplacingExpressionVisitor.Replace(
        expression.Parameters.Single(), parameterType, expression.Body);

    var internalEntityTypeBuilder = entityTypeBuilder.GetInternalEntityTypeBuilder();
    if (internalEntityTypeBuilder.Metadata.QueryFilter != null)
    {
        var currentQueryFilter = internalEntityTypeBuilder.Metadata.QueryFilter;
        var currentExpressionFilter = ReplacingExpressionVisitor.Replace(
            currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body);
        expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter);
    }

    var lambdaExpression = Expression.Lambda(expressionFilter, parameterType);
    entityTypeBuilder.HasQueryFilter(lambdaExpression);
}

internal static InternalEntityTypeBuilder GetInternalEntityTypeBuilder(this EntityTypeBuilder entityTypeBuilder)
{
    var internalEntityTypeBuilder = typeof(EntityTypeBuilder)
        .GetProperty("Builder", BindingFlags.NonPublic | BindingFlags.Instance)?
        .GetValue(entityTypeBuilder) as InternalEntityTypeBuilder;

    return internalEntityTypeBuilder;
}

Usage:

if (typeof(ITrackSoftDelete).IsAssignableFrom(entityType.ClrType))
    modelBuilder.Entity(entityType.ClrType).AddQueryFilter<ITrackSoftDelete>(e => IsSoftDeleteFilterEnabled == false || e.IsDeleted == false);
if (typeof(ITrackTenant).IsAssignableFrom(entityType.ClrType))
    modelBuilder.Entity(entityType.ClrType).AddQueryFilter<ITrackTenant>(e => e.TenantId == MyTenantId);

[mguinness]

Managing global filters can certainly become unwieldy. I have filters based on user roles and using HasQueryFilter isn't really cutting it and something like PredicateBuilder would be really useful.

Combining filters seems to be a common use case including this SO question but the solution can be difficult for beginners to follow. Can extension methods like these be added into EF Core, or would a separate nuget package be more expedient?

[cgountanis]

When doing multiple includes and using HasQueryFilter on say, objects with child objects which all have a IsDeleted flag, the execute goes up to 14,000 MS. Is there a solution to this? I would rather not include deleted records in the context vs, the business or presentation layer.

In my case the soft delete is a DateTime? and I only include where IS NULL, could that be the issue? IS a bool faster than a IS NULL check?

[ajcvickers]

@cgountanis Please file a new issue and include a small, runnable project solution or complete code listing that demonstrates the behavior you are seeing.

[ajcvickers]

@cgountanis Just saw #15996. Thanks!

[haacked]

Here's a scenario where support for multiple HasQueryFilter calls on the same entity would be useful. https://haacked.com/archive/2019/07/29/query-filter-by-interface/

In short, I wrote a method that lets you do this:

modelBuilder.SetQueryFilterOnAllEntities<ITenantEntity>(e => e.TenantId == tenantId);
modelBuilder.SetQueryFilterOnAllEntities<ISoftDeletable>(e => !e.IsDeleted);

What that code does is is find all entities that implement the interface and adds the specified query filter to the entity (some expression tree rewriting is involved).

However, this doesn't work in the case where an entity implements both interfaces because the last query filter overwrites the previous one.

[haacked]

I want to note that I can update my own method now that I know about this behavior, but it was surprising. It lead to entities from one tenant bleeding into another until i figured out what was going on.

So in short, I think it's surprising that the last HasQueryFilter call wins. I'd rather it throw an exception if called twice on the same entity, or that it combine query filters per this issue.

[haacked]

Actually, as I think about it, throwing an exception could be problematic if you were appending to an existing query filter by overwriting it. Perhaps adding an AppendQueryFilter method would be useful which would be explicit. Then you could safely throw on multiple calls to HasQueryFilter. Since the primary use case is soft deletes and multi-tenancy, there's security implications for users of the API getting it wrong.

[ajcvickers]

@haacked Thanks for the feedback. We agree that there is a usability issue here. We can't do anything here for 3.0, but for a future release we would like to make it possible to:

• Add multiple filters independently of each other--tracked by this issue
• Allow those filters to be named in some way (#8576) such that enabling/disabling (#17347) a query filter is also independent of other filters
• We may also want to make it easier to use the same filter across multiple types in the model (#17434)

Also, we plan to implement filtered Include (#1833) for more localized ad-hoc filtering.

[haacked]

Those all sound great! Thanks for following up.

[pantonis]

@YZahringer Any workaround for .net 3.0

[YZahringer]

@pantonis Updated to EF Core 3.1:

internal static void AddQueryFilter<T>(this EntityTypeBuilder entityTypeBuilder, Expression<Func<T, bool>> expression)
{
    var parameterType = Expression.Parameter(entityTypeBuilder.Metadata.ClrType);
    var expressionFilter = ReplacingExpressionVisitor.Replace(
        expression.Parameters.Single(), parameterType, expression.Body);

    var currentQueryFilter = entityTypeBuilder.Metadata.GetQueryFilter();
    if (currentQueryFilter != null)
    {
        var currentExpressionFilter = ReplacingExpressionVisitor.Replace(
            currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body);
        expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter);
    }

    var lambdaExpression = Expression.Lambda(expressionFilter, parameterType);
    entityTypeBuilder.HasQueryFilter(lambdaExpression);
}

Usage:

if (typeof(ITrackSoftDelete).IsAssignableFrom(entityType.ClrType))
    modelBuilder.Entity(entityType.ClrType).AddQueryFilter<ITrackSoftDelete>(e => IsSoftDeleteFilterEnabled == false || e.IsDeleted == false);
if (typeof(ITrackTenant).IsAssignableFrom(entityType.ClrType))
    modelBuilder.Entity(entityType.ClrType).AddQueryFilter<ITrackTenant>(e => e.TenantId == MyTenantId);

[mhosman]

Hey @YZahringer, thanks for the updated workaround script. Do you have any example about how to disable a specific filter for a given entity in Linq, using that approach? Thanks!

[YZahringer]

@mhosman You can define a simple bool property in your DbContext like bool IsSoftDeleteFilterEnabled { get; set; } and check it in your filter.

To be more dynamic/focussed, I suppose you can use an array of strings to enable named filters and use contains in your filter.

[smitpatel]

We could combine multiple query filter calls. And add API HasNoQueryFilter to remove the filter.

[mguinness]

Has anyone used DynamicFilters package? On the surface it seems to provide functionality that some are requesting here. The ability enable a filter under specific condition (i.e. from HttpContext) seems very powerful. Hopefully some ideas can be incorporated into EF Core when the design stage for this issue is worked on.

[levitation]

@YZahringer

Use this EntityTypeBuilder<T> (note the generic <T>) to make calling the function less verbose. Then you do not need to specify the type at the calling site unless you want to.

My workaround with extension methods:

internal static void AddQueryFilter<T>(this EntityTypeBuilder entityTypeBuilder, Expression<Func<T, bool>> expression)
...

[ghost]

@YZahringer thanks for your answer. I modified your code a little bit.

    public static class EntityFrameworkExtensions
    {
        public static void AddQueryFilterToAllEntitiesAssignableFrom<T>(this ModelBuilder modelBuilder,
            Expression<Func<T, bool>> expression)
        {
            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                if (!typeof(T).IsAssignableFrom(entityType.ClrType))
                    continue;

                var parameterType = Expression.Parameter(entityType.ClrType);
                var expressionFilter = ReplacingExpressionVisitor.Replace(
                    expression.Parameters.Single(), parameterType, expression.Body);

                var currentQueryFilter = entityType.GetQueryFilter();
                if (currentQueryFilter != null)
                {
                    var currentExpressionFilter = ReplacingExpressionVisitor.Replace(
                        currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body);
                    expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter);
                }

                var lambdaExpression = Expression.Lambda(expressionFilter, parameterType);
                entityType.SetQueryFilter(lambdaExpression);
            }
        }
    }

and usage

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfigurationsFromAssembly(GetType().Assembly);

            modelBuilder.AddQueryFilterToAllEntitiesAssignableFrom<ISoftDeletableEntity>(x => x.IsDeleted == false);
            modelBuilder.AddQueryFilterToAllEntitiesAssignableFrom<ITenantEntity>(x => x.TenantId == _securityContext.LoggedUser.TenantId);
        }

[magiak]

If all you need is to append a new query filter you can use (EF Core 5.0.2)

     public static void AppendQueryFilter<T>(
          this EntityTypeBuilder<T> entityTypeBuilder, Expression<Func<T, bool>> expression)
          where T : class
      {
        var parameterType = Expression.Parameter(entityTypeBuilder.Metadata.ClrType);

        var expressionFilter = ReplacingExpressionVisitor.Replace(
            expression.Parameters.Single(), parameterType, expression.Body);

        if (entityTypeBuilder.Metadata.GetQueryFilter() != null)
        {
            var currentQueryFilter = entityTypeBuilder.Metadata.GetQueryFilter();
            var currentExpressionFilter = ReplacingExpressionVisitor.Replace(
                currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body);
            expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter);
        }

        var lambdaExpression = Expression.Lambda(expressionFilter, parameterType);
        entityTypeBuilder.HasQueryFilter(lambdaExpression);
    }

it does not use internal EF api as previous answers so it should be quite stable :)

[haacked]

@magiak thank you for this! I finally got around to updating my website to EF Core 5 and tested this out and it worked like a charm.

I also updated my blog post to mention this code and credit you.

[sguryev]

it does not use internal EF api as previous answers so it should be quite stable :)

@magiak what internal EF api are talking about?

[Krzysztofz01]

Recently, I tried to work around the problem of being able to only use the last query filter. I have created an extension that allows the use of multiple filters and it is also possible to control which query filter is active with services injected into DbContext.

EFCore.QueryFilterBuilder

Example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //Fluent API
    modelBuilder.Entity<Blog>()
        .HasQueryFilter(QueryFilterBuilder<Blog>
            .Create()
            .AddFilter(b => b.Name == "Hello World")
            .AddFilter(b => b.Posts == 20, _injectedService.ShouldApplyFilter())
            .Build());

    //As a last command, you can call Build(), 
    //but you don't have to because it will be called automatically.
}

[ErikEJ]

Feel free to add this to the extensions list in docs.

[mguinness]

it is also possible to control which query filter is active with services injected into DbContext.

Since OnModelCreating runs only once and the model is cached, could the bool parameter contain something like accessor.HttpContext.User.IsInRole("Admin") to enable only when user is in a particular role? If so, this would be an easier way to manage global query filters. I hope more people upvote this issue so it can be improved for the next release.

[Krzysztofz01]

it is also possible to control which query filter is active with services injected into DbContext.

Since OnModelCreating runs only once and the model is cached, could the bool parameter contain something like accessor.HttpContext.User.IsInRole("Admin") to enable only when user is in a particular role? If so, this would be an easier way to manage global query filters. I hope more people upvote this issue so it can be improved for the next release.

Sure, the only thing you need to do, is inject the IHttpContextAccesor service into DbContext, or a even better approach is to create a wrapper for the IHttpContextAccesor and create methods like: IsAdmin() etc.

[ajcvickers]

Also consider #26146

[jzabroski]

I think the workaround for this problem, other than the one @nphmuller put together, is to abstract away protected override void OnModelCreating(ModelBuilder modelBuilder) via creating your own IEntityConfiguration that internally forwards calls to entity framework. In this way, your configuration layer can have a List of Conditions and you can Fold those conditions together in the same way @anpete suggests you manually do with && inside a single Where clause, and it can all be injected at run-time (or statically now with C# code generators). - This is similar to what #26146 champions, I guess. - This approach of abstracting away entity configuration is what I usually do, as I don't want my model registration layer to be directly dependent on a specific Entity Framework library (EF6 vs. EFCore) or even a specific ORM (yes, it's a lot of work to swap out to something like NHibernate, but it allows me to keep a consistent set of interfaces across various customer projects, so the point isn't swap-ability as it is a fiddling layer that allows me to try to keep behavior consistent across all the ORM frameworks I have used).

I've long wondered why there isn't a marker interface for these sorts of things. Every project I've worked on in the last 10 years pretty much has an IEntity, IEntity<TKey> where TKey : struct { TKey Id { get; set }, ILongEntity : IEntity<long>, etc. as well as interfaces for abstracting across ORMs. I understand some developers want to do things in a certain "pure way", but, let's be real, a quick examination of various .NET line of business GitHub reference architectures show we all more or less are doing the same thing. Some of us just want to glorify our particular approaches, and I'm not that dogmatic about mine.

I think this would also (long-term) allow some standards for ORMs to specify metadata, similar to Microsoft.Extensions.DependencyInjection's 50 rules for IoC conformance. Ideally, that metadata layer is separate from any particular ORM. After all, we pretty much had these design patterns defined ~20 years ago by Fowler:

• Two-way mapping between object properties and database columns
• Mapping 1-1, 1-n, n-m associations between objects to relationships between tables
• Lazy loading (proxy)
• Object caching (identity map)

[Prinsn]

Recently, I tried to work around the problem of being able to only use the last query filter. I have created an extension that allows the use of multiple filters and it is also possible to control which query filter is active with services injected into DbContext.

EFCore.QueryFilterBuilder

Example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //Fluent API
    modelBuilder.Entity<Blog>()
        .HasQueryFilter(QueryFilterBuilder<Blog>
            .Create()
            .AddFilter(b => b.Name == "Hello World")
            .AddFilter(b => b.Posts == 20, _injectedService.ShouldApplyFilter())
            .Build());

  //As a last command, you can call Build(), 
  //but you don't have to because it will be called automatically.
}

@Krzysztofz01 Does this package allow you to append to existing? Currently extending off of a base DbContext which applies one that we're trying to preserve while appending more

[Krzysztofz01]

Recently, I tried to work around the problem of being able to only use the last query filter. I have created an extension that allows the use of multiple filters and it is also possible to control which query filter is active with services injected into DbContext. EFCore.QueryFilterBuilder Example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //Fluent API
    modelBuilder.Entity<Blog>()
        .HasQueryFilter(QueryFilterBuilder<Blog>
            .Create()
            .AddFilter(b => b.Name == "Hello World")
            .AddFilter(b => b.Posts == 20, _injectedService.ShouldApplyFilter())
            .Build());

    //As a last command, you can call Build(), 
    //but you don't have to because it will be called automatically.
}

@Krzysztofz01 Does this package allow you to append to existing? Currently extending off of a base DbContext which applies one that we're trying to preserve while appending more

Currently, the EFCore.QueryFilterBuilder is working more like a LINQ Expressions compiler wrapper with an EntityFramework-like API. The current implementation is not capable of editing query filters which were added using the default API. I'm doing some research to make this tool more versatile, by experimenting and overriding some DbContext default behaviour.

[diegofernandes-dev]

My solution:

modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted && y.TenantId == tenantId);

[HerveZu]

Based on @nphmuller's gist, I created a nuget package.