group by month and year parts using queryover

Go To


I have a model, that contains a DateTime field and a Price field. I want to get a list that gives me an aggregation of month/year - price so for the data:

  1. 15/01/2012 200
  2. 16/01/2012 200
  3. 15/02/2012 300
  4. 16/02/2012 300
  5. 15/03/2012 400
  6. 16/03/2012 400

I will get:

  1. 01/2012 400
  2. 02/2012 600
  3. 03/2012 800

So far I didn't find a way to achive this using QueryOver. I keep getting "could not resolve property" when i try to look at the Month/Year parts of the date.

Those questions:

Group posts by year, then by month

Group/Sort by Date Year/Month, Partial Select with NHibernate (Projection? Transformation?)

got answered using LINQ - not what I'm looking for.

Any ideas? Thanks

(using NHibernate 3.2)

2012-04-04 16:50
by Nadav Ben-Gal


QueryOver is not a good choice because grouping will need to be done using a SQL expression. For this type of query, QueryOver is simply a pointless abstraction that adds no value.

If you must do it using QueryOver you could do something like:

   .SelectList(list => list
      .SelectSum(x => x.Price)
2012-04-05 00:33
by Phil Degenhardt
In Criteria you will have to use the same projection... that's what i hoped to get around, so what do you recommend if not the QueryOver - Nadav Ben-Gal 2012-04-05 11:28
Probably just use ICriteria. Or you could map a SQL query similar to the idea explained here: If you want to keep magic strings and database specific code in a single location then the latter would be the way to go - Phil Degenhardt 2012-04-05 20:57