Snæbjørn Snæbjørn - 2 months ago 139
C# Question

Applying Distinct to OData query

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

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.