Group by date with LINQ to NHibernate

Question

I am facing an issue with an unsupported feature in the LINQ implementation of NHibernate (it's firing a QueryException: "could not resolve property: CreatedOn.Date of: Example.Comment").

Given the entity below, I would like to extract statistics by grouping the comments per day and sum up the amount of comments for each day (naturally it has additional properties and correct mapping, written in Fluent NHibernate):

public class Comment
{
 public virtual DateTime CreatedOn
 {
  get;
  set;
 }
}

My LINQ query looks like below (the query works fine if executed against a LINQ to SQL context).

var commentsPerDay = (from c in session.Linq<Comment>()
       group c by new { c.CreatedOn.Date } into g
       select new
       {
        Date = g.Key.Date,
        Count = g.Count()
       };

The proper solution would be to contribute to the project and fix this issue.

On the other hand, I would love to find out if (LINQ to) NHibernate has an extensibility point, where I could inject this behaviour. I know I could probably create a custom type and mapping a second property to the date part of the creation time, but I'm guessing this wouldn't translate to a query, which would make it too slow.

I have limited access to rewriting the queries, as they still has to work with LINQ to SQL, so I can't use HQL.

Answer 1

I've only found one solution for it and that is to do the following:

Select all the results from nhibernate, and then force it to retrieve the results. Then you can perform a group by on the results.

It would look something like :

var comments = session.Linq<Comment>().Select(s=> new { s.CreatedOn } ).ToList(); //get all the comments into the results
var commentsPerDay = comments.GroupBy(c=>c.CreatedOn.Date).Select(foo => new {Date = g.Key, Count = g.Count());  //group by the results

Sorry, I like lamda, but I think you'll get the idea. You'll probably want to limit the date range to query across so that you don't pull back a ton of results.

(I was previously logged in with the wrong user to google. and posted under that user.)