Since Entity Framework Core 2 we can use Global Query Filters to add where clauses to any queries EF generates for particular entities. This means we can globally filter out data such as soft deleted records or other records which shouldn’t be shown to the current user in a single location rather than having to remember to do it on all queries throughout our apps.
If you’re using the repository pattern you might already have a central location to filter out certain records but if not Global Query Filters can be very useful. They are also very easy to use and are set in the OnModelCreating method on your DbContext class.
SIDE NOTE – Zero issues here if your using Entity Framework Core Code First workflow as the code is the driver but if your using a DB first approach your context and thus your OnModelCreating method with all your filters will be overridden every time you change your DB. In this instance you need to use a partial class deriving from your scaffolded DbContext to store all your customizations. It is this new partial class which you will instantiate in your app.
Applying global query filters entity by entity
In the most basic sense you can just statically add query filters for each relevant entity as shown below.
The above approach works fine for a few entities but what if you have hundreds of entities which all have some kind of soft delete flag or other discriminator column which you want to filter on? That would be a lot of effort and perhaps you’ll forget to add the filters for new entities etc.
Ideally we’d be able to apply filters to groups of related entities which all implement a particular interface or all have a particular column. Fortunately this is easily done.
Applying query filters to entities which implement a particular interface
Below we have an extension method which applies the expression passed in as a global query filter to entities which implement a particular interface. In this case we are applying a filter to entities which implement the IDeleted interface.
Applying global query filters to entities with a particular column name
This approach isn’t as clean and delineated as the interface approach above but if you wouldn’t be using the interfaces for anything other than setting query filters in EF you can just set a filter on entities which have a particular property name and type combination.
The extension method below searches all entities for the passed in property name and builds an expression to set its value. To use it just pass in the property name as a string and the value you want to filter on. In this case C# can infer the generic type is bool, but I’ve included it in the call below for clarity.
Extension Method Code for setting filter queries in Entity Framework Core based on interfaces or property names…
Here’s the code for the extension methods shown above. Note these work as of Entity Framework Core 3.1.3, I think there might have been some breaking changes between EF 2 and EF 3 so if your having issues try upgrading if possible.
2 thoughts on “Setting Global Query Filters on multiple entities dynamically in Entity Framework Core”
Thank you very much for this. I tried embedding a global query filter for a TenantId inside an IEntityTypeConfiguration implementation. I had a way of resolving the Tenant at runtime and setting a public property on the DbContext class but that meant the query filter expression (inside the Configuration class) didn’t have access to the runtime tenant ID.
I really didn’t want to have to create a bunch of separate query filters for each model item and this post was exactly what I was looking for. Worked perfectly! Thanks a million!
how to update tenantId in the global filter? Once one tenantID is set it does not update the tenantId