WebAPI OData pre filtering expand queries

Question

I want to know if it's possible to pre-filter OData results in a WebAPI for items in the expand clause. I only want this to filter based on a predefined interface with a Deleted flag.

public interface IDbDeletedDateTime
{
    DateTime? DeletedDateTime { get; set; }
}

public static class IDbDeletedDateTimeExtensions
{
    public static IQueryable<T> FilterDeleted<T>(this IQueryable<T> self) 
        where T : IDbDeletedDateTime
    {
        return self.Where(s => s.DeletedDateTime == null);
    }
}

public class Person : IDbDeletedDateTime
{
     [Key]
     public int PersonId { get; set }
     public DateTime? DeletedDateTime { get; set; }
     public virtual ICollection<Pet> Pets { get; set; }
}

public class Pet : IDbDeletedDateTime
{
     [Key]
     public int PetId { get; set }
     public int PersonId { get; set }
     public DateTime? DeletedDateTime { get; set; }
}


public class PersonController : ApiController
{
    private PersonEntities db = new PersonEntities();

    [EnableQuery]
    // GET: api/Persons
    public IQueryable<Person> GetPersons()
    {
        return db.Persons.FilterDeleted();
    }
}

You can see that I'm very easily filtering deleted people. The problem comes when someone gets deleted Pets from a query like /api/Persons?$expand=Pets

Is there a way to check if this expansion of "Pets" is an IDbDeletedDateTime and filter them accordingly? Maybe there is a better way to approach this?

EDIT:

I tried to solve this based on what was picked up in this answer. I don't think it can be done, at least not in all scenarios. The only part of a ExpandedNavigationSelectItem that even looks like it is related to the filters is the FilterClause. This can be null when it has no filter, and it is only a getter property, meaning we can't set it with a new filter if we wanted to. Weather or not it is possible to modify a current filter is only covering a small use case that I'm not particularly interested in if I can't add a filter freshly.

I have an extension method that will recurse through all the expand clauses and you can at least see what the FilterOption is for each expansion. If anyone can get this 90% code fully realized, that would be amazing, but I'm not holding my breath on it.

public static void FilterDeletables(this ODataQueryOptions queryOptions)
{
    //Define a recursive function here.
    //I chose to do it this way as I didn't want a utility method for this functionality. Break it out at your discretion.
    Action<SelectExpandClause> filterDeletablesRecursive = null;
    filterDeletablesRecursive = (selectExpandClause) =>
    {
        //No clause? Skip.
        if (selectExpandClause == null)
        {
            return;
        }

        foreach (var selectedItem in selectExpandClause.SelectedItems)
        {
            //We're only looking for the expanded navigation items. 
            var expandItem = (selectedItem as ExpandedNavigationSelectItem);
            if (expandItem != null)
            {
                //https://msdn.microsoft.com/en-us/library/microsoft.data.odata.query.semanticast.expandednavigationselectitem.pathtonavigationproperty(v=vs.113).aspx
                //The documentation states: "Gets the Path for this expand level. This path includes zero or more type segments followed by exactly one Navigation Property."
                //Assuming the documentation is correct, we can assume there will always be one NavigationPropertySegment at the end that we can use. 
                var edmType = expandItem.PathToNavigationProperty.OfType<NavigationPropertySegment>().Last().EdmType;
                string stringType = null;

                IEdmCollectionType edmCollectionType = edmType as IEdmCollectionType;
                if (edmCollectionType != null)
                {
                    stringType = edmCollectionType.ElementType.Definition.FullTypeName();
                }
                else
                {
                    IEdmEntityType edmEntityType = edmType as IEdmEntityType;
                    if (edmEntityType != null)
                    {
                        stringType = edmEntityType.FullTypeName();
                    }
                }

                if (!String.IsNullOrEmpty(stringType))
                {
                    Type actualType = typeof(PetStoreEntities).Assembly.GetType(stringType);
                    if (actualType != null && typeof (IDbDeletable).IsAssignableFrom(actualType))
                    {
                        var filter = expandItem.FilterOption;
                        //expandItem.FilterOption = new FilterClause(new BinaryOperatorNode(BinaryOperatorKind.Equal, new , ));
                    }
                }

                filterDeletablesRecursive(expandItem.SelectAndExpand);
            }
        }
    };

    filterDeletablesRecursive(queryOptions.SelectExpand?.SelectExpandClause);
}

Answer 1

Correct me if I understood wrong: you want to always filter the entities if they implement the interface IDbDeletedDateTime, so when the user wants to expand a navigation property you also want to filter if that navigation property implements the interface, right?

In your current code you enabled OData query options, with the [EnableQuery] attribute, so OData will handle the expand query option for you, and the Pets will not be filtered the way you want.

You have the option of implementing your own [MyEnableQuery] attribute, and override the ApplyQuery method: check there if the user has set the $expand query option and if so, check if the requested entity implements IDbDeletedDateTime and filter accordingly.

You can check here the code of the [EnableQuery] attribute and see that in the ApplyQuery method you have access to the object ODataQueryOptions that will contain all the query options set by the user (WebApi populates this object from the URI query string).

This would be a generic solution that you could use in all your controller methods if you are going to have several entities with that interface with your custom filtering. If you only want this for a single controller method, you can also remove the [EnableQuery] attribute, and invoke the query options directly in the controller method: add the ODataQueryOptions parameter to your method and handle the query options manually.

That would be something like:

// GET: api/Persons
public IQueryable<Person> GetPersons(ODataQueryOptions queryOptions)
{
    // Inspect queryOptions and apply the query options as you want
    // ...
    return db.Persons.FilterDeleted();
}

See the section Invoking Query Options directly to understand more how to play around with that object. If you read the entire article, be aware that the [Queryable] attribute is your [EnableQuery] attribute, since the article is from a lower version of OData.

Hope it points you in the right direction to achieve what you want ;).

---

EDIT: some information regarding nested filtering in $expand clause:

OData V4 supports filtering in expanded content. This means you can nest a filer inside an expand clause, something like: GET api/user()?$expand=followers($top=2;$select=gender). In this scenario, again you have the option to let OData handle it, or handle it yourself exploring the ODataQueryOptions parameter: Inside your controller you can check expand options and if they have nested filters with this code:

if (queryOptions.SelectExpand != null) {
    foreach (SelectItem item in queryOptions.SelectExpand.SelectExpandClause.SelectedItems) {
        if (item.GetType() == typeof(ExpandedNavigationSelectItem)) {
            ExpandedNavigationSelectItem navigationProperty =  (ExpandedNavigationSelectItem)item;

            // Get the name of the property expanded (this way you can control which navigation property you are about to expand)
            var propertyName = (navigationProperty.PathToNavigationProperty.FirstSegment as NavigationPropertySegment).NavigationProperty.Name.ToLowerInvariant();

            // Get skip and top nested filters:
            var skip = navigationProperty.SkipOption;
            var top = navigationProperty.TopOption;

            /* Here you should retrieve from your DB the entities that you
               will return as a result of the requested expand clause with nested filters
               ... */
            }
        }
    }

Answer 2

Zachary, I had a similar requirement and I was able to solve it by writing an algorithm that adds additional filtering to the request ODataUri based on the properties of my model. It examines any properties at the root level entity and the properties of any expanded entities as well to determine what additional filter expressions to add to the OData query.

OData v4 supports filtering in $expand clauses but the filterOption in the expanded entities is read only so you cannot modify the filter expressions for the expanded entities. You can only examine the filterOption contents at the expanded entities.

My solution was to examine all entities (root and expanded) for their properties and then add any additional $filter options I needed at the root filter of the request ODataUri.

Here is an example OData request Url:

/RootEntity?$expand=OtherEntity($expand=SomeOtherEntity)

This is the same OData request Url after I had updated it:

/RootEntity?$filter=OtherEntity/SomeOtherEntity/Id eq 3&$expand=OtherEntity($expand=SomeOtherEntity)

Steps to accomplish this:

1. Use ODataUriParser to parse the incoming Url into a Uri object

See below:

var parser = new ODataUriParser(model, new Uri(serviceRootPath), requestUri);   
var odataUri = parser.ParseUri();

2. Create a method that will traverse down from the root to all expanded entities and pass the ODataUri by ref (so that you can update it as needed as you examine each entity)

The first method will examine the root entity and add any additional filters based on the properties of the root entity.

AddCustomFilters(ref ODataUri odataUri);

The AddCustomFilters method will the traverse the expanded entities and call the AddCustomFiltersToExpandedEntity which will continue to traverse down all expanded entities to add any necessary filters.

foreach (var item in odatauri.SelectAndExpand.SelectedItems)
{
    AddCustomFiltersToExpandedEntity(ref ODataUri odataUri, ExpandedNavigationSelectItem expandedNavigationSelectItem, string parentNavigationNameProperty)
}

The method AddCustomFiltersToExpandedEntity should call itself as it loops over the expanded entities at each level.

3. To update the root filter as you examine each entity

Create a new filter clause with your additional filter requirements and overwrite the existing filter clause at the root level. The $filter at the root level of the ODataUri has a setter so it can be overriden.

odataUri.Filter = new FilterClause(newFilterExpression, newFilterRange);

Note: I suggest creating a new filter clause using a BinaryOperatorKind.And so that your additional filter expressions are simply appended to any existing filter expressions already in the ODataUri

var combinedFilterExpression = new BinaryOperatorNode(BinaryOperatorKind.And, odataUri.Filter.Expression, newFilterExpression);
odataUri.Filter = new FilterClause(combinedFilterExpression, newFilterRange);

4. Use ODataUriBuilder to create a new Url based on the updated Uri

See below:

var updatedODataUri = new Microsoft.OData.Core.UriBuilder.ODataUriBuilder(ODataUrlConventions.Default, odataUri).BuildUri();

5. Replace the request Uri with the updated Uri.

This allows the OData controller to complete processing the request using the updated OData Url which includes the additional filter options you just added to the root level filer.

ActionContext.Request.RequestUri = updatedODataUri;

This should provide you with the capability to add any filtering options you need and be 100% sure that you have not altered the OData Url structure incorrectly.

I hope this helps you achieve your goals.

Answer 3

I had a similar problem and I managed to solve it using Entity Framework Dynamic Filters.

In your case, you would create a filter that filters out all deleted records, like that:

Your DbContext OnModelCreating method

modelBuilder.Filter("NotDeleted", (Pet p) => p.Deleted, false);

This filter will be then applied every time you query your Pets collection, either directly or trough OData's $expand. You have of course full control over the filter, and you can disable it manually or conditionally - it is covered in the Dynamic Filters documentation.

Answer 4

I asked the OData team about this issue, and I may have an answer that can be used. I haven't been able to test it out fully and get it used, but it looks like it will solve my problems when I am able to get around to them. I want to post this answer just in case this will help someone else.

That being said... It looks there is a framework on top of OData that seems to be in its relative infancy called RESTier being developed by Microsoft. It seems to offer a layer of abstraction on top of OData that allows for these kinds of filters, as the examples would suggest.

This looks like it would be an example above with a filter in the Domain object that would be added:

private IQueryable<Pet> OnFilterPets(IQueryable<Pet> pets)
{
    return pets.Where(c => c.DeletedDateTime == null);
}

If I get around to implementing this logic, I'll return to this answer to confirm or deny the use of this framework.

I was never able to implement this solution to know if it's worthwhile. There were too many challenges to justify the worth in my particular use case. It may very well be a great solution for new projects or folks the really need these features, but my particular use case was challenging to implement the framework into existing logic.

Your mileage may vary, and this may still be a useful framework to check out.