实体框架：按月有效分组 [英] Entity Framework: Efficiently grouping by month

问题描述

我对此进行了一些研究，到目前为止，我发现最好的方法是对整个数据集使用Asenumerable，以便对对象而不是对数据库进行linq过滤。我正在使用最新的EF。

I've done a bit of research on this, and the best I've found so far is to use an Asenumerable on the whole dataset, so that the filtering occurs in linq to objects rather than on the DB. I'm using the latest EF.

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

My working (but very slow) code is:

        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格式的月份，以及总额和平均金额。但是，每次都需要几分钟。

This gives me months in format YYYY-MM, along with the total amount and average amount. However it takes several minutes every time.

我的另一种解决方法是在SQL中进行更新查询，因此我有一个YYYYMM字段作为本地分组依据。更改数据库不是一个容易的解决方案，因此，任何建议都将不胜感激。

My other workaround is to do an update query in SQL so I have a YYYYMM field to group natively by. Changing the DB isn't an easy fix however so any suggestions would be appreciated.

我发现上述代码想法的线程（http://stackoverflow.com/questions / 1059737 / group-by-weeks-in-linq-to-entities）提到等到.NET 4.0。在这种情况下，最近引入了什么帮助吗？

The thread I found the above code idea (http://stackoverflow.com/questions/1059737/group-by-weeks-in-linq-to-entities) mentions 'waiting until .NET 4.0'. Is there anything recently introduced that helps in this situation?

推荐答案

性能不佳的原因是整个表都被提取到内存中（AsEnumerable（））。您可以像这样按年和月分组

The reason for poor performance is that the whole table is fetched into memory (AsEnumerable()). You can group then by Year and Month like this

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

编辑

edit

根据我的回答，原始查询试图在int和字符串之间进行串联，EF无法将其转换为SQL语句。我可以使用 SqlFunctions 类，但是查询变得很难看。因此，我在进行分组后添加了 AsEnumerable （） ，这意味着EF将在服务器上执行组查询，并获得年，月等，但是自定义在对象上进行投影（在 AsEnumerable （）之后是什么）。

The original query, from my response, was trying to do a concatenation between an int and a string, which is not translatable by EF into SQL statements. I could use SqlFunctions class, but the query it gets kind ugly. So I added AsEnumerable() after the grouping is made, which means that EF will execute the group query on server, will get the year, month, etc, but the custom projection is made over objects (what follows after AsEnumerable()).

这篇关于实体框架：按月有效分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！