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

The best solution to solve the problem by defining an collection Action on the resource.

First Step : configure the 'Distinct' action in WebApiConfig.cs

ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<FooBarBaz>("FooBarBazs");//Resource Name

ActionConfiguration Distinct = builder.Entity<FooBarBaz>().Collection.Action("Distinct");//Name of the action method
Distinct.ReturnsCollectionFromEntitySet<FooBarBaz>("FooBarBazs");//Return type of action
Distinct.Parameter<string>("On");//Property on which collection is filtered as Distinct

config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());

Second Step : Add the Action in FooBarBazsController.cs which returns the collection of distinct entities

[EnableQuery]//enable the $select,$expend Queries
[HttpPost]//All the action methods are of post type in Web api
public IQueryable<FooBarBaz> Distinct(ODataActionParameters parameters)
{
        string on = "";
        if (!ModelState.IsValid)
        {
            throw new HttpResponseException(HttpStatusCode.BadRequest);
        }

        try
        {
             on = parameters["On"] as string;
        }
        catch (NullReferenceException ex)
        {
            HttpResponseMessage message = new HttpResponseMessage(HttpStatusCode.BadRequest);
            message.Content = new StringContent("{\"Error\":\"Invalid Query -> On property is not defined\"}");
            throw new HttpResponseException(message);
        }
        catch (Exception ex)
        {
            throw new HttpResponseException(HttpStatusCode.BadRequest);
        }


        PropertyInfo[] props = new FooBarBaz().GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
        var isPropertyExist = false;
        for (int i = 0; i < props.Length; i++)
        {
            if (props[i].Name.Equals(on))
            {
                isPropertyExist = true;
                break;
            }
        }


        if (isPropertyExist)
        {
            var fooBarBazCollection = db.fooBarBazs.GroupBy(GetGroupKey(on)).Select(g => g.FirstOrDefault());//Select the Distinct Entity on the basis of a property
            return fooBarBazCollection ;
        }
        else
        {
            HttpResponseMessage message = new HttpResponseMessage(HttpStatusCode.BadRequest);
            message.Content = new StringContent("{\"Error\":\"Property '"+on+"' Not Exist}");
            throw new HttpResponseException(message);
        }
}

Third Step : Add a static method which returns an Expression for groupby on the basis of Property Name.

private static Expression<Func<fooBarBaz, string>> GetGroupKey(string property)
    {
        var parameter = Expression.Parameter(typeof(fooBarBaz));
        var body = Expression.Property(parameter, property);
        return Expression.Lambda<Func<fooBarBaz, string>>(body, parameter);
    } 

Now Build the project and You can query the Resource like this

POST /odata/FooBarBazs/Distinct HTTP/1.1
Host: localhost:9360
Content-Type: application/json
Cache-Control: no-cache
Postman-Token: 6d174086-7b97-76a2-679c-4dab3dfb5938

{"On":"PropertyName"} 

And can also use the $select and $expend like this

POST /odata/FooBarBazs/Distinct?$select=PropertyName1,PropertyName2 HTTP/1.1
Host: localhost:9360
Content-Type: application/json
Cache-Control: no-cache
Postman-Token: 6d174086-7b97-76a2-679c-4dab3dfb5938

{"On":"PropertyName"} 

I hope this solve the problem. +1 if it do.

Answer 2

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 :)