集团通过LINQ到实体周 [英] Group by Weeks in LINQ to Entities

问题描述

我有一个应用程序，允许用户输入他们花的工作时间，而我试图让这充分利用了LINQ到实体建立了一些很好的报告。因为每个 TrackedTime 有一个 TargetDate 这仅仅是日期一部分日期时间，它是相对简单的时代用户和日期（我离开了为简单的，其中条款）组：

I have an application that allows users to enter time they spend working, and I'm trying to get some good reporting built for this which leverages LINQ to Entities. Because each TrackedTime has a TargetDate which is just the "Date" portion of a DateTime, it is relatively simple to group the times by user and date (I'm leaving out the "where" clauses for simplicity):

var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, t.TargetDate} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    TargetDate = ut.Key.TargetDate,
                    Minutes = ut.Sum(t => t.Minutes)
                };

感谢 DateTime.Month 属性，用户分组和月仅稍微复杂一些：

Thanks to the DateTime.Month property, grouping by user and Month is only slightly more complicated:

var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, t.TargetDate.Month} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    MonthNumber = ut.Key.Month,
                    Minutes = ut.Sum(t => t.Minutes)
                };

现在到了棘手的部分。是否有按周的可靠方法组？我尝试以下根据<一个href=\"http://stackoverflow.com/questions/687968/need-some-groupby-linq2sql-help-please/688071#688071\">this响应以类似的LINQ to SQL的问题：

Now comes the tricky part. Is there a reliable way to group by Week? I tried the following based on this response to a similar LINQ to SQL question:

DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    WeekNumber = ut.Key.WeekNumber,
                    Minutes = ut.Sum(t => t.Minutes)
                };

但LINQ到实体似乎并不支持datetime对象算术运算，因此它不知道该怎么办（t.TargetDate - firstDay）.Days / 7 。

我认为在创造简单地映射天至数周的数据库视图，然后添加查看到我的实体框架范围内，在我的LINQ查询加入到它，但这似乎是一个大量的工作，这样的事情这个。是否有一个良好的工作，周围的算术方法？一些使用LINQ工程实体，那我可以简单地纳入LINQ语句，而无需触摸数据库？某种方式告诉LINQ到实体如何从另一个减去一个日期吗？

I've considered creating a View in the database that simply maps days to weeks, and then adding that View to my Entity Framework context and joining to it in my LINQ query, but that seems like a lot of work for something like this. Is there a good work-around to the arithmetic approach? Something that works with Linq to Entities, that I can simply incorporate into the LINQ statement without having to touch the database? Some way to tell Linq to Entities how to subtract one date from another?

我要感谢大家的深思熟虑和创造性的响应。这一切回来，来回之后，它看起来像真正的答案是等到.NET 4.0。我要去给赏金诺多给仍然利用LINQ，特别提到雅各Proffitt能想出一个使用实体框架，而不需要在数据库方面的修改响应的最实际的答案。还有其他伟大的答案也一样，如果你正在寻找这个问题的第一次，我强烈建议在所有的那些中已经被上投了票，以及他们的意见读书。这真的被计算器的力量一个极好的例子。谢谢大家！

I'd like to thank everyone for their thoughtful and creative responses. After all this back-and-forth, it's looking like the real answer to this question is "wait until .NET 4.0." I'm going to give the bounty to Noldorin for giving the most practical answer that still leverages LINQ, with special mention to Jacob Proffitt for coming up with a response that uses the Entity Framework without the need for modifications on the database side. There were other great answers, too, and if you're looking at the question for the first time, I'd strongly recommend reading through all of the ones that have been up-voted, as well as their comments. This has really been a superb example of the power of StackOverflow. Thank you all!

推荐答案

您应该能够强制查询使用LINQ到对象，而不是LINQ到实体为分组，利用给呼叫 AsEnumerable 扩展方法。

You should be able to force the query to use LINQ to Objects rather than LINQ to Entities for the grouping, using a call to the AsEnumerable extension method.

请尝试以下操作：

DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = 
    from t in context.TrackedTimes.Where(myPredicateHere).AsEnumerable()
    group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
    select new
    {
        UserName = ut.Key.UserName,
        WeekNumber = ut.Key.WeekNumber,
        Minutes = ut.Sum(t => t.Minutes)
    };

这将至少意味着其中，条款被通过LINQ的组子句执行到实体，但这是太复杂，实体处理，得到由LINQ做对象。

This would at least mean that the where clause gets executed by LINQ to Entities, but the group clause, which is too complex for Entities to handle, gets done by LINQ to Objects.

让我知道，如果您有任何运气这一点。

Let me know if you have any luck with that.

下面是另外一个建议，这可能会允许您使用LINQ到实体整个事情。

Here's another suggestion, which might allow you to use LINQ to Entities for the whole thing.

(t.TargetDate.Days - firstDay.Days) / 7

这只是扩大了，因此只有整数减法执行，而不是的DateTime 减法操作。

This simply expands the operation so that only integer subtraction is performed rather than DateTime subtraction.

它是目前未测试，所以这可能会或可能不会工作...

It is currently untested, so it may or may not work...

这篇关于集团通过LINQ到实体周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！