将Distinct应用于OData查询 [英] Applying Distinct to OData query

问题描述

我想从我的OData端点获取一个不同值的列表.但是尚不支持与众不同或分组依据.

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

我的URI查询看起来像这样

My URI query looks something like this

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

我的控制器

[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
}

没有工作:(

推荐答案

通过在资源上定义集合Action来解决问题的最佳解决方案.

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

第一步:在WebApiConfig.cs中配置"Distinct"操作

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());

第二步:在FooBarBazsController.cs中添加操作，该操作将返回不同实体的集合

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);
        }
}

第三步::添加一个静态方法，该方法将基于属性名称"为groupby返回一个表达式.

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"} 

还可以像这样使用$ select和$ expend

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"} 

我希望这可以解决问题.如果可以，则+1.

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

这篇关于将Distinct应用于OData查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！