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, ...

asked Apr 12 '12 at 04:38

olorin's gravatar image

olorin
356818489

edited Apr 12 '12 at 04:38


One 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))

answered Apr 19 '12 at 08:11

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov
179325

edited Apr 19 '12 at 08:12

This is a very powerful workaround!

(Apr 21 '12 at 10:08) Malisa Malisa's gravatar image

Wow! This really helps! Besides it is a great example on how to write SQL compiler extensions.

(Apr 24 '12 at 04:29) olorin olorin's gravatar image

I have another (minor) question: Is it possible to use an enum for SqlDatePart? It seems to block compilation because the enum is translated to Int32 during the query translation.

(Apr 24 '12 at 05:22) olorin olorin's gravatar image

You are right. Enums are converted to integers at early stages of translation. That's why string constants have to be used.

(Apr 24 '12 at 05:25) Denis Krjuchkov Denis%20Krjuchkov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA