How to filter an odata $expand query in a Web-Api controller

Question

I have got two models. A Product model:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    public virtual ICollection<Categories> Categories { get; set; }
}

And a Categories model:

public class Categories
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsPublic { get; set; }

    public virtual Product Products { get; set; }
}

As you can see there is a relation between the models. One Product can have multiple categories. I'm able to query the model with http://localhost/Products?$expand=Categories.

However I want to filter the categories before they are returned. Main goal is to avoid that the api consumer can query categories where theIsPublic is set to false. How can I archive this inside the Web API controller?

My first attemp is not working because I need a IQueryable<Product> and it returns a IQueryable<Categories>:

        [HttpGet]
        [EnableQuery]
        public IQueryable<Product> Get(ODataQueryOptions<Product> odataQueryOptions)
        {
            if (odataQueryOptions.SelectExpand.RawExpand == "Categories")
            {
                var p = db.Products.First();
                var result = db.Entry(p).Collection(x => x.Categories).Query().Where(x => x.IsPublic == true).AsQueryable();
                return result;
            }
            else
            {
                return db.Products;
            }
        }

Basically my question is: What is the correct way to write a OData-like $expand query in LINQ? If this is not possible, how else can I filter on en expanded navigation property?

Answer 1

EF doesn't allow to filter included properties. (This was a highly voted feature request for EF but never implemented: Allow filtering for Include extension method)

So, EF doesn't support it. So I can only offer you these two hacks:

• create a filtered view in the database, and map it to a different entity in EF. This is efficient, because the filtering will happen in the server.
• in the controller's code, project the query to a new class, with the related collection filtered (by using Select(x=> new ...)), and make this projected result IQueryable with .AsQueryable extension method. In this way you'll returned a new queryable with related entities filtered as you wanted. This is more inefficient: it requires to recover the whole related collection from the DB server, filter it in the controller's method, and convert it to queryable

Obviously the first option is the "best hack". I think that unfortunately there is not a better solution. Perhaps some other hacks, with TVFs on the server or something like that.