Entity Framework：高效地按月分组

Question

对此我进行了一些研究，目前找到的最佳方案是在整个数据集上使用Asenumerable，这样过滤就会发生在LINQ to Objects中而不是数据库中。 我正在使用最新的EF。

我的工作代码（但非常慢）是：

        var trendData = 
            from d in ExpenseItemsViewableDirect.AsEnumerable()
            group d by new {Period = d.Er_Approved_Date.Year.ToString() + "-" + d.Er_Approved_Date.Month.ToString("00") } into g
            select new
            {
                Period = g.Key.Period,
                Total = g.Sum(x => x.Item_Amount),
                AveragePerTrans = Math.Round(g.Average(x => x.Item_Amount),2)
            };

这会给我以YYYY-MM格式的月份、总金额和平均金额。但是每次都需要几分钟的时间。

我另一个解决方法是在SQL中执行更新查询，这样我就有了一个YYYYMM字段可以本地分组。但是更改数据库不是一个简单的解决方案，因此任何建议将不胜感激。

我在以下线程(https://dev59.com/i3NA5IYBdhLWcg3wL6sc)中找到了上述代码想法，其中提到“等到.NET 4.0”。是否有最近引入的任何内容可以帮助解决这个问题？

- Glinkot

Answer 1

性能不佳的原因是整张表被读入内存（AsEnumerable()）。你可以按照年份和月份进行分组，方法如下：

var trendData = 
            (from d in ExpenseItemsViewableDirect
            group d by new {
                            Year = d.Er_Approved_Date.Year, 
                            Month = d.Er_Approved_Date.Month 
                            } into g
            select new
            {
                Year = g.Key.Year,
                Month = g.Key.Month,
                Total = g.Sum(x => x.Item_Amount),
                AveragePerTrans = Math.Round(g.Average(x => x.Item_Amount),2)
            }
       ).AsEnumerable()
        .Select(g=>new {
              Period = g.Year + "-" + g.Month,
              Total = g.Total,
               AveragePerTrans = g.AveragePerTrans
         });

编辑

我的原始响应中的查询试图在整数和字符串之间进行连接，这不可被 EF 转换为 SQL 语句。我可以使用 SqlFunctions 类，但查询会变得很丑陋。所以我在分组后添加了 AsEnumerable()，这意味着 EF 将在服务器上执行分组查询，获取年份、月份等信息，但自定义投影是基于对象进行的 (AsEnumerable() 后面的部分)。

- Adrian Iftode

---

非常好，非常感谢Adrian。我刚刚进行了一个快速测试，你的代码大约需要3.5秒，而原始代码需要5.2秒。长时间的延迟可能是我的程序中的其他步骤。我非常感谢你的努力，你的代码已经开始发挥作用了！ - Glinkot

这不应该是被接受的答案。这只是一个解决方法！@cryss的答案给出了完美的结果。 - Steffen Mangold

优秀的解决方案，“group by new” 真的非常强大，可以简化复杂的查询。 - Jacob

Answer 2

当涉及按月分组时，我更喜欢以这种方式完成任务：

var sqlMinDate = (DateTime) SqlDateTime.MinValue;

var trendData = ExpenseItemsViewableDirect
    .GroupBy(x => SqlFunctions.DateAdd("month", SqlFunctions.DateDiff("month", sqlMinDate, x.Er_Approved_Date), sqlMinDate))
    .Select(x => new
    {
        Period = g.Key // DateTime type
    })

由于它在分组结果中保留日期时间类型。

- cryss

---

这绝对应该是被接受的答案！将简单的MS-SQL月份分离转换为linq-to-entity！ - Steffen Mangold

为什么不使用 GroupBy(g=>new{g.DateTime.Year, g.DateTime.Month}？ - Shawn

Answer 3

与cryss所写的类似，我正在为EF执行以下操作。请注意，我们必须使用EntityFunctions才能调用EF支持的所有DB提供程序。SqlFunctions仅适用于SQLServer。

var sqlMinDate = (DateTime) SqlDateTime.MinValue; 

(from x in ExpenseItemsViewableDirect
let month = EntityFunctions.AddMonths(sqlMinDate, EntityFunctions.DiffMonths(sqlMinDate, x.Er_Approved_Date))
group d by month 
into g
select new
{
Period = g.Key,
   Total = g.Sum(x => x.Item_Amount),
   AveragePerTrans = Math.Round(g.Average(x => x.Item_Amount),2)
}).Dump();

一段生成的SQL示例（来自类似的模式）：

-- Region Parameters
DECLARE @p__linq__0 DateTime2 = '1753-01-01 00:00:00.0000000'
DECLARE @p__linq__1 DateTime2 = '1753-01-01 00:00:00.0000000'
-- EndRegion
SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [C2], 
[GroupBy1].[A1] AS [C3]
FROM ( SELECT 
    [Project1].[C1] AS [K1], 
    FROM ( SELECT 
        DATEADD (month, DATEDIFF (month, @p__linq__1, [Extent1].[CreationDate]), @p__linq__0) AS [C1]
        FROM [YourTable] AS [Extent1]
    )  AS [Project1]
    GROUP BY [Project1].[C1]
)  AS [GroupBy1]

- Boris