Question

Is there a simple way to use GroupBy of dates with arbitrary truncation?

That would be the LINQ equivalent to

SELECT dateadd(DAY, datediff(DAY,0, CreationDate), 0) as CreationDateDay, COUNT(*) as C
FROM [DonatelloDomain_EDF].[dbo].[SPB.Model.Invoice_View]
GROUP BY dateadd(DAY, datediff(DAY,0, CreationDate), 0)

Interesting precisions would be Year, Month, Day, Hour, Minute, Second, ...

Accepted Answer

Hello olorin,

Unfortunately creating DateTime's in LINQ currently unsupported due to internal limitations of LINQ pipeline.

However, you can utilize our LINQ extensibility features to write queries in exactly that way:

public static class SqlDatePart
{
  public const string Year = "Year";
  public const string Month = "Month";
  public const string Day = "Day";
  public const string Hour = "Hour";
  public const string Minute = "Minute";
  public const string Second = "Second";
}

public static class SqlDateExtensions
{
  public static DateTime Truncate(this DateTime d, string part)
  {
    switch (part) {
      case SqlDatePart.Year:
        return new DateTime(d.Year, 1, 1);
      case SqlDatePart.Month:
        return new DateTime(d.Year, d.Month, 1);
      case SqlDatePart.Day:
        return new DateTime(d.Year, d.Month, d.Day);
      case SqlDatePart.Hour:
        return new DateTime(d.Year, d.Month, d.Day, d.Hour, 0, 0);
      case SqlDatePart.Minute:
        return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, 0);
      case SqlDatePart.Second:
        return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second);
      default:
        throw new ArgumentOutOfRangeException("part");
    }
  }
}

[CompilerContainer(typeof (SqlExpression))]
public static class SqlDateCompilers
{
  [Compiler(typeof (SqlDateExtensions), "Truncate", TargetKind.Method | TargetKind.Static)]
  public static SqlExpression Truncate(SqlExpression d, SqlExpression part)
  {
    var partName = ((SqlLiteral<string>) part).Value;
    var difference = SqlDml.FunctionCall("DATEDIFF", SqlDml.Native(partName), 0, d);
    return SqlDml.FunctionCall("DATEADD", SqlDml.Native(partName), difference, 0);
  }
}

After SqlDateCompilers is registered in Domain, you can use Truncate extension method everywhere:

session.Query.All<MyEntity>().GroupBy(p => p.DateTime.Truncate(SqlDatePart.Month))

Subscription:

Related questions