OData/Entity Framework: filter child collections

Imagine you want to query a set of entities with OData and/or Entity Framework and do an $expand/Include() on child collections as navigation properties of those entities. But then you want to apply a filter on these child collections, thus returning only child entities that match the given condition. It’s a common problem in OData/EF… and there is no built-in solution for it. Really? Yes! You can only specify the name of the navigation property in $expand or Include(), but you cannot define a filter condition for the children. With pure SQL, it’s no problem by using Join conditions, but with OData and Entity Framework with its entity queries, we normally don’t have pure SQL. Of course it’s possible to filter in C# after the SQL query executed, but you want the condition directly in the SQL query before executing, right? It’s a shame that OData/EF don’t give you a solution for this out-of-the-box…

But fortunately, there are not-so-well-known solutions for it. One that we applied successfully in the current project is the EntityFramework.DynamicFilters library, that also comes as NuGet package. With it, you have the ability to specify common filter conditions for entities. The filter are applied globally on a DbContext in dependency of the entity type. Thus, e.g. if you have different queries to entities of type MyEntity and you have a filter defined for MyEntity, the filter will be applied automagically to all queries for MyEntity.

Take for example the following filter, that is applied in OnModelCreating() of your DbContext (code first EF):

modelBuilder.Filter<MyEntity>("IsNotDeleted", entity => entity.IsDeleted, false);

This defines a filter that is applied everytime when MyEntity is queried from the database. In this case, only such entities will be returned, that are not deleted, which realizes a soft-delete scenario. „applied everytime“ means also queries for child collections as navigation properties of other entities, thus fulfilling our initial requirement.

There is no need that the type of the entity has to be concrete – abstract base classes and interfaces are allowed, too. Thus, you can define an interface like IDeletable and use it for all soft-deletable entities in your model.

The filter condition can also be dynamic, that means you can define dynamic parameters that are evaluated when the filter is applied. For instance, if you only want to return entities of a certain user, you could apply:

modelBuilder.Filter<MyEntity, int>(
    "HasUser",
    (entity, userId) => entity.CreatorId == userId,
    () => GetCurrentUserId());

In this example, GetCurrentUserId() is a dynamic service method for the filter. You can also define more than one parameter for the filter condition, no problem.

One more thing: the filter examples above were all defined in the OnModelCreating() function of the DbContext, so it’s hard to define parameter values that depend on the desired functionality of a certain query. Fortunately, with EntityFramework.DynamicFilters you are able to „override“ filter parameter values on a certain DbContext for a given filter, e.g. "HasUser":

dbContext.SetFilterScopedParameterValue("HasUser", "userId", GetCurrentUserId());

What a nice helper library, isn’t it?