带$ count的OData v4 groupby [英] OData v4 groupby with $count

问题描述

我正在将带有WebAPI的OData v4与这些nuget软件包一起使用:

I'm using OData v4 with WebAPI with these nuget-packages:

Microsoft.Data.OData 5.7.0

Microsoft.AspNet.WebApi.OData 4.0.30506

我无法获得 groupby 来处理count.这应该是最简单的事情.

I can't get groupby to work with count. It should be the most simple thing.

我有一个名为 Delivery 的简单表/实体.它有一个状态列(在其他几个列中).我基本上想做的就是这个SQL查询，但是要使用OData:

I have a simple table/entity called Delivery. It has a status column (among several other ones). What I basically want to do is this SQL query, but with OData:

SELECT e.status, count(*) AS total
FROM Delivery e
GROUP BY e.status

我尝试使用

I've tried using countdistinct, but it doesn't give me the correct results.

/odata/deliveries?$apply=groupby((status),aggregate(status with countdistinct as total))

它返回:

"value": [
{
    "@odata.id": null,
    "total": 1,
    "status": 1
},
{
    "@odata.id": null,
    "status": 2,
    "total": 1
},
{
    "@odata.id": null,
    "status": 4,
    "total": 1
}
]

正确的结果应该是(这是我的SQL查询返回的结果):

The correct results should be (which is what my SQL-query returns):

status  total
1       2
2       22
4       1

我还阅读了有关虚拟属性

I've also read about the virtual property $count, but it seems as Microsoft doesn't support it yet.

如何在OData v4中将分组依据和简单的 count 一起使用?

How do I use group by together with a simple count with OData v4?

推荐答案

虽然不支持$ count，但一段时间以来一直支持contdistinct，对于OData聚合方案，它在语法上可能更正确，但该文档尚欠缺一点，但countdistinct使调用者可以控制在结果集中使用哪个字段来确定唯一结果.这在复杂的嵌套或扩展分组查询中特别有用.

Whilst $count is not supported, contdistinct has been supported for a while and is possibly more syntactically correct for OData aggregate scenarios, the documentation is a little bit lacking but countdistinct gives the caller control over which field to use in the result set to determine the unique results. This is especially useful in complex nested or expanded grouping queries.

事实证明，您的查询中存在一个简单的错误！

So it turns out you have a simple bug in your query!

在您的查询中:/odata/deliveries?$ apply = groupby((status)，aggregate(以countdistinct为总计的状态))

您要统计所有状态的不同值.由于您是按状态分组的，因此每个组中的不同值将始终为1:)

You are asking for count all the distinct values of status. Because you are grouping by status, the distinct values in each group will always be 1 :)

您的实际查询应该是对一个属性进行区分，该属性对于集合中的每个项目都是唯一的，通常是一个ID.假设交付"具有一个名为"Id"的ID字段，则您的查询应为:

Your actual query should be to countdistinct over a property that will be unique for each item in the set, usually an Id. Assuming Delivery has an Id field called "Id", your query should be this:

/odata/deliveries?$apply=groupby((status),aggregate(id with countdistinct as total))

这将与您期望的SQL紧密相关:

this will translate closely to your expected SQL:

SELECT status, COUNT(DISTINCT id) AS total
FROM Delivery
GROUP BY status

这篇关于带$ count的OData v4 groupby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！