C# 在 LINQ to Entities 中按周分组

Group by Weeks in LINQ to Entities

提问by StriplingWarrior

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 TrackedTimehas a TargetDatewhich 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):

我有一个应用程序，允许用户输入他们花在工作上的时间，我正在尝试为此构建一些利用 LINQ to Entities 的良好报告。因为每个TrackedTime都有 a TargetDate，它只是 a 的“日期”部分，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)
                };

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

多亏了这个DateTime.Month属性，按用户和月份分组只是稍微复杂一点：

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 responseto a similar LINQ to SQL question:

现在是棘手的部分。有没有可靠的方法按周分组？我尝试以下根据该响应，以类似的LINQ to 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)
                };

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.

但是 LINQ to Entities 似乎不支持 DateTime 对象上的算术运算，因此它不知道该怎么做(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?

我已经考虑在数据库中创建一个视图，它只是将几天映射到几周，然后将该视图添加到我的实体框架上下文中，并在我的 LINQ 查询中加入它，但这对于这样的事情来说似乎有很多工作。算术方法有什么好的解决方法吗？与 Linq to Entities 一起使用的东西，我可以简单地合并到 LINQ 语句中而不必接触数据库？有什么方法可以告诉 Linq to Entities 如何从另一个日期中减去一个日期？

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!

我要感谢大家的深思熟虑和创造性的回应。经过反复考虑，这个问题的真正答案似乎是“等到 .NET 4.0”。我将感谢 Noldorin 提供仍然利用 LINQ 的最实用的答案，并特别提到 Jacob Proffitt 提出了一个使用实体框架的响应，而无需在数据库端进行修改。还有其他很棒的答案，如果你是第一次看这个问题，我强烈建议你通读所有已经投票的答案，以及他们的评论。这确实是 StackOverflow 强大功能的绝佳示例。谢谢你们！

采纳答案by Noldorin

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 AsEnumerableextension method.

您应该能够使用对AsEnumerable扩展方法的调用来强制查询使用 LINQ to Objects 而不是 LINQ to Entities 进行分组。

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 whereclause gets executed by LINQ to Entities, but the groupclause, which is too complex for Entities to handle, gets done by LINQ to Objects.

这至少意味着该where子句由 LINQ to Entities 执行，但是该group子句对于 Entities 来说太复杂而无法处理，而是由 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.

这是另一个建议，它可能允许您在整个过程中使用 LINQ to Entities。

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

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

这只是扩展了操作，以便只执行整数减法而不是DateTime减法。

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

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

回答by dmo

You could try an extension method for doing the conversion:

您可以尝试使用扩展方法进行转换：

static int WeekNumber( this DateTime dateTime, DateTime firstDay) {
    return (dateTime - firstDay).Days / 7;
}

Then you'd have:

那么你会有：

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

回答by dmo

Well, it might be more work depending on how the DB is indexed, but you could just sort by day, then do the day -> week mapping on the client side.

好吧，这可能需要更多的工作，具体取决于数据库的索引方式，但您可以按天排序，然后在客户端进行天 -> 周映射。

回答by Pavel Minaev

Hereis the list of methods supported by LINQ to Entities.

以下是 LINQ to Entities 支持的方法列表。

The only option that I see is to retrieve the week number from DateTime.Month and DateTime.Day. Something like this:

我看到的唯一选项是从 DateTime.Month 和 DateTime.Day 检索周数。像这样的东西：

int yearToQueryIn = ...;
int firstWeekAdjustment = (int)GetDayOfWeekOfFirstDayOfFirstWeekOfYear(yearToQueryIn);

from t in ...
where t.TargetDate.Year == yearToQueryIn
let month = t.TargetDate.Month
let precedingMonthsLength =
    month == 1 ? 0 :
    month == 2 ? 31 :
    month == 3 ? 31+28 :
    month == 4 ? 31+28+31 : 
    ...
let dayOfYear = precedingMonthsLength + t.TargetDate.Day
let week = (dayOfYear + firstWeekAdjustment) / 7
group by ... 

回答by Sam Mackrill

I had exactly this problem in my Time tracking application which needs to report tasks by week. I tried the arithmetic approach but failed to get anything reasonable working. I ended-up just adding a new column to the database with the week number in it and calculating this for each new row. Suddenly all the pain went away. I hated doing this as I am a great believer in DRYbut I needed to make progress. Not the answer you want but another users point of view. I will be watching this for a decent answer.

我在我的时间跟踪应用程序中遇到了这个问题，它需要按周报告任务。我尝试了算术方法，但没有得到任何合理的工作。我最终只是在数据库中添加了一个新列，其中包含周数，并为每个新行计算它。突然，所有的痛苦都消失了。我讨厌这样做，因为我非常相信DRY，但我需要取得进展。不是您想要的答案，而是其他用户的观点。我会看这个以获得一个体面的答案。

回答by Jacob Proffitt

Okay, this is possible, but it's a huge PITA and it bypasses the LINQ part of the Entity Framework. It took some research, but you can accomplish what you want with the SqlServer provider-specific function SqlServer.DateDiff() and "Entity SQL". This means you're back to string queries, and manually populating custom objects, but it does what you want. You might be able to refine this a bit by using a Select instead of a foreach loop and such, but I actually got this to work on a designer-generated entity set of YourEntities.

好的，这是可能的，但它是一个巨大的 PITA，它绕过了实体框架的 LINQ 部分。这需要进行一些研究，但您可以使用特定于 SqlServer 提供程序的函数 SqlServer.DateDiff() 和“实体 SQL”来完成您想要的操作。这意味着您将返回到字符串查询，并手动填充自定义对象，但它会执行您想要的操作。您可以通过使用 Select 而不是 foreach 循环等来稍微改进它，但我实际上让它在设计器生成的 YourEntities 实体集上工作。

void getTimes()
{
    YourEntities context = new YourEntities();
    DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
    var weeks = context.CreateQuery<System.Data.Common.DbDataRecord>(
        "SELECT t.User.UserName, SqlServer.DateDiff('DAY', @beginDate, t.TargetDate)/7 AS WeekNumber " +
        "FROM YourEntities.TrackedTimes AS t " +
        "GROUP BY SqlServer.DateDiff('DAY', @beginDate, t.TargetDate)/7, t.User.UserName", new System.Data.Objects.ObjectParameter("beginDate", firstDay));
    List<customTimes> times = new List<customTimes>();
    foreach (System.Data.Common.DbDataRecord rec in weeks)
    {
        customTimes time = new customTimes()
        {
            UserName = rec.GetString(0),
            WeekNumber = rec.GetInt32(1)
        };
        times.Add(time);
    }
}

class customTimes
{
    public string UserName{ get; set; }
    public int WeekNumber { get; set; }
}

回答by sgmoore

I don't use Linq to Entities, but if it supports .Year, .Day .Week as well as integer division and modulo then it should be possible to work out the week number using Zellers algorithm/congruence.

我不使用 Linq to Entities，但如果它支持 .Year、.Day .Week 以及整数除法和模数，那么应该可以使用 Zellers 算法/同余计算周数。

For more details see http://en.wikipedia.org/wiki/Zeller's_congruence

有关更多详细信息，请参阅http://en.wikipedia.org/wiki/Zeller's_congruence

Whether it is worth the effort, I shall leave up to you/others.

是否值得努力，我会留给你/其他人。

Edit:

编辑：

Either wikipedia is wrong, or the forumla I have is not Zeller's , I don't know if it has a name, but here it is

要么维基百科是错的，要么我的论坛不是泽勒的，我不知道它是否有名字，但在这里

 weekno = (( 1461 * ( t.TransactionDate.Year + 4800 
           + ( t.TransactionDate.Month - 14 ) / 12 ) ) / 4
           +  ( 367 * ( t.TransactionDate.Month - 2 - 12 *
                    ( ( t.TransactionDate.Month - 14 ) / 12 ) ) ) / 12
            -   ( 3 * ( ( t.TransactionDate.Year + 4900 
            + ( t.TransactionDate.Month - 14 ) / 12 ) / 100 ) ) / 4 
            +   t.TransactionDate.Day - 32075) / 7 

So it should be possible to use this in a group by (This may need to be tweaked depending on what day your week starts on).

所以应该可以在一个组中使用它（这可能需要根据你一周的开始日期进行调整）。

Proviso. This is a formula that I have never used 'for real'. I typed in by hand (can't remember, but I may have copied it from a book), which means that, although I tripled-checked I had my brackets correct, it is possible the source was wrong. So, you need to verify that it works before using.

附带条件。这是我从未“真正”使用过的公式。我是手工输入的（不记得了，但我可能是从书上复制的），这意味着，虽然我三重检查我的括号是正确的，但来源可能是错误的。因此，您需要在使用前验证它是否有效。

Personally, unless the amount of data is massive, I would prefer to return seven times the data and filter locally rather than use something like this, (which maybe explains why I have never used the forumla)

就个人而言，除非数据量很大，否则我宁愿返回七倍的数据并在本地过滤，而不是使用这样的东西，（这也许可以解释为什么我从未使用过论坛）

回答by Colin

Weird no one's posted the GetWeekOfYear method of the .NET Framework yet.

奇怪的是还没有人发布 .NET Framework 的 GetWeekOfYear 方法。

just do your first select, add a dummy weeknumber property, then loop over all of them and use this method to set the correct weeknumber on your type, it should be fast enough to do jhust one loop between 2 selects shouldn't it?:

只需执行您的第一个选择，添加一个虚拟周数属性，然后遍历所有这些属性并使用此方法在您的类型上设置正确的周数，它应该足够快，可以在 2 个选择之间执行一次循环，不是吗？：

public static int GetISOWeek(DateTime day)
{
  return System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(day, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}

回答by Your Friend Ken

does lambda count? I started with linq syntax but I seem to think in C# 3.0 lambda expressions when it comes to declarative programming.

lambda 算不算？我从 linq 语法开始，但我似乎在 C# 3.0 lambda 表达式中考虑声明式编程。

public static void TEST()
{
    // creating list of some random dates
    Random rnd = new Random();
    List<DateTime> lstDte = new List<DateTime>();
    while (lstDte.Count < 100)
    {
        lstDte.Add(DateTime.Now.AddDays((int)(Math.Round((rnd.NextDouble() - 0.5) * 100))));
    }
    // grouping 
    var weeksgrouped = lstDte.GroupBy(x => (DateTime.MaxValue - x).Days / 7);
}

回答by Nick Craver

I think the only problem you're having is arithmetic operations on the DateTime Type, try the example below. It keeps the operation in the DB as a scalar function, only returns the grouped results you want. The .Days function won't work on your end because TimeSpan implementation is limited and (mostly) only available in SQL 2008 and .Net 3.5 SP1, additional notes here on that.

我认为您遇到的唯一问题是 DateTime 类型的算术运算，请尝试以下示例。它将数据库中的操作作为标量函数保留，只返回您想要的分组结果。.Days 函数对您不起作用，因为 TimeSpan 实现是有限的，并且（大部分）仅在 SQL 2008 和 .Net 3.5 SP1 中可用，此处的附加说明。

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

Function in the database added to your context (as context.WeekOfYear):

添加到您的上下文的数据库中的函数（作为 context.WeekOfYear）：

CREATE FUNCTION WeekOfYear(@Date DateTime) RETURNS Int AS 
BEGIN
RETURN (CAST(DATEPART(wk, @Date) AS INT))
END

For additional reference: Here are the supported DateTime methods that do translate properly in a LINQ to SQL scenario

其他参考： 以下是在 LINQ to SQL 场景中正确转换的受支持 DateTime 方法