In almost every application, there are some tables which contains inactive or soft deleted data. This kind of data should not be shown to the client or used anymore every time we query these tables, we must remove them with a WHERE clause. Major ORM like NHibernate have a filter feature to select records based on a predefined filter but, unfortunately for Entity Framework users, Query Filter is only available through third party library.
EF+ Query Filter lets you change the predefined query from the context generated by Entity Framework for your own Query.
- You can filter the query with a predicate to exclude soft deleted records:
- You can control the default query to add a default sorting:
- You can use a predefined filter and enable it only for a specific query:
EF+ Query Filter Global
Global filter can be used by any context.
Global filter is normally preferred in most scenario over instance filter since the filter code is centralized in one method over being spread in multiple methods.
Use entities context constructor to initialize global filter by default.
EF+ Query Filter By Instance
Instance filter applies filters to the current context only. The filtering logic is added once the context is created.
Use entities context constructor to make some filter “global” to all context.
EF+ Query Filter By Query
Query filter applies filters to specific queries only. The filtering logic is added globally or by instance but in a disabled state and then it is enabled by these specific queries.
EF+ Query Filter By Inheritance/Interface
Filter can be enabled and disabled by class inheritance and interface.
EF+ Query Filter Enable/Disable
Filters are very flexible, you can enable and disable them at any time and only for a specific inheritance or interface if desired.
EF+ Query Filter AsNoFilter
You can bypass all filters by using AsNoFilter method in a query if a special scenario doesn’t require filtering.
Real Life Scenarios
Logical Data Partitioning
A common scenario is to retrieve products by category or the ones available only for a specific country. All data are stored in the same table but only a specific range should be available.
In this example, we retrieve only the products available for the selected category.
Single category by product
Many categories by product
An example of multi-tenancy is an online store for which the same instance of the database is used by multiple independent applications or clients and the data should not be shared between them.
Learn more about Multi-tenancy
In this example, the application is a tenant. The customer can only see invoice from the current application.
Removing inactive or soft deleted records is probably the most common scenario. A soft delete is often useful when related data cannot be deleted. By example, the customer cannot be deleted because related orders cannot be deleted instead, he becomes inactive.
In this example, we display only active category.
Viewing sensible data requires often some permissions. By example, not everyone can see all posts in a forum.
In this example, some posts are only available by role level.
Default ordering can be often useful for base table like category. No matter the query, you probably want to show categories by alphabetic order.
In this example, categories are sorted by name
Including related entities can be useful. By example, every time you load a post, you want to include all active comments related.
Require: EF+ Query Include
In this example, All active comments are included for every post.
- Entity Framework 5
- Doesn’t work with LazyLoading
- Doesn’t work with Include Entity Framework Core:
- Doesn’t work with LazyLoading
- Doesn’t work with Include
- DO NOT support filter by inheritance/interface (Will be supported when EntityFramework team will fix this issue)
Entity Framework 6 - Limitations
To some backward compatibility, filtering works already on collection but are not enabled on a property by default.
You must enable the option AllowPropertyFilter
Since the QueryCacheManager is global, our library have some limitation with Filter by context (Global Filter doesn’t have this issue since it apply the same logic to all query.
- LazyLoading only work with GlobalFilter
- context.Set only work with GlobalFilter (You can use the method SetFiltered instead)
For this kind of scenario, we recommend using instead: EntityFramework.DynamicFilters.
Entity Framework Core - Limitations
A ForceCast option has been added to support temporary inheritance, but some LINQ method will not be longer work in combination with ForceCast
Here is a list of known method that doesn’t longer work with query filtered with the ForceCast options enabled:
- EF+ Query Filter: Full version or Standalone version
- Database Provider: All supported
- Entity Framework Version: EF5, EF6, EF Core
- Minimum Framework Version: .NET Framework 4
EF+ Query Filter is very powerful and very easy to use. Our filter version covers all kinds of requirements an application could have.
Need help getting started? firstname.lastname@example.org
We welcome all comments, ideas and suggestions to improve our library.