在LINQ to Entities中按周分组

Question

我有一个应用程序，允许用户输入他们工作所花费的时间，并且我正在尝试构建一些好的报告来利用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 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)
                };

现在进入棘手的部分。有可靠的方法来按周分组吗？我尝试了以下基于这个回答的类似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)
                };

但是LINQ to Entities似乎不支持DateTime对象的算术运算，所以它不知道如何执行(t.TargetDate - firstDay).Days / 7。

我考虑在数据库中创建一个视图，将天数简单映射到周数，然后将该视图添加到我的Entity Framework上下文中，并在LINQ查询中加入它，但这似乎对于像这样的东西来说太麻烦了。有没有好的解决方法来处理算术方法？有什么适用于Linq to Entities的好方法，我可以在LINQ语句中直接使用而不必触及数据库？有没有一种方法告诉Linq to Entities如何从一个日期中减去另一个日期？

总结：

感谢大家的周到和创造性的回答。经过这一番探讨，看起来这个问题的真正答案是“等到.NET 4.0”。我将把赏金给Noldorin，因为他给出了最实用的答案，同时还利用了LINQ，特别提到Jacob Proffitt发现了一种使用Entity Framework的响应，而无需对数据库进行修改。还有其他很棒的答案，如果你第一次看到这个问题，我强烈建议阅读所有得到投票的答案以及它们的评论。这真的是StackOverflow的力量的一个绝妙的例子。谢谢大家！

- StriplingWarrior

---

请问您能否澄清跨年查询的要求？从您的示例中看到GetFirstDayOfFirstWeekOfYear，我认为您基本上只查询可以假定年份相同的记录。您是否实际上正在查询任意日期，并从指定的任意点（可能是一年的第一天，也可能不是）计算周数？还是仍然只有单年，仅调整以考虑年初的“短周”就足够了？ - Pavel Minaev

是的，抱歉。我使用GetFirstDayOfFirstWeekOfYear是想表明有一个日期，可以从中减去所有其他日期，以确定它们所在的周数。最终，我想将这些周表示为日期范围（7/12/09 - 7/18/09），但我认为这超出了本问题的范围。 - StriplingWarrior

Answer 1

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

请尝试以下操作：

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

这至少意味着where子句由LINQ to Entities执行，但是group子句太复杂，Entities无法处理，因此需要由LINQ to Objects完成。

如果有任何进展，请告诉我。

更新：

这里有另一个建议，可能允许您完全使用LINQ to Entities。

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

这只是扩展了操作，使其仅执行整数减法而不是 DateTime 减法。

目前尚未经过测试，因此可能有效也可能无效...

- Noldorin

---

正如我对dmo所评论的那样，这种方法可能是可行的，但它会消除使用Linq to Entities的许多优势。它会导致更多的数据在数据库和应用程序之间传输，这是不必要的。就我而言，正确的解决方案是使Linq to Entities能够在数据库端进行分组。 - StriplingWarrior

DateTime没有.Days属性。.Days来自TimeSpan对象，当您减去两个DateTime时会创建该对象，这将使我们回到最初的问题，因为我们无法减去两个DateTime。我会尝试使用.Ticks并乘以一天中的tick数，但是在Entity Framework中.Ticks也不起作用。根据其他论坛的一些帖子，看起来随着.NET 3.5截止日期的临近，他们决定撤回有限的DateDiff支持，以便在.NET 4中提供更全面的支持。也许我只能等待。 - StriplingWarrior

加1分，因为你不仅提出了一个实用的（虽然不完美）解决方案，还包括了清晰的示例代码，而dmo却没有这样做。 - StriplingWarrior

没错。恭喜你获得了赏金。 - StriplingWarrior

你能帮我解决这个LINQ查询吗？ http://stackoverflow.com/questions/38120664/how-to-group-by-on-2-child-entities-and-get-total-of-both-this-child-entities - Learning-Overthinker-Confused

Answer 2

只要你使用SQLServer，就可以在LINQ中使用SQL函数：

using System.Data.Objects.SqlClient;

var codes = (from c in _twsEntities.PromoHistory
                         where (c.UserId == userId)
                         group c by SqlFunctions.DatePart("wk", c.DateAdded) into g
                         let MaxDate = g.Max(c => SqlFunctions.DatePart("wk",c.DateAdded))
                         let Count = g.Count()
                         orderby MaxDate
                         select new { g.Key, MaxDate, Count }).ToList();

这个示例来自MVP Zeeshan Hirani在这个帖子中的贡献： MSDN

- HBlackorby

---

你能否扩展这个示例，以展示如何使得有人可以将项目分组为一周？ - StriplingWarrior

我的意思是说，如果您提供相关的EDM函数并稍微重写查询以减少面向对象的特性，那么您可以以这种方式使用所有日期函数。我自己正在将其用于数据库查询。 - HBlackorby

这将涉及到主要使用AddDays；我会完成一个完整的示例并发布它。 - HBlackorby

对于这篇帖子造成的混淆，我感到抱歉；在发现我不能在Linq中使用两个嵌套的EDM函数后，我编辑了我的答案以使用SqlFunctions；Linq处理器在SQL语句中出现无效的表别名时会崩溃。 - HBlackorby

Answer 3

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

- Sam Mackrill

---

听起来你采用的是与我想创建的视图相似的模式。感谢您抽出时间让我知道它对您有用。 - StriplingWarrior

Answer 4

这里是LINQ to Entities支持的方法列表。

我所看到的唯一选择是从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 ... 

- Pavel Minaev

---

如果我正确理解你的例子，你只是找到一年中的日期并将其除以7。如果我从一年的第一天开始计算周数，这是正确的。但是，我希望让每周始终从给定的某一天开始（例如星期日）。此外，最好使查询在跨年度时也能正常工作（例如在'09年12月和'10年1月之间）。不过，支持方法列表加1分！ - StriplingWarrior

Answer 5

好的，这是可能的，但它非常麻烦并且绕过了Entity Framework的LINQ部分。经过一些研究，您可以通过SqlServer提供程序特定的函数SqlServer.DateDiff()和"Entity 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; }
}

- Jacob Proffitt

---

可能不是我会选择的结果，因为显而易见的原因，但是对于认真的努力和开拓思路给予+1的肯定！ - StriplingWarrior

请记住，基础对象“weeks”仍然是IQueryable，因此您可以迭代地构建它。因此，例如，在上面之后，您可以执行以下操作：weeks = weeks.Where("t.User = 'Smith'"); - Jacob Proffitt

Answer 6

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

更多详细信息请参见http://en.wikipedia.org/wiki/Zeller的同余公式。

是否值得付出努力，我将留给您/其他人决定。

编辑：

要么维基百科错了，要么我拥有的公式不是Zeller的公式，我不知道它是否有名称，但是这就是它。

 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 

因此，应该可以在“分组依据”中使用这个公式（这可能需要根据一周的起始日进行调整）。

注意：我从未真正使用过这个公式。我是手动输入的（记不清了，但可能是从书上复制的），这意味着虽然我三次检查了括号是否正确，但可能来源是错误的。因此，在使用之前，您需要验证其有效性。

就我个人而言，除非数据量非常大，否则我宁愿返回七倍的数据并在本地进行筛选，而不是使用像这样的东西（这也许可以解释为什么我从未使用过这个公式）。

- sgmoore

---

我同意你的评估：牺牲性能以获得可读性等方面更好。我意识到我在对这个问题的回应中要求的比实际上“实用”的要多，但我认为这是值得的，可以鼓励创造性的答案。给一个疯狂的回答+1，它可能真的有效。 - StriplingWarrior

Answer 7

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

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

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

- Colin

---

知道这种方法的存在很好，但由于它不受Entity Framework支持，它并不能回答这个问题。 - StriplingWarrior

Answer 8

另一种解决方案是：每天记录一次组数据，然后对它们进行迭代。当您看到周开始时，执行求和或计数操作。

以下是一个计算每周收入统计的样本代码。

GetDailyIncomeStatisticsData方法按日从数据库检索数据。

   private async Task<List<IncomeStastistic>> GetWeeklyIncomeStatisticsData(DateTime startDate, DateTime endDate)
    {
        var dailyRecords = await GetDailyIncomeStatisticsData(startDate, endDate);
        var firstDayOfWeek = DateTimeFormatInfo.CurrentInfo == null
            ? DayOfWeek.Sunday
            : DateTimeFormatInfo.CurrentInfo.FirstDayOfWeek;

        var incomeStastistics = new List<IncomeStastistic>();
        decimal weeklyAmount = 0;
        var weekStart = dailyRecords.First().Date;
        var isFirstWeek = weekStart.DayOfWeek == firstDayOfWeek;

        dailyRecords.ForEach(dailyRecord =>
        {
            if (dailyRecord.Date.DayOfWeek == firstDayOfWeek)
            {
                if (!isFirstWeek)
                {
                    incomeStastistics.Add(new IncomeStastistic(weekStart, weeklyAmount));
                }

                isFirstWeek = false;
                weekStart = dailyRecord.Date;
                weeklyAmount = 0;
            }

            weeklyAmount += dailyRecord.Amount;
        });

        incomeStastistics.Add(new IncomeStastistic(weekStart, weeklyAmount));
        return incomeStastistics;
    }

- Alper Ebicoglu

Answer 9

我认为你遇到的唯一问题是在DateTime类型上进行算术运算，请尝试以下示例。它将操作保留在DB中作为标量函数，仅返回您想要的分组结果。.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)
    };

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

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

额外参考： 这里是在 LINQ to SQL 场景中正确转换的支持 DateTime 方法列表

- Nick Craver

---

这看起来很有前途。类似于我之前想到的View概念，但更加可重用。我明天早上会看看它是否有效。如果有效，你可能会得到赏金。 - StriplingWarrior

你非常接近成功了，但我们再次被当前版本的Entity Framework的不足所挫败，它不允许我们使用Linq to Entities调用用户定义函数。不过，你的回答非常完整，加一分。 - StriplingWarrior

Answer 10

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

- Your Friend Ken

---

我遇到的困难不在于创建将日数转换为周数的语法。问题在于实体框架无法评估“DateTime.MaxValue-x”，因此一旦尝试实际评估查询，这种方法就行不通了。 - StriplingWarrior