atashbahar.com

thoughts, ideas and ...

Group by day, week, month, quarter and year in Entity Framework (Linq to SQL)

Consider having following Product class and you want to get some stats based on the creation date of the product.

public class Product
{
    public int Id { get; set; }
    public string { get; set; }
    public DateTime CreateDate { get; set; }
}

What we want to query here is the number of products created every day, week, month, querter and year; we also want to get the stats for certain number of days.

public ActionResult Stats(int days = 365, DateGroupType group = DateGroupType.Week)
{
    var startDate = DateTime.Now.AddDays(-1 * days);
    var allStats = _db
        .Products
        .Where(x => x.CreateDate > startDate);

    var groupedStats =  GroupByDate(allStats, group, startDate);

    var stats = groupedStats.Select(x => new
        {
            x.Key,
            Count = x.Count()
        })
        .ToList()
        .Select(x => new Stat
        {
            Created = GetDate(group, x.Key.Value, startDate),
            x.Count
        })
        .OrderBy(x => x.Created)
        .ToList();

    return stats;
}

private IQueryable<IGrouping<int?, Product>> GroupByDate(IQueryable<Product> products, DateGroupType group, DateTime startDate)
{
    switch (group)
    {
        case DateGroupType.Day:
            return products.GroupBy(x => SqlFunctions.DateDiff("dd", startDate, x.CreateDate));

        case DateGroupType.Week:
            return products.GroupBy(x => SqlFunctions.DateDiff("ww", startDate, x.CreateDate));

        case DateGroupType.Month:
            return products.GroupBy(x => SqlFunctions.DateDiff("mm", startDate, x.CreateDate));

        case DateGroupType.Quarter:
            return products.GroupBy(x => SqlFunctions.DateDiff("qq", startDate, x.CreateDate));

        case DateGroupType.Year:
            return products.GroupBy(x => SqlFunctions.DateDiff("yy", startDate, x.CreateDate));

        default:
            throw new NotSupportedException($"Grouping by '{group}' is not supported");
    }
}

private DateTime GetDate(DateGroupType group, int diff, DateTime startDate)
{
    switch (group)
    {
        case DateGroupType.Day:
            return startDate.AddDays(diff);

        case DateGroupType.Week:
            return startDate.AddDays(diff * 7);

        case DateGroupType.Month:
            return startDate.AddMonths(diff);

        case DateGroupType.Quarter:
            return startDate.AddMonths(diff * 3);

        case DateGroupType.Year:
            return startDate.AddYears(diff);

        default:
            throw new NotSupportedException($"Grouping by '{group}' is not supported");
    }
}

There are a few classes and enumerations used in this code that are listed below:

public class Stat
{
    public DateTime CreateDate { get; set; }
    public int Count { get; set; }
}

public enum DateGroupType
{
    Day,
    Week,
    Month,
    Quarter,
    Year
}