LINQ GroupByDate Reporting Extension

README.md

LINQ GroupByDate Reporting Extension

This IQueryable<T> extension saves a bit of time when writing a report that groups results by date. You can specify which timeframes to group by (day, week, month, year), or use the default automatic thresholds.

This method also accepts and corrects null dates based on the available results. It does this by performing a simple query to grab the starting date, if it's missing, before running the main report query.

Sample Usage

public class OrderCountRow: ReportingExtensions.ReportRow
{
    public int OrderCount { get; set; }
}

...

var startDate = new DateTime(2015, 1, 1);
var endDate = new DateTime(2015, 12, 31);
var orders = db.Orders.Where(o => o.Status == OrderStatuses.Completed);

var results = orders.GroupByDate(startDate, endDate, k => k.DateOrdered,
    g => new OrderCountRow() { Date = g.Key, OrderCount = g.Count() });

ReportingExtensions.cs

using System;
using System.Collections.Generic;
using System.Data.Entity.SqlServer;
using System.Linq;
using System.Linq.Expressions;
using System.Web;

public static class ReportingExtensions
{
	public static IEnumerable<TResult> GroupByDate<TSource, TResult>(this IQueryable<TSource> source,
		   DateTime? startDate,
		   DateTime? endDate,
		   Expression<Func<TSource, System.DateTime?>> keySelector,
		   Expression<Func<IGrouping<ReportDate, TSource>, TResult>> resultSelector) where TResult : ReportRow, new()
	{

		return GroupByDate<TSource, TResult>(source, startDate, endDate, null, keySelector, resultSelector);
	}

	public static IEnumerable<TResult> GroupByDate<TSource, TResult>(this IQueryable<TSource> source,
		   DateTime? startDate,
		   DateTime? endDate,
		   Dictionary<ReportDateType, int> dateTypeItemCountThreshold,
		   Expression<Func<TSource, System.DateTime?>> keySelector,
		   Expression<Func<IGrouping<ReportDate, TSource>, TResult>> resultSelector) where TResult : ReportRow, new()
	{
		source.CorrectFilterDates(ref startDate, ref endDate, keySelector);

		if (dateTypeItemCountThreshold == null)
		{
			dateTypeItemCountThreshold = new Dictionary<ReportDateType,int>
			{
				{ReportDateType.Day, 31}, //Most days in a month month
				{ReportDateType.Week, 8}, //Just shy of two months
				{ReportDateType.Month, 12} //A year
			};
		}

		var reportSpan = new TimeSpan(endDate.Value.Ticks - startDate.Value.Ticks);
		var dateType = reportSpan.TotalDays <= dateTypeItemCountThreshold[ReportDateType.Day] ? ReportDateType.Day :
			(reportSpan.TotalDays / 7) <= dateTypeItemCountThreshold[ReportDateType.Week] ? ReportDateType.Week :
			(reportSpan.TotalDays / 30.416) <= dateTypeItemCountThreshold[ReportDateType.Month] ? ReportDateType.Month :
			ReportDateType.Year;

		return source.GroupByDate(startDate, endDate, dateType, keySelector, resultSelector);
	}

	public static IEnumerable<TResult> GroupByDate<TSource, TResult>(this IQueryable<TSource> source,
		   DateTime? startDate,
		   DateTime? endDate,
		   ReportDateType dateType,
		   Expression<Func<TSource, System.DateTime?>> keySelector,
		   Expression<Func<IGrouping<ReportDate, TSource>, TResult>> resultSelector) where TResult : ReportRow, new()
	{
		source.CorrectFilterDates(ref startDate, ref endDate, keySelector);

		var nonNullDateMember = Expression.Convert(keySelector.Body, typeof(DateTime));
		var filterDateRangeExpression = Expression.Lambda<Func<TSource, bool>>(
			Expression.AndAlso(
				Expression.GreaterThanOrEqual(nonNullDateMember, Expression.Constant(startDate.Value.Date, typeof(DateTime))),
				Expression.LessThan(nonNullDateMember, Expression.Constant(endDate.Value.AddDays(1).Date, typeof(DateTime)))
				),
			keySelector.Parameters);

		var filteredSource = source.Where(filterDateRangeExpression);

		//Transform the keySelector to ReportDate
		var keySource = keySelector.Parameters[0];
		var year = Expression.Property(nonNullDateMember, "Year");
		var month = dateType == ReportDateType.Day || dateType == ReportDateType.Week || dateType == ReportDateType.Month ?
			(Expression)Expression.Property(nonNullDateMember, "Month") : Expression.Constant(0);
		var week = dateType == ReportDateType.Day || dateType == ReportDateType.Week ?
			(Expression)Expression.Convert(Expression.Call(typeof(SqlFunctions),
				"DatePart", null, Expression.Constant("week"), keySelector.Body), typeof(int)) :
			Expression.Constant(0);
		var day = dateType == ReportDateType.Day ? (Expression)Expression.Property(nonNullDateMember, "Day") : Expression.Constant(0);
		var dateSelector = Expression.Lambda<Func<TSource, ReportDate>>(
			Expression.MemberInit(
				Expression.New(typeof(ReportDate)),
				Expression.Bind(typeof(ReportDate).GetProperty("Year"), year),
				Expression.Bind(typeof(ReportDate).GetProperty("Month"), month),
				Expression.Bind(typeof(ReportDate).GetProperty("Week"), week),
				Expression.Bind(typeof(ReportDate).GetProperty("Day"), day)
			), keySource);

		var results = filteredSource.GroupBy(dateSelector).OrderBy(g => g.Key).Select(resultSelector);

		var resultsGap = new List<TResult>();
		var currentDate = startDate.Value;
		while (currentDate <= endDate)
		{
			var reportRow = (TResult)Activator.CreateInstance(typeof(TResult));
			if (dateType == ReportDateType.Day)
			{
				reportRow.Date = new ReportDate() { Year = currentDate.Year, Month = currentDate.Month, Week = currentDate.GetIso8601WeekOfYear(), Day = currentDate.Day };
				currentDate = currentDate.AddDays(1);
			}
			else if (dateType == ReportDateType.Week)
			{
				reportRow.Date = new ReportDate() { Year = currentDate.Year, Month = currentDate.Month, Week = currentDate.GetIso8601WeekOfYear() };
				currentDate = currentDate.AddDays(7);
			}
			else if (dateType == ReportDateType.Month)
			{
				reportRow.Date = new ReportDate() { Year = currentDate.Year, Month = currentDate.Month };
				currentDate = currentDate.AddMonths(1);
			}
			else if (dateType == ReportDateType.Year)
			{
				reportRow.Date = new ReportDate() { Year = currentDate.Year };
				currentDate = currentDate.AddYears(1);
			}
			resultsGap.Add(reportRow);
		}

		return results.ToList().Union(resultsGap, new ReportRowComparer<TResult>()).OrderBy(o => o.Date);
	}

	/// <summary>
	/// Examines the supplied dates and corrects them with the appropriate date range
	/// </summary>
	/// <typeparam name="TSource"></typeparam>
	/// <param name="source"></param>
	/// <param name="startDate">The start date to be checked and corrected, if needed</param>
	/// <param name="endDate">The end date to be checked and corrected, if needed</param>
	/// <param name="keySelector">The model date field used to correct the start and end dates.</param>
	private static void CorrectFilterDates<TSource>(this IQueryable<TSource> source,
		ref DateTime? startDate,
		ref DateTime? endDate,
		Expression<Func<TSource, System.DateTime?>> keySelector)
	{
		//Create null/min date filter
		var filterNullExpression = Expression.Lambda<Func<TSource, bool>>(
			Expression.AndAlso(
				Expression.NotEqual(keySelector.Body, Expression.Constant(null, keySelector.Type)),
				Expression.GreaterThan(Expression.Property(Expression.Convert(keySelector.Body, typeof(DateTime)), "Year"), Expression.Constant(1900, typeof(int)))
				),
			keySelector.Parameters);

		var filteredSource = source.Where(filterNullExpression);

		if (startDate == null)
			startDate = filteredSource.OrderBy(keySelector).Select(keySelector).FirstOrDefault() ?? DateTime.Now.AddYears(-1);
		if (endDate == null)
			endDate = DateTime.Now;
	}

	public abstract class ReportRow
	{
		public ReportDate Date { get; set; }
	}

	public enum ReportDateType { Day, Week, Month, Year }

	public class ReportRowComparer<TReportRow> : IEqualityComparer<TReportRow> where TReportRow : ReportRow
	{
		public bool Equals(TReportRow x, TReportRow y)
		{
			return x.Date.Day == y.Date.Day
				&& x.Date.Week == y.Date.Week
				&& x.Date.Month == y.Date.Month
				&& x.Date.Year == y.Date.Year;
		}

		public int GetHashCode(TReportRow obj)
		{
			return (obj.Date.Year.ToString() + obj.Date.Month.ToString() + obj.Date.Week.ToString() + obj.Date.Day.ToString()).GetHashCode();
		}
	}

	public class ReportDate : IComparable<ReportDate>
	{
		public int Year { get; set; }
		public int Month { get; set; }
		public int Week { get; set; }
		public int Day { get; set; }

		public ReportDateType DateType {
			get
			{
				return Month == 0 ? ReportDateType.Year : Week == 0 ? ReportDateType.Month : Day == 0 ? ReportDateType.Week : ReportDateType.Day;
			}
		}

		public override string ToString()
		{
			return this.DateType == ReportDateType.Day ? (this.Month + "/" + this.Day + "/" + this.Year.ToString().Substring(2, 2)) :
				this.DateType == ReportDateType.Week ? ("Week " + this.Week) :
				this.DateType == ReportDateType.Month ? (this.Month + "/" + this.Year) :
				this.Year.ToString();
		}

		public int CompareTo(ReportDate other)
		{
			var comparison = this.Year.CompareTo(other.Year);
			if (comparison == 0)
				comparison = this.Month.CompareTo(other.Month);
			if (comparison == 0)
				comparison = this.Week.CompareTo(other.Week);
			if (comparison == 0)
				comparison = this.Day.CompareTo(other.Day);
			return comparison;
		}
	}
}