Webapi: In memory filtering after ODataQueryOptions.ApplyTo()

[MikalJ]

I need to perform ekstra in memory filtering of a query, after it has been retrieved from the DB, and before returning it to the client.

Using ODataQueryOptions.ApplyTo(...) I can let OData do all it's query magic on the DB. However if $expand or $select has been supplied, the returned IQueryable contains not the entities themselves, but SelectExpandWrapper<TElement> objects. Which it is impossible to extract the original entity from.

The SelectExpandWrapper<TElement> class has a public property Instance that contains the wrapped entity, but the class itself is internal.

Ideally I would like to be able to do something like this in a controller action

public IQueryable<Entity> GetFeed(ODataQueryOptions<Entity> opts)
{
    IQueryable<Entity> result = opts.ApplyTo(DB.Entities.Query());

    foreach (var item in result)
    {
        // perform extra filtering here
    }

    return result;
}

But even just making SelectExpandWrapper<TElement> public would be a huge help for me.

Note: I do not wish to add or remove any items from the returned IQueryable. But I need to filter some of the properties on each entity, depending on the authorization level of the current user

[congysu]

SelectExpandWrapper is a dictionary, can you use the interface ISelectExpandWrapper? Would like to submit a pull request?

[VikingsFan]

@MikalJ refer to this issue, I think you can modify the result in your own customize attribute.

[andrensairr]

@VikingsFan, I have a similar requirement to @MikalJ in that I need to remove some property values from the response, and was wondering how you envisaged the custom EnableQueryAttribute would help to change the values in the SelectExpandWrapper objects. My ApplyQuery override looks something like the following:

public override IQueryable ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
{
    var queryPath = queryOptions.Context.Path;
    var identity = (ClaimsIdentity)_actionContext.RequestContext.Principal.Identity;
    var userId = identity.GetUserId();
    var results = base.ApplyQuery(queryable, queryOptions);

    // Once the query has been applied, remove all disallowed properties from the results.
    if (!identity.IsInAnyRole(userId, RoleNames.Administrators)) // IsInAnyRole is an extension method implemented to allow checking for multiple roles
    {
        if (queryPath != null && queryPath.NavigationSource != null && queryPath.NavigationSource.Name == "Contacts" && queryable.ElementType == typeof(Contact))
        {
            // It's relatively easy to change these objects, as the original objects are directly contained by the IQueryable
            var contacts = (IQueryable<Contact>)results;

            foreach (var contact in contacts)
            {
                if (!contact.ShowDOBInSearch)
                    contact.DateOfBirth = null;

                if (!contact.ShowSuburbInSearch)
                   contact.PrimaryCity = null;
            }
        }

        if (queryOptions.SelectExpand != null)
        {
            // Get only items in the first level of expansion; this is all that is allowed at present.
            var items = queryOptions.SelectExpand.SelectExpandClause.SelectedItems
                .OfType<ExpandedNavigationSelectItem>()
                .Where(i => i.NavigationSource != null)
                .ToList();

            // Check for any expands to Contact type properties.
            if (items.Any(i => i.NavigationSource.Name == "Contacts"))
            {
                var outerWrappers = (IQueryable<ISelectExpandWrapper>)results;

                foreach (var outerWrapper in outerWrappers)
                {
                    var dict = outerWrapper.ToDictionary();

                    // No way to change properties on the source object, only in the dictionary, which contains only copies.
                }
            }
        }
    }

    return results;
}

As you can hopefully see, being unable to access the internal marked SelectExpandWrapper<TEntity> class somewhat prevents our ability to modify the objects. If I implement a custom DelegatingHandler, the response object still appears to contain only SelectExpandWrappers, with no way to modify the values.

[VikingsFan]

@andrensairr Change the SelectExpandClause can modify the result returns to client, can you try like this:

    public override IQueryable ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
    {
        var result = queryOptions.ApplyTo(queryable);
        var originalRequest = queryOptions.Request;
        if (originalRequest.ODataProperties().SelectExpandClause != null)
        {

            List<SelectItem> selectItems = new List<SelectItem>();
            foreach (var selectItem in originalRequest.ODataProperties().SelectExpandClause.SelectedItems)
            {
                var expandedItem = selectItem as ExpandedNavigationSelectItem;
                if (expandedItem != null && expandedItem.NavigationSource.Name.Equals("Contacts"))
                {
                    continue;
                }
                selectItems.Add(selectItem);
            }
            originalRequest.ODataProperties().SelectExpandClause = new SelectExpandClause(selectItems, false);
        }
        return result;
    }

[andrensairr]

Thanks @VikingsFan, I can see this changes the results returned to the client. I would like to include any expanded Contacts, but only conditionally hide two of the many properties on this object, so I would need to create nested ExpandedNavigationSelectItems specifically for each of those properties I do not want to hide. However, I cannot replace the expandedItem.SelectAndExpand property because it has no setter, and I cannot add new nested properties to expandedItem.SelectAndExpand.SelectedItems because the IEnumerable<T> interface doesn't allow it, and I'm not sure the API for creating a new ExpandedNavigationSelectItem from scratch is available to us; I understand they need to be parsed from the web request URI. I can't readily do something like this: selectItems.Add(new ExpandedNavigationSelectItem("LastName,FirstName,Email").

What I have previously done to filter out disallowed objects (for which the authenticated user does not have authorisation to view) is to customise the expand query option by manually applying a new one. I could probably extend it to select a subset of properties like so:

var baseUri = queryOptions.Request.RequestUri.GetLeftPart(UriPartial.Path);
var uri = string.Format("{0}?$expand=Contacts($select=FirstName,LastName,Email;$filter=ApplicationUserId eq '{1}' or AllowAddingToGroups)", baseUri, userId);
var req = new HttpRequestMessage(HttpMethod.Get, uri);

// First apply the existing queryOptions. This is need to actually perform the expand, or they come back empty.
queryOptions.ApplyTo(queryable);
queryOptions = new ODataQueryOptions(queryOptions.Context, req);
...
return base.ApplyQuery(queryable, queryOptions);

However what I was really hoping for is a way to override the values returned to the client if a value _on that retrieved object_ specifies it, like in the first part of my example.

if (!identity.IsInAnyRole(userId, RoleNames.Administrators)) // IsInAnyRole is an extension method implemented to allow checking for multiple roles
{
    ...
    foreach (var contact in contacts)
    {
        if (!contact.ShowDOBInSearch)
            contact.DateOfBirth = null;

        if (!contact.ShowSuburbInSearch)
            contact.PrimaryCity = null;
    }
    ...
}

As far as I can tell, the only way to change this would be to be able to modify the values in the SelectExpandWrapper<TEntity>, unless you have any other ideas?

[VikingsFan]

@andrensairr about override the value, have you tried to customize the ODataEntityTypeSerializer's CreateEntry method, modify the entry's properties? or would you like to send a PR to change the SelectExpandWrapper ? :)

[andrensairr]

Thanks again @VikingsFan, customising an ODataEntityTypeSerializer looks to be a good solution. It appears to be working fine, and has the added advantage of allowing me to manipulate the entity before any $select operations are applied.

It would have been ideal to keep all my authorisation logic in the one place, but I've already had to put some in the ApplyQuery override anyway, in addition to a custom AuthorizationFilterAttribute, so it's an acceptable compromise. I may yet create a pull request for that change as you suggested if it proves to be a problem for me.

For the benefit of @MikalJ and others that might need to do a similar thing, my solution is summarised as follows:

public class ContactEntityTypeSerializer : ODataEntityTypeSerializer
{
    public ContactEntityTypeSerializer(ODataSerializerProvider serializerProvider)
        : base(serializerProvider)
    { }

    public override ODataEntry CreateEntry(SelectExpandNode selectExpandNode, EntityInstanceContext entityInstanceContext)
    {
        var identity = (ClaimsIdentity)entityInstanceContext.SerializerContext.RequestContext.Principal.Identity;
        var userId = identity.GetUserId();
        var contact = (Contact)entityInstanceContext.EntityInstance;

        if (!identity.IsInAnyRole(userId, RoleNames.Administrators)) // IsInAnyRole is an extension method implemented to allow checking for multiple roles
        {
            if (!contact.ShowDOBInSearch)
                contact.DateOfBirth = null;

            if (!contact.ShowSuburbInSearch)
                contact.PrimaryCity = null;
        }

        return base.CreateEntry(selectExpandNode, entityInstanceContext);
    }
}

public class CustomSerializerProvider : DefaultODataSerializerProvider
{
    private readonly ContactEntityTypeSerializer _contactEntityTypeSerializer;

    public CustomSerializerProvider()
    {
        _contactEntityTypeSerializer = new ContactEntityTypeSerializer(this);
    }

    public override ODataEdmTypeSerializer GetEdmTypeSerializer(IEdmTypeReference edmType)
    {
        if (edmType.IsEntity() && edmType.Definition.FullTypeName() == "Model.Contact")
            return _contactEntityTypeSerializer;

        return base.GetEdmTypeSerializer(edmType);
    }
}

This is registered in the Web Api config using config.Formatters.InsertRange(0, ODataMediaTypeFormatters.Create(new CustomSerializerProvider(), new DefaultODataDeserializerProvider()));

[gorillapower]

@andrensairr thanks for your code above, but it seems this will not alter the result for queries including $select. When i apply the code above for a $select query, the result remains unchanged. For single calls, this works perfectly.

I think its got to do with the entityInstanceContext.edmObject being wrapped in the SelectSome wrapper, and thus the properties cant be changed.

[gorillapower]

@andrensairr, @MikalJ I managed to find a solution without overriding the Serializer. Using the code in your initial question,

public IQueryable<Entity> GetFeed(ODataQueryOptions<Entity> opts)
{
    IQueryable<Entity> result = opts.ApplyTo(DB.Entities.Query());
    foreach (var item in result)
    {
        // perform extra filtering here
    }
    return result;
}

You can do this to check for a $select wrapper, get a Dictionary of key,values and then map it your model.

The result is

public IQueryable<Entity> GetFeed(ODataQueryOptions<Entity> opts)
{
    IQueryable<Entity> result = opts.ApplyTo(DB.Entities.Query());


    var resultList = new List<Entity>();
    foreach (var item in result)
    {
        if (item is Entity) 
        {
            var model = item as Entity;
            //do something
            resultList.Add((Entity)item);
        }
        else if (item.GetType().Name == "SelectAllAndExpand`1") //$expand
        {
            var entityProperty = item.GetType().GetProperty("Instance");
            var model = (Entity)entityProperty.GetValue(item);
            //do something
            resultList.Add((Entity)entityProperty.GetValue(item));
        }
        else if (item.GetType().Name == "SelectSome`1") //$select
        {
            var dict = ((ISelectExpandWrapper)item).ToDictionary();
            var model = dict.ToObject<Entity>();
            //do something
            resultList.Add(model);
        }
    }

    return result;
}

All credit must go to http://www.jauernig-it.de/intercepting-and-post-processing-odata-queries-on-the-server/#comment-617 and this answer here http://stackoverflow.com/a/30552434/592449 and http://stackoverflow.com/a/4944547

[MikalJ]

I appreciate the suggestion and all the help on this issue. However, using reflection to access a non-public member is not really a valid solution in a serious application.

We chose to abandoned OData quite a while ago, partly because of limitations in this library, and partly because of issues with the OData spec itself.

[gorillapower]

@MikalJ well noted your response, curios did you end up using an alternative solution or just abandoning OData altogether?

[MikalJ]

Abandoned OData entirely. Even if we'd found no limitations in this library, the OData spec itself is problematic for our scenario, and would have forced us to go another route in any case.

[MikalJ]

By the way. We did solve this issue before moving away from OData. But the solution was overly complex, and hard to maintain. The basic steps was as follows:

• Remove [EnableQuery] attribute from all OData endpoints.
• Instantiate an ODataQueryOptions object manually, remembering to do some of the work on it that the EnableQueryAttribute previously took care of.
• Manually parse the $expand queryoption, and translate it into a series of .Include(...) calls on the EF DBSet.
• Use the ODataQueryOptions object to apply all query options on the IQueryable except Expand and Select (this lets EF translate the filter, skip and take to SQL and apply those directly in the DB)
• Perform the needed filtering in memory, on the result.
• Use the ODataQueryOptions object to apply Select and Expand on the filtered result.

By the time the response reaches the ODataMediaTypeFormatter, its' content has been filtered and massaged to contain only what I want it to. And it can safely be serialized to json.

Most of this work was packaged away in a custom ODataController base class, a new HttpReponseMessage implementation, and some HttpRequestMessage extension methods. In total it was probably around a thousand lines of code.
A basic endpoint action ended up looking like this.

[ODataRoute]
public FilteredODataFeedResponseMessage<Role> GetFeed()
{
    // Fail the request (by throwing an exception) before hitting DB if there's invalid query options
    ValidateQueryOptions<Role>(); 
    // parse the $expand queryoption into a generic "join" tree
    var joins = GetJoins<Role>();
    // Get an IQueryable<Role> from our EF repository implementation. It's actually a DBQuery<Role> with the joins applied as .Include() calls
    var query = roleRepository.Query(joins);
    // construct, and return, a FilteredODataFeedResponseMessage, that has a filtering object injected, based on the type of T
    return Request.ODataFeedResponse(query);
}

As I said, it's an overly complex solution, but the only one we can find that didn't require us to modify the source of this library, or depend on reflection.