在LINQ to Entities中每周一组 [英] Group by Weeks in LINQ to Entities

问题描述

我有一个应用程序，允许用户输入他们花费工作的时间，我正在努力获得一些良好的报告，为此利用LINQ to Entities。因为每个 TrackedTime 有一个 TargetDate ，它只是 DateTime ，用户和日期对时间进行分组比较简单（为了简单起见，我将where子句放在一起）：

  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 用户分组和月份只是稍微复杂一点：

  var userTimes = from context in Context.TrackedTimes 
 group t通过新的{t.User.UserName，t.TargetDate.Month}到ut 
中选择新的
 {
 UserName = ut.Key.UserName，
 MonthNumber = ut.Key。月，
分钟= ut.Sum（t => t.Minutes）
}; 
  

现在是棘手的部分。有一个可靠的方式来按周分组吗？我尝试以下基于此回复到类似的LINQ到SQL问题：

  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 to Entities似乎不支持对DateTime对象进行算术运算，因此它不知道如何做（t.TargetDate - firstDay）.Days / 7 。

我考虑过创建一个在数据库中查看简单地映射几天到几周，然后将该视图添加到我的实体框架上下文中，并在我的LINQ查询中加入它，但这似乎是很多工作。算术方法有很好的解决方法吗？一些与Linq to Entities一起使用的东西，我可以简单地将其并入到LINQ语句中，而无需触摸数据库？某些方式告诉Linq to Entities如何从另一个减去一个日期？

总结

我想感谢大家的周到和创造性的回应。毕竟这一切都是来回的，它看起来像这个问题的真正答案是等到.NET 4.0。我将向诺多林提供赏金，以提供仍然利用LINQ的最实际的答案，特别提到雅各布Proffitt提出使用实体框架的响应，而不需要在数据库方面进行修改。还有其他很好的答案，如果你是第一次看这个问题，我强烈建议您阅读所有被投票的内容，以及他们的意见。这实际上是StackOverflow功能的一个很好的例子。谢谢大家！

解决方案

您应该可以强制查询使用LINQ to Objects而不是LINQ to Entities进行分组使用调用 AsEnumerable 扩展方法。

尝试以下操作：

  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 
选择新
 {
 UserName = ut.Key.UserName，
 WeekNumber = ut.Key.WeekNumber，
 Minutes = ut。 Sum（t => t.Minutes）
}; 
  

这至少意味着其中子句由LINQ to Entities执行，但对于实体来说太复杂的组子句由LINQ to Object完成。

如果您有任何运气，请通知我。

更新

另外一个建议可能会让你在整个事情上使用LINQ to Entities。

 （t.TargetDate.Days  -  firstDay。天）/ 7 
  

这简单地展开了这个操作，所以只执行整数减法，而不是 DateTime 减法。

目前未经测试，因此可能会或可能无法正常工作。

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

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

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

But LINQ to Entities doesn't appear to support arithmetic operations on DateTime objects, so it doesn't know how to do (t.TargetDate - firstDay).Days / 7.

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?

Summary

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!

解决方案

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.

Try the following:

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

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.

Update

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

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

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 to Entities中每周一组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！