Allow global query filters to be defined on a derived Entity Type of a hierarchy

[HappyNomad]

The What's New doc says,

Limitations

• Navigation references are not allowed. This feature may be added based on feedback.
• Filters can only be defined on the root Entity Type of a hierarchy

The first limitation says "This feature may be added based on feedback.", and I found #8881 covers it. The second limitation doesn't say that. Does that mean that limitation will likely never be removed?

It seems I could partially get around the second limitation by writing:

HasQueryFilter( a => !(a is Subclass && a.IsFoo) )

Will this work? (I haven't been able to confirm yet due to unrelated strange errors.) What about accessing a property on the subclass like the following?

HasQueryFilter( a => !(a is Subclass && a.IsFoo && ((Subclass)a).IsBar) )

When I tried this, I get the error, "No coercion operator is defined between types" where one type isn't even used in the expression. It would be great if it does work, but removing the second limitation would allow nicer syntax.

[ajcvickers]

@HappyNomad Everything is always open to change based on feedback. It was emphasized for navigation references because we explicitly wanted to elicit feedback on that one since we wanted to see what kinds of ways people wanted to use navigation properties so that we could look at how difficult it made things with this limitation in place. This would in turn drive the priority of adding support.

For inheritance, the implementation was investigated and it was quite complex. At the time we felt that the value would not out-weigh the cost, but if it is something that enough people want, then we could reconsider this. Leaving this open so we can discuss the priority again in triage.

[tjmcnaboe]

@ajcvickers @HappyNomad I used the syntax like you suggested as a workaround and it seems to be working ok for me with a relatively small dataset.
modelBuilder.Entity<Truck>().HasQueryFilter(c => ((CompanyTruck)c).CompanyId == _resolver.CompanyId);
With the caveat that if you want to use the base class you need to turn off the query filter using the ignore query filter syntax or you will get the no coercion operator exception
await _context.Trucks.IgnoreQueryFilters().ToListAsync());

[zinov]

I am getting the same problem with this scenario.

https://stackoverflow.com/questions/50476427/filter-expression-cannot-be-specified-for-an-entity-type-because-it-may-only-be

[reloaded]

+1 It would be great to have the ability to write EF queries that filter based on inheritance.

My use case is simple, I have a hierarchy of log entities that inherit from a base log entity. The discriminators are success, error, info, diagnostics, etc. I'd like to write a single query against the base type and filter it so I get all but Diagnostics back.

Pseudo code below.

ApplicationDbContext {
  DbSet<Log> Logs {get;set;}
  DbSet<ErrorLog> ErrorLogs {get;set;}
  DbSet<InfoLog> InfoLogs {get;set;}
  DbSet<SuccessLog>Successlogs {get;set}
  DbSet<DiagnosticLog>DiagnosticLogs {get;set}
}

LogRepository {
  ApplicationDbContext _context;

  LogRepository(ApplicationDbContext) {}

  IEnumerable<Log> AllExceptDiagnostics() {
     return _context.Logs.Where(e => !(e is DiagnosticLog)).ToList();
  }
}

[smitpatel]

@reloaded - Since you want to filter on base type, you should be able to do that even with current functionality.

[reloaded]

@reloaded - Since you want to filter on base type, you should be able to do that even with current functionality.

Could you advise me on how to do this in a way that does the filtering in SQL? When I attempted to achieve this type filtering by using the type "Is" operator was not translated to SQL so LINQ ran the filter in the app.

Here's the code I was using before I gave up and wrote 4 separate LINQ queries. The requirements are to count how many logs of each type there are where ImportOperationId is equal to some ID that identifies the group of logs.

            var totals = _dbSet
                .Where(logs => logs.ImportOperationId == operationId)
                .GroupBy(logs =>
                        logs is WarningLogState ? "Warnings"
                        : logs is FailedLogState ? "Failed"
                        : logs is SkippedLogState ? "Skipped"
                        : logs is SuccessLogState ? "Successful"
                        : "",
                    (logType, results) => new
                    {
                        LogType = logType,
                        Count = results.Count()
                    }
                )
                .ToDictionary(selector => selector.LogType);
            

[smitpatel]

    public class Blog
    {
        public int Id { get; set; }
    }

    public class SpecialBlog :Blog
    {
    }

// Query Filter
modelBuilder.Entity<Blog>().HasQueryFilter(e => !(e is SpecialBlog));

// Query
var query = db.Blogs.ToList();
// Generates SQL
      SELECT [e].[Id], [e].[Discriminator]
      FROM [Blogs] AS [e]
      WHERE [e].[Discriminator] IN (N'SpecialBlog', N'Blog') AND NOT ([e].[Discriminator] = N'SpecialBlog')

The filter you want to apply is working for me. I am not sure why are you facing issue with it. Please file a new issue with detailed repro for the issue you are seeing.

[reloaded]

HasQueryFilter

Interesting, so it looks like you're using the EF model builder API in ApplicationDbContext to ensure the said filter is applied to the entire DbSet.

Is there a way to achieve this functionality without baking it into the EF Model? Ideally I want to write multiple repository methods, each querying against the same EF DbSet, but each repository method can optionally filter the query further based on the discriminator.

There will be REST APIs that pull all the logs down and some pull non-diagnostic level logs. It seems tedious that I would have to create several DbSet in the DbContext just to achieve this simple filtering functionality.

[ajcvickers]

@reloaded Is there a reason you want to use global query filters for that instead of adding filters as needed locally to the queries that should have the filters?

[reloaded]

@reloaded Is there a reason you want to use global query filters for that instead of adding filters as needed locally to the queries that should have the filters?

Global query filters seem to be the method I'm trying to avoid. I don't want my logging DbSet to always have diagnostics filtered out. My goal is to achieve this class type filtering for only one or two queries.

[ranouf]

Hi, I m looking for the same functionality, a way to filter like you proposed @reloaded :
.Where(e => !(e is DiagnosticLog))

In my situation, I have a list of item to display, the user can filter by one or multiple Type.

[Cubelaster]

Ok, guys, I have found a working solution. Say thanks to these guys:
https://dzone.com/articles/global-query-filters-in-entity-framework-core-20-1
With this, we don't really need any improvements upon Entity.

[tjmcnaboe]

Nice read. I had abandoned hope and went in a different direction on this but the article gave me some new ideas and reminded me how much I liked this as a feature. Thx

[CesarD]

Came here for the same... Leaving sad knowing that after 3 years there's still this lack of functionality.

My case is very simple: I have NominationBase entity from which I derive classes NominationType1 and NominationType2. The first one implements IDelete interface for soft deletion, the second doesn't. So from the EntityTypeConfiguration for NominationType1 I intend to create a global query filter like builder.HasQueryFilter(x => !x.IsDeleted);
So I find myself having to break my model encapsulation and implement IDelete to the base class because global filters can only be applied to root classes from a hierarchy.
Really annoying.

[snebjorn]

I ran into this issue as well, and ended up with one separate query pr entity :(

.Include supports this syntax .Include(x => (x as Derived).SomeNavigationProp) and it's awesome!
Would be nice if .Where(x => (x as Derived).SomeProp == y) was a thing.

Actually when working with derived types in OData it tries to execute the above .Where query, but it fails because EF Core cannot translate it.

[gojanpaolo]

Hello, Is there a workaround for this?
Our use case is pretty simple, we have TPH with something like Employee : EntityBase and Project : EntityBase.. now we have a concept of employee groups where only employees (and related entities) within the same group can be accessed by a given user (employee).

modelBuilder.Entity<Employee>().HasQueryFilter(IncludeEmployeesWithinTheSameGroupAsUser);
modelBuilder.Entity<Project>().HasQueryFilter(IncludeProjectsOfEmployeesWithinTheSameGroupAsUser);

Thank you!

[SIPFernandes]

@ajcvickers @HappyNomad I used the syntax like you suggested as a workaround and it seems to be working ok for me with a relatively small dataset. modelBuilder.Entity<Truck>().HasQueryFilter(c => ((CompanyTruck)c).CompanyId == _resolver.CompanyId); With the caveat that if you want to use the base class you need to turn off the query filter using the ignore query filter syntax or you will get the no coercion operator exception await _context.Trucks.IgnoreQueryFilters().ToListAsync());

When I run add-migration it keeps warning me:

[Qwertyluk]

Hello, Is there a workaround for this? Our use case is pretty simple, we have TPH with something like Employee : EntityBase and Project : EntityBase.. now we have a concept of employee groups where only employees (and related entities) within the same group can be accessed by a given user (employee).

modelBuilder.Entity<Employee>().HasQueryFilter(IncludeEmployeesWithinTheSameGroupAsUser);
modelBuilder.Entity<Project>().HasQueryFilter(IncludeProjectsOfEmployeesWithinTheSameGroupAsUser);

Thank you!

Is there a reliable solution for this use case? I've encountered the same issue.

[zorgoz]

Sad, that no solution is out yet. It is quite a common scenario... :(
The property to be filtered on can well be defined in the derived type. Translating any filter to a where clause is indifferent to TPT or TPH, as it can only restrict the resulting set. With TPH the discriminator is already applied. With TPT the respective column will be present in the respective relation anyway.