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.

Answer 2

Data model:

public class Customer
{
    public int Id { get; set; }
    public bool IsActive { get; set; }
    public ICollection<Contact> Contacts { get; set; }
}

public class Contact
{
    public int Id { get; set; }
    public bool IsActive { get; set; }
}

Controller with canned data:

public class CustomersController : ODataController
{
    private List<Customer> customers = new List<Customer>
    {
        new Customer { Id = 1, IsActive = false },
        new Customer { Id = 2, IsActive = true,
            Contacts = new List<Contact>
            {
                new Contact { Id = 101, IsActive = true },
                new Contact { Id = 102, IsActive = false },
                new Contact { Id = 103, IsActive = true },
            }
        }
    };

    [EnableQuery]
    public IHttpActionResult Get()
    {
        return Ok(customers.Where(c => c.IsActive).AsQueryable());
    }
}

Note that one Customer is active, and that Customer has 2 (out of 3) active Contacts.

Finally, configure your OData service:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        var builder = new ODataConventionModelBuilder();
        builder.EntitySet<Customer>("customers");

        config.MapODataServiceRoute(
            routeName: "OData",
            routePrefix: null,
            model: builder.GetEdmModel());
    }
}

Now call the service as follows:

GET http://host/customers?$expand=Contacts($filter=IsActive eq true)

You should receive a payload similar to this:

{
  "@odata.context": "http:/host/$metadata#customers",
  "value": [
    {
      "Id": 2,
      "IsActive": true,
      "Contacts": [
        {
          "Id": 101,
          "IsActive": true
        },
        {
          "Id": 103,
          "IsActive": true
        }
      ]
    }
  ]
}

Answer 3

One possible solution is to add Views to represent the data you actually want to expose.

You can have Customer and Contact Views which are just filtered versions of the original table.

Back on the C# side, your models can directly reference the Views as if they were tables.

The nice thing is that they will be treated just as tables, all lazy loading, navigation properties, and database side filtering will still work as if you were referencing the original tables.