按Linq to SQL中的每周（周数）分组

In regular SQL i could do something like

在常规SQL中我可以做类似的事情

SELECT * From T GROUP BY DATEPART(wk, T.Date)

How can i do that in Linq to SQL ?

我怎么能在Linq to SQL中做到这一点?

The following don't work

以下不起作用

From F In DB.T Group R By DatePart(DateInterval.WeekOfYear, F.Date)

Also don't work:

也不工作:

From F In DB.T Group R By (F.Date.DayOfYear / 7)

5 个解决方案

#1

Range variable name can be inferred only from a simple or qualified name with no arguments

范围变量名只能从不带参数的简单或限定名称推断

#2

LINQ to SQL does not support the Calendar.WeekOfYear method, but you could potentially create a TSQL function that wraps the call to DatePart. The DayOfYear / 7 trick should work for most cases and is much easier to use. Here's the code I ended up with:

LINQ to SQL不支持Calendar.WeekOfYear方法,但您可能会创建一个TSQL函数来包装对DatePart的调用。 DayOfYear / 7技巧应该适用于大多数情况并且更容易使用。这是我最终得到的代码:

var x = from F in DB.T
        group F by new {Year = F.Date.Year, Week = Math.Floor((decimal)F.Date.DayOfYear / 7)} into FGroup
        orderby FGroup.Key.Year, FGroup.Key.Week
        select new {
            Year = FGroup.Key.Year,
            Week = FGroup.Key.Week,
            Count = FGroup.Count()
        };

Results in something like this:

结果是这样的:

Year    Week    Count
2004    46      3
2004    47      3
2004    48      3
2004    49      3
2004    50      2
2005    0       1
2005    1       8
2005    2       3
2005    3       1
2005    12      2
2005    13      2

#3

This works correctly.

这工作正常。

from F in DB.T group F by F.Date.DayOfYear / 7;

You were specifying the group by incorrectly. The result of this code be a collection of objects. Each object will have a Key property which will be what you grouped by (in this case the result of F.Date.DayOfYear / 7. Each object will be a collection of objects from T that met the group condition.

您错误地指定了该组。此代码的结果是对象的集合。每个对象都有一个Key属性,它将是你的分组(在这种情况下是F.Date.DayOfYear / 7的结果。每个对象将是来自T的符合组条件的对象的集合。

#4

If you are concerned about the culture you are in the following code will take that into account:

如果您关注以下代码中的文化,则会考虑到这一点:

var ci = CultureInfo.CurrentCulture;
var cal = ci.Calendar;
var rule = ci.DateTimeFormat.CalendarWeekRule;
var firstDayOfWeek = ci.DateTimeFormat.FirstDayOfWeek;

var groups = from F in DB.T
             group F by cal.GetWeekOfYear(F, rule, firstDayOfWeek) into R
             select R;

#5

First you should get the date of the first day in the week.

首先,您应该获得一周中第一天的日期。

To get the date of the first day in the week. you can use this code:

获取本周第一天的日期。你可以使用这段代码:

public static class DateTimeExtensions
{
    public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek)
    {
        int diff = dt.DayOfWeek - startOfWeek;
        if (diff < 0)
        {
            diff += 7;
        }
        return dt.AddDays(-1 * diff).Date;
    }
}

Then you can group by the first date of the week.

然后,您可以按一周的第一个日期进行分组。

So this code in regular SQL :

所以常规SQL中的这段代码:

SELECT * From T GROUP BY DATEPART(wk, T.Date)

can be done in Linq to SQL like this

可以像这样在Linq to SQL中完成

T.GroupBy(i => i.Date.StartOfWeek(DayOfWeek.Monday));

#1