通过查询和位字段进行分组 [英] GroupBy query and bit fields

问题描述

我正在使用实体框架核心2.1，我具有一个数据库访问环境，该访问环境具有一个模型访问器，该模型包含一个布尔字段，该字段表示为MS SQL数据库中的不可为空的位字段。我想构造一个查询，该查询可以在SQL中进行高效评估，从而为我提供表中所有行以及启用了bit列的行的计数。

I am using entity framework core 2.1, I have a database context with an accessor for a model containing a boolean field represented as a non nullable bit field in an MS SQL database. I want to construct a query that evaluates in SQL efficiently that provides me a count of all rows in the table, and those with the bit column enabled.

var groups = await this.context.Models
    .AsNoTracking()
    .GroupBy(i => 1)
    .Select(g => new ViewModel
    {
        Count = g.Count(),
        Revoked = g.Count(p => p.IsRevoked)
    })
    .ToArrayAsync();

为了强制查询使用所有行，我使用了ToArray，但是group by计算

In order to force the query to consume all rows, I use ToArray, however the group by, count and where clauses log they cannot be evaluated remotely.

其他尝试，例如：

var query = await this.context.Models
        .AsNoTracking()
        .GroupBy(i => i.IsRevoked)
        .ToArrayAsync();

产生两个组，稍后可以检查，但它们无法对bit列进行相同的评估。

Produces two groups which I can later inspect but they fail to evaluate the bit column the same.

如何生成一个生成新对象的表达式，其中包含启用了位字段的所有行数和子集数？

How can I generate a single expression that produces a new object with the count of all rows and the count of the subset which have the bit field enabled?

推荐答案

第一种技术（按常数分组）在EF6中效果很好。只是使用基于条件的 Sum 代替了基于谓词的 Count ，而该条件没有直接的SQL等效项，产生了一个不错的 GROUP BY SQL。

The first technique (group by constant) worked well in EF6. Just instead of predicate based Count which has not direct SQL equivalent, using the conditional Sum produced a nice GROUP BY SQL.

不幸的是，即使在2.1版中，这也不会转换为EF Core中的SQL。

Unfortunately, this doesn't translate to SQL in EF Core, even in 2.1.

幸运的是，将其与中间投影结合使用可以在EF 2.1中产生所需的SQL翻译：

Fortunately, combining it with intermediate projection produces the desired SQL translation in EF 2.1:

var counts = await this.context.Models
    .Select(e => new { Revoked = e.IsRevoked ? 1 : 0 })
    .GroupBy(e => 1)
    .Select(g => new ViewModel
    {
        Count = g.Count(),
        Revoked = g.Sum(e => e.Revoked)
    })
    .ToArrayAsync();

这篇关于通过查询和位字段进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！