Applying Distinct to OData query

Question

I want to get a list of distinct values from my OData endpoint. But distinct or group by isn't supported yet.

My URI query looks something like this

GET /odata/Products?$select=foo & $top=10 & $count=true & distinct=true

My Controller

[EnableQuery]
public IQueryable<FooBarBaz> Get(ODataQueryOptions<FooBarBaz> queryOptions, bool distinct)
{
        //I've tried the following
        return Repository.AsQueryable().Distinct();

        // and
        return Repository.AsQueryable().GroupBy(x => x.Foo);

        // and
        IQueryable query = queryOptions.ApplyTo(Repository.AsQueryable());
        return query.Distinct(); // Can't call .Distinct() here
}

None work :(

Answer 1

Because you have specified the EnableQuery attribute, you can use $apply to groupby your distinct fields, without having to add any custom functions or parameters, you get this for free out of the box:

GET /odata/Products?$apply=groupby((foo))&top=10&$count=true

This is simple OData v4 standard syntax that doesn't require any code modification to implement. Don't go changing each controller that you want to support a distinct query on, you can't know 100% in advance which controllers your client apps might want this functionality on, so use the functionality that is provided before you start customisations.

Of course there is a caveat to this approach that do not make it viable 100% of the time:

• $filter and $orderby can only operate on the fields specified in your group by clause

This may require you to include additional fields into your grouping statement and for some complex filtering the resultant dataset may not be satisfactory, in cases such as these we found it easier to support passing in an additional pre-filter parameter through HTTP header that can be applied to the query before the passed in query options are applied, note that this was only necessary because our filter conditions were tenancy and security related and so the resultant data set had many more duplicate entries if you ignored the security descriptors.

Just for fun, here is our custom GET function that applies the pre-filter if it is passed in:

[EnableQuery]
public IQueryable<FooBarBaz> Get(ODataQueryOptions<FooBarBaz> queryOptions, bool distinct)
{
    DbQuery<FooBarBaz> query = Repository;
    query = this.ApplyUserPolicy(query);
    return Ok(query);
}

The following is implemented in a base class so that we don't have it in each controller:

/// <summary>
/// Apply default user policy to the DBQuery that will be used by actions on this controller.
/// The big one we support here is X-Filter HTTP headers, so now you can provide top level filtering in the header of the request 
/// before the normal OData filter and query parameters are applied.
/// This is useful when you want to use $apply and $filter together but on separate sets of conditions.
/// </summary>
/// <param name="dataTable">DBQuery to apply the policy to</param>
/// <returns>Returns IQueryable entity query ready for processing with the headers applied (if any)</returns>
private IQueryable<TEntity> ApplyUserPolicy(DbQuery<TEntity> dataTable)
{
    // Proprietary Implementation of Security Tokens
    //var tokenData = SystemController.CurrentToken(Request);
    //IQueryable<TEntity> query = ApplyUserPolicy(dataTable, tokenData);
    IQueryable<TEntity> query = dataTable.AsQueryable();

    // Now try and apply an OData filter passed in as a header.
    // This means we are applying a global filter BEFORE the normal OData query params
    // ... we can filter before $apply and group by

    System.Collections.Generic.IEnumerable<string> filters = null;
    if (Request.Headers.TryGetValues("X-Filter", out filters))
    {
        foreach (var filter in filters)
        {
            //var expressions = filter.Split(',');
            //foreach (var expression in expressions)
            {
                var expression = filter;
                Dictionary<string, string> options = new Dictionary<string, string>()
                {
                    { "$filter"  , expression },
                };

                var model = this.Request.ODataProperties().Model;
                IEdmNavigationSource source = model.FindDeclaredEntitySet(this.GetEntitySetName());
                var type = source.EntityType();
                Microsoft.OData.Core.UriParser.ODataQueryOptionParser parser
                    = new Microsoft.OData.Core.UriParser.ODataQueryOptionParser(model, type, source, options);
                var filterClause = parser.ParseFilter();     // parse $filter 

                FilterQueryOption option = new FilterQueryOption(expression, new ODataQueryContext(model, typeof(TEntity), this.Request.ODataProperties().Path), parser);
                query = (IQueryable<TEntity>)option.ApplyTo(query, new ODataQuerySettings());
            }
        }
    }


    return query;
}

If nothing else, it's cheaper that trying to sell AdaptiveLINQ to your manager :)