CODEWITHSUNDEEP
nhibernatelinq-to-nhibernate
Troels Thomsen
Troels Thomsen

Reputation: 11617

Group by date with LINQ to NHibernate

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.

Upvotes: 4

Views: 5195

Answers (2)

viggity
viggity

Reputation: 15237

Well, this question was posted 7 years ago... so I think a lot has changed since then.

Long story short - you can group on date the way that would feel natural with the latest version (3.3 of NH) for sure. I'm not sure what version of NH introduced this feature. @Mike had a solution for the old versions, but pulling all that data over the wire is not performant at all. You can group on SomeDate.Date just fine now with Linq to NH.

var results = _session.Query<Appointment>()
    .Where(ts => ts.StartTime > model.StartDate && ts.StartTime < endDateTime)
    .GroupBy(ts => new { ts.Account, ts.Date })
    .Select(g => new { g.Key.Account, g.Key.Date, AppointmentCount = g.Count() });

Upvotes: 1

Mike
Mike

Reputation: 217

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

Upvotes: 0

Related Questions