Web Api $extend IQueryable with filter

Question

I have a setup where I've get a WebApi OData service which returns: Customers. The code for returning the customers is:

public IHttpActionResult GetCustomers(ODataQueryOptions<Customer> queryOptions)
{
    return Ok(context.Customers.Where(i => i.IsActive).AsQueryable());
}

So the GetCustomers method returns an IQuerable result of all active customers. For history purposes we leave all customers in the database, but when a customer is removed, we set the IsActive field to false.

The OData setup is created using a simple builder.EntitySet to build the Url for the entities.

EntitySetConfiguration<Customer> customers = builder.EntitySet<Customer>("customers");

This works flawlessly. I have an Angular front-end which uses $http calls to receive the customers, etc.

However a customer can contain related contacts in the database. To get the contacts in the Angular Frontend, I use the $extend functionality of OData:

odata/customers?$expand=contacts

This also works great. I receive the customers with all related contacts. However as you've guessed I would like to receive only contacts which have IsActive should be returned. And the IQueryable functionality gives me all results back.

I understand I can use the seperate Odata call to get the contacts, but I really would like to use the $expand features to get all data in one call. I know I can also do the filtering on the client side (with: $filter). But I'd like to setup this correctly in the WebApi part, so the client does not have to care about filtering inactive results back.

I can't seem to figure out how to achieve this correctly. Can somebody help me get on the right track?

Answer 1

EntityFramework.DynamicFilters is one of the greatest tools for Entity Framework that I know. It jumped into the gap of the often-requested but up to EF6 never implemented feature of filtered Incudes. It leans on EF's interception API and does the heavy lifting of modifying expressions while exposing a very simple interface.

In your case, what you can do is something like this:

using EntityFramework.DynamicFilters;

// In OnModelCreating (DbContext)
modelBuilder.Filter("CustomerActive", (Customers c) => c.IsActive);

That's all! Now everywhere where Customers are queried, be it directly, through navigation properties or in Includes, the predicate will be added to the query.

Do you want all customers? You can simply turn the filter off per context instance by doing

context.DisableFilter("CustomerActive");

There's only one glitch (or caveat) I discovered so far. If there are two entities, Parent and Child and there is a filter on Parent that doesn't return any records, then this query ...

context.Children.Include(c => c.Parent)

... doesn't return anything. However, from the shape of the query, I would expect it to return Child entities with empty parents.

This is because in SQL there is an INNER JOIN between Parent and Child and a predicate on Parent that evaluates to false. An OUTER JOIN would give the expected behavior, but of course we can't demand from this library to be that smart.