EF Core Group By 翻译支持条件总和 [英] EF Core Group By translation support on conditional sum

问题描述

听到 EF Core 2.1 将支持分组翻译，我真的很兴奋.我下载了预览版并开始对其进行测试，但发现在许多地方我仍然无法按翻译分组.

I was really excited to hear that EF Core 2.1 will be supporting group by translations. I downloaded the preview and started testing it out but found that I am still not getting group by translations in a number of places.

在下面的代码片段中，对 TotalFlagCases 的查询将阻止组翻译工作.无论如何，我可以重写它以便我可以通过支持获得团队吗?或者我可以采取的另一种方法?

In the code snippet below, the query for TotalFlagCases will prevent the group by translation from working. Is there anyway that I can rewrite this so that I can have group by support? Or perhaps another approach that I can take?

此表中有很多行，我不希望 .NET 必须加载所有这些行.我也使用行级数据，但一次只有大约 15 条记录.

There are a lot of rows in this table and I don't want .NET to have to load all of these rows. I use row level data as well, but only about 15 records at a time.

var headerTask = (from c in cases
    group c by 1
    into g
    select new CaseHeader
    {
        TotalCases = g.Count(),
        // ... A number of other aggregates
        TotalFlagCases = g.Where(a => a.Flag).Sum(b => 1),
    })
.DefaultIfEmpty(new CaseHeader()).FirstAsync();

推荐答案

在此版本的 EF Core 中有一种方法可以进行条件求和.所提供的代码不会通过 GROUP BY 转换为所需的 SQL，但也许未来的某个版本会以这种方式支持它.现在你可以尝试这样的事情:

There is a way to do a conditional sum in this version of EF Core. Provided code is not going to be translated into desired SQL with GROUP BY but maybe some future version will support it this way. For now you can try something like this:

var headerTask = cases
    .Select(c => new
    {
        c.Flag,
        c.YourKey,
        //other properties
    })
    .GroupBy(c => c.YourKey, (k, g) => new CaseHeader
    {
        TotalCases = g.Count(),
        // ... A number of other aggregates
        TotalFlagCases = g.Sum(b => a.Flag ? 1 : 0)
    });

当您将实体投影为匿名类型，然后将其分组并在聚合函数中使用条件运算符时，它将被转换为带有 GROUP BY 和聚合的 SQL，例如:

When you project your entity into an anonymous type and then group it and use conditional operator in an aggregate function it will be translated into SQL with GROUP BY and aggregates like:

SELECT COUNT(*) AS [TotalCases], SUM(CASE
    WHEN [c].[Flag] = 1
    THEN 1 ELSE 0
END) AS [TotalFlagCases]
FROM [Cases] AS [c]
GROUP BY [c].[YourKey]

当您不将其投影到匿名类型时，因此当缺少上述 Select 函数时，它不会被转换为带有 GROUP BY 的 SQL.看起来这个预发布的查询翻译器不支持它或者它是一个错误.

When you do not project it to an anonymous type so when there is the above Select function missing it will not be translated into SQL with GROUP BY. It looks like the query translator for this prerelease does not support it or it's a bug.

这篇关于EF Core Group By 翻译支持条件总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！