Missing EF Feature Workarounds: Filters

Filters are one of those ORM features that when you need it, you REALLY need it. NHibernate has had this feature for quite a long time, but it still doesn’t exist in EF. What are filters? In NHibernate, a filter is

a global, named, parameterized filter that may be enabled or disabled for a particular NHibernate session

The scenarios for this feature are pretty extensive:

  • Soft deletes
  • Multi-tenancy (with a tenant ID column)
  • Security
  • Active/inactive records
  • Logical data partitions

Basically, any time you want to apply a predicate to a set of entities when queried, but not force the developer to “remember” to add that clause. The typical approach in EF is with extension methods or base DbContext/Repository classes, but both of these approaches are limited.

Fortunately, EF exposes an extension point to alter the DB query before it goes out the door and gets executed in the form of interceptors. There are two levels of interception:

  • DbCommand
  • DbCommandTree

With the DbCommand, you’re at the SQL level, not where I want to be. Instead, we can work with an expression tree and alter it accordingly. And because this was a bit extensive, I wound up creating an OSS extension for EF to accomplish filters:



I used the NHibernate design for EF, in that you define your filters with the entity metadata, and the parameters of the filter on an instance of your DbContext:

public class FooContext {
    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            .Filter("ActiveListings", c => c.Condition<ListingStatus>(
                listing => listing.Status == ListingStatus.Active));
        // Create parameterized filter for all entities that match type
        // In this case, DB is multi-tenant, with AgencyId as tenant column on _all_ tables
            FilterConvention.Create<IAgencyEntity, int>("Agency", (e, agencyId) => e.AgencyId == agencyId);

// usage, filters disabled by default
    .SetParameter("agencyId", _userContext.CurrentUser.AgencyId);

// can disable filters

There are some limitations and the queries aren’t as powerful as what you can do in NHibernate, but the basic scenarios are there. Because filter parameter values are scoped against an instance of a DbContext, you can effectively partition the filter values based on specific scenarios/contexts. Behind the covers, I translate the LINQ expression passed in via filter configuration to a DbExpression, substitute the contextual parameter values appropriately, and append the result as an additional filter to your query.

For example, different users in a multi-tenant environment will have their tenant filter applied based on their specific tenant:

// In our authentication filter in MVC
var user = dbContext.Users
    .Where(u => u.UserName == form.UserName)

    .SetParameter("tenantId", user.TenantId);

// In our controller, only retrieves orders for this tenant
var orders = dbContext.Orders.ToList();

The GitHub readme has more details, and the code can at least provide an inspiration for those that want to do something a bit more dynamic than what was shown at Tech Ed.

It was an interesting exercise implementing pretty complicated extension to EF, one that was greatly helped by having access to the actual source code, but hindered when nearly all the interesting work happening in EF is marked “internal”. In order to facilitate easy development, I wound up forking EF, replacing all “internal” modifiers with “public”, getting my solution working, then reverting back to the original EF version. Strong-naming may be a pain to OSS development, but highly conservative use of “public” is far more limiting in my experience.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in EntityFramework, NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Chris

    This looks great! Thanks for your effort in providing this much-needed Entity Framework capability. I have a couple of questions:
    1) The NuGet package is only available for .NET 4.5.1. Unfortunately I’m working on a project that is currently stuck on .NET 4.5 due to licensing restrictions. Does your code depend on .NET 4.5.1? If not, could the package be made available for .NET 4.5?
    2) The current implementation involves enabling/disabling filters at the DbContext instance level. Would it be possible to support an extension method which enables a filter just for a query? e.g. dbContext.Orders.Filter(“Tenant”).ToList(). This capability would fit nicely within the Repository pattern I’m currently using.

    • jbogard

      1) Done! 0.2 targets .NET 4.0

      2) This is done by intention – if you want filters at the query level, you’re better off building extension methods for IQueryable. The point of the filters is to enable them at a DbContext scope (which, being your Unit of Work, is only instantiated once per request and shared amongst all repository instances). Filters are scoped per context/session so that every query during that session/context/unit of work can have the filter enabled.

      • Chris

        Re: (2) I was thinking it could help work around the lack of ‘filtered Include()’ in EF (https://entityframework.codeplex.com/workitem/47).
        E.g. I have Parent, Child and Grandchild entities which each implement an interface ITemporalEntity with CreateTimestamp and DeleteTimestamp properties. It would be great to be able to do a query with a filter applied for all temporal entities like:
        dbContext.Parents.Filter(“TemporalFilter”, dateTime).Include(p => p.Children.Select(c => c.Grandchildren)).ToList();
        Your parameterised global filter capability will support such a temporal filter across many entities which is great but it would be nice to be able to have the filter scoped only to a single query, especially where different filters/parameters might be used within a single unit of work. Otherwise, to ensure a filter is only used for a single query I would create a DbContext extension method to support something like:
        List result = null;
        dbContext.WithFilter(“TemporalFilter”, dateTime, ctx => result = ctx.Parents.Include(p => p.Children.Select(c => c.Grandchildren)).ToList());
        The extension method would enable/disable a filter before/after executing an Action.

  • zhuravl

    Cool, but there could be issues. For instance:


    // many lines of code


    It’s is usually expected that this query will return the list of all orders. And other developers, who are not familiar with filters yet just can’t see EnableFilter code existing somewhere.

    They will expect standard behavior, but can get filtered results. What do you think?

    • jbogard

      This is exactly the point of filters – you set them when you’re creating your DbContext. If you want to know what filters are enabled, you go look there. You also don’t turn them off and on willy-nilly – they’re meant to be cross-cutting concerns, *not* a replacement for common queries etc.

      • Darren Hull

        Hi Jimmy,

        As I’m sure you know EF doesn’t support HierarchyId, do you think this functionality could be provided via a filter?

  • Jon Pawley

    Interesting article, especially about being able to intercept EF actions, which I hadn’t been aware of before. We have implemented a similar kind of thing using FilteredDbSet, pretty much the same as http://www.agile-code.com/blog/entity-framework-code-first-applying-global-filters/.

    I like these “set and forget” approaches!

    I also totally agree that marking classes as being “internal” really hampers the “Open/Closed Principle,”… you know, that part of SOLID mantra of agilists…

    • jbogard

      I thought about going the path of extending DbSet, but I really didn’t like the idea of needing to subclass. You only get to inherit from one type, but it seems like a lot of extension points in EF just require inheritance.

  • xaralabos karypidis

    This is a great effort but I face a problem when I try to use filter in a web application to implement a multitenant approach.

    If I am not mistaken once we assign one value to the filter then the value is cased and it doesn’t evaluate again. This is probably related to the fact that the query command trees are cached by the model and are evaluated only once. The documentation for my last sentence is here https://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework/Infrastructure/Interception/IDbCommandTreeInterceptor.cs

    What I missed from the documentation is what exactly means by model. I would expect any time we create a new DbContext class nothing is cached something that is not the case.

    • jbogard

      Filter values are tied to DbContext *instances* and not cached. The definition of a filter is separate to the value tied to a parameter in a filter.

      • xaralabos karypidis

        jbogard Thanks for the reply but probably I miss something. I made a quick example and deploy a site here http://effilters.azurewebsites.net/ The source code of this one is here https://github.com/xabikos/EfFilters
        I tried to show only the categories that the logged on user created. If you want to play with this try to create two different users then create a couple of categories and you will find out that the query is cached and the filter does not apply correctly. If I have done something wrong in the implementation please let me know.

        • jbogard

          You’re right, I’m getting that too. My tests didn’t uncover this, but maybe there’s something else going on there. Can you open a GitHub issue?

          • xaralabos karypidis
          • jbogard


          • coco

            Got the same issue, whereby subsequent calls to `SetParameter` aren’t having any effect (the previous value is still being used in queries) :(

          • Riana

            Hi Jimmy,

            Did you found a solution for the query caching problem ?
            Like you, I implemented an interceptor to apply filters to my sets but I got this very annoying problem of the query caching… :-(



  • Francisco Suárez

    Hi Jimmy,

    I have a model like this:

    The resident entity has a collection of pathologies. The pathology has a ‘visible’ attribute. I need to create a filter so that the access to the pathologies of a resident always be filtered by those whose visible attribute is true.

    When I try to return the pathologies of a resident, an exception occurs in the FilterQueryVisitor.cs class. If the filter is for any resident’s property everything works fine.

    Is this error related to the lack of support of collections that you mention in the post?.

    Thank you vey much.

    • Francisco Suárez

      Hi everyone,

      I found what the problem is.

      The name of the property of the entity that is used to filter, must match the field name in the table of the database to which the entity is mapped.

      We are Spanish. The names of the fields and tables in the database are in Spanish, however, the names of the properties and entities fo the entity framework model, are in English.

      This is causing us the problem, at least with the properties used in the filters.

      I hope this is helpful.

      • Jonny Bekkum


        This is because the FilterInterceptor adds interception in CSpace (Conceptual Space) and not in SSpace (Storage Space). When you get to storage space the mapping to database is already done and the only way to make it work there is to have NO custom mapping.

        I have fixed this is my own fork – to plug in to CSpace and allow custom mapping to be processed correctly. We have a legacy databsae that we cannot modify field names and still want to have better names in the model so we also use custom mapping. I will send a pull request to Jimmy next week with my modifications.

        • coco

          I don’t see your fork on GitHub – can you post a link to your repo?

          • Jonny Bekkum

            I have added PR on GitHub. You may also look at branch #12 in my repo: https://github.com/jonnybee/EntityFramework.Filters/tree/%2312

          • jbogard

            Brilliant, thanks!

          • coco


            I do however get an exception on line 33 in FilterQueryVisitor.cs, as propertyInfo is null.

            This seems to happen whenever `value.GetType()` is a `NavigationPropertyConfiguration` (which doesn’t have an `Annotations` property). `NavigationPropertyConfiguration` seems to be used whenever one side of the association uses `.Map(x => x.MapKey(“MyColumnName”))` – something else to add to your tests?

          • Jonny Bekkum

            Yes, will do that! Thanks.

  • mrbaseball2usa

    Good evening Jimmy,

    Thanks for the great article and source code. We’re looking at using the code with a U o Work wrapping Repository wrapping the DbContext pattern (all using Ninject for the DI container), and are wanting to establish the tenant parameter per at query time (necessary for a web application). I have read over the questions posted here, as well as the ‘bugs’ listed on Github (specifically here https://github.com/jbogard/EntityFramework.Filters/issues/8), and am not seeing a clear way on best practice for such an idea.

    Any help is appreciated!

  • ibrahim

    Hello, This is amazing!
    But in odata $expand property, it does not work, is there any workaround for this?

  • jlddodger

    This is amazing at just what I needed at the right time. While just getting started with it I was frustrated because none of filters worked (even the simplest ones). I almost walked away from it, but I realized at the last minute that I hadn’t registered the interceptor which turned out to be the entire problem. My mistake, entirely, but it might be worth adding the registration options to your sample here (they are already on github, but I worked from the wrong sample).

    Thanks again.

  • anoop xaviour

    Hi Jimmy,

    Thanks a lot! We were searching for the model level filter and none of them worked as we expected. Finally we got the solution in your article and it saved our life. We implemented the same in our project and it is working fine. Now we have one issue, equal to with null variable is not working once we implement the filter.


    int? active = null
    where x.active == active returns no records.

    if we re write the expression as
    where x.active == null returns records.

    This issue was resolved in EF6 and it was working. We are using MVC 4 and EF 6

  • Felipe Queiroz Drumond

    Hi Jimmy. How would you configure entities to use a dynamic database schema depending on the logged tenant? (Consider that every tenant has its own Schema and tables). I couldn’t find a reasonable solution for this, so that I had to implement a IDbCommandInterceptor to replace the sql command sent to the sql server, replacing ‘[dbo]‘ for tenant’s schema. Pretty ugly implementation!!!

  • Ibrahim

    This is genius!