动态地天/周/月/年的运行分组 [英] Dynamically grouping by Day/Week/Month/Year at Runtime

问题描述

我想不同的基础上传递到我的函数一个int组我的查询。目前我有这个非常哈克解决方案：

I'd like to group my query differently based on an int passed into my function. Currently I have this very hacky solution:

`.GroupBy(occurrence => new { date = 
                // Bucket by day.
                timeBucket == 0 ? DbFunctions.TruncateTime(occurrence.occurrenceDate) : 
                // Bucket by week.
                timeBucket == 1 ? DbFunctions.AddDays(DbFunctions.CreateDateTime(occurrence.occurrenceDate.Year, 1, 1, 0, 0, 0), 7*(occurrence.occurrenceDate.DayOfYear/7)) : 
                // Bucket by month.
                timeBucket == 2 ? DbFunctions.TruncateTime(DbFunctions.CreateDateTime(occurrence.occurrenceDate.Year, occurrence.occurrenceDate.Month, 1, 1, 1, 1)) :
                // Bucket by year.
                DbFunctions.TruncateTime(DbFunctions.CreateDateTime(occurrence.occurrenceDate.Year, 1, 1, 1, 1, 1)),
                type = occurrence.type })`

我是如何计算日期的细节并不是我来说太重要（但随时反正给予帮助）。我想，以避免不必去通过这个case语句对数据库中的每一行。反正有没有避免这样做呢？我已经尝试了各种像使用表达式的解决方案，但我不能回到我想要的对象，因为表达式树必须参...

The specifics of how I calculate the dates isn't too important to me (but feel free to give help anyway). I would like to avoid having to go through this case statement for each row in the database. Is there anyway to avoid doing this? I've tried a variety of solutions like using an expression, but I couldn't return the object I wanted because the expression tree must be parameterless...

如果任何人有一个解决方案是，将不胜感激。

If anyone has a solution it would be much appreciated.

谢谢！

推荐答案

定义一个类石斑鱼：

class TimeGrouper
{
    public int Year { get; set; }
    public int Month { get; set; }
    public int Week { get; set; }
    public int Day { get; set; }
}

和返回表达式的函数：

using System.Data.Entity.SqlServer;
...
Expression<Func<Occurrence, TimeGrouper>> GetGrouper(int grouping)
{
    switch (grouping)
    {
        case 1:
            return o => new TimeGrouper
                        { 
                            Year = o.occurrenceDate.Year
                        };
        case 2:
            return o => new TimeGrouper 
                        { 
                            Year = o.occurrenceDate.Year,
                            Month = o.occurrenceDate.Month
                        };
        case 3:
            return o => new TimeGrouper 
                        {
                            Year = o.occurrenceDate.Year,
                            Week = SqlFunctions.DatePart("wk", o.StartDate).Value
                        };
        default:
            return o => new TimeGrouper
                        {
                            Year = o.occurrenceDate.Year,
                            Day = SqlFunctions.DatePart("dy", o.StartDate).Value
                        };
    }
}

现在你可以叫

db.Occurrences.GroupBy(GetGrouper(3));

这篇关于动态地天/周/月/年的运行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！