Nhibernate get rows grouped by column

Question

In short i want the following grouping functionality: http://demos.telerik.com/aspnet-mvc/grid/index However, I cannot seem to figure out how to enable my back end to retrieve this data dynamically.

I have the following line:

 query.UnderlyingCriteria.SetProjection(Projections.GroupProperty("PropertyNumber"));

Where query is of type IQueryOver.

However, when I use

var statistics = query.List<T>();

I get

"The value \"000000\" is not of type \"SDS" and cannot be used in this generic collection.\r\nParameter name: value"}

000000 is a default property number, and SDS is the object I'm attempting to group. It is obvious to me that what I've written is attempting to cast strings back as the value.

Interestingly the following returns the exact count of rows in the table.

var rowCount = query.RowCount();

My question then, is how do I actually return SDS in a grouped manner, if the projection cannot do what I want?

score 2 · Accepted Answer · answered Sep 23 '14 at 15:34

There are two issues in general:

once we've used projection, any other property which we want to get must be projected as well.
if there is a projection and we need to get a list of entities, we need result transformer

Having that we can get the list like this:

// the As() is essential for result transformer
query
    .UnderlyingCriteria
    .SetProjection(Projections.GroupProperty("PropertyNumber")
                              .As("PropertyNumber"));
// set Transformer
query.TransformUsing(Transformers.AliasToBean<T>());

// the list of T, but only PropertyNumber is filled by NHibernate
var list = query.List<T>();

or this to get more properties filled

query
    .UnderlyingCriteria
    .SetProjection(
      Projections.ProjectionList()
        .Add(Projections.GroupProperty("PropertyNumber").As("PropertyNumber"))
        .Add(Projections.Count("ID").As("Count"))
        ....
    )
    ;

// set Transformer to some DTO
query.TransformUsing(Transformers.AliasToBean<U>());

// the list of DTO, with more than PropertyNumber filled
var list = query.List<U>(); // generic U representing DTO

Finally, the QueryOver API has its own way how to declare projections

16.6. Projections

Okay, this solves one of my problems. AliasToBean helped a ton. Also thanks for the QueryOver projection pointer. However, I don't want simply aggregates. Example: CountId doesn't help me, I need just ID for each row. My telerik link above shows I'm trying to reach a point where I can select a column and get all rows for that grouping still. — Seth, Sep 23 '14 at 15:51
I am afraid that I can hardly assist with telerik stuff. Just wanted to show you how we should use projections in NHibernate. What else you will do with that API (to make telerik happy) is in your hands. Power of Projections is really great. If you would need even more powerful Transformer, check [this answer](http://stackoverflow.com/a/24248309/1679310)... wish to help more... — Radim Köhler, Sep 23 '14 at 16:23
Thanks Radim, I would like to rephrase my question. In the telerik example, it shows data for each individual row, and grouping as well. I'm trying to figure out how I can acquire those datapoints in nhibernate, your example is very helpful, but with the objects I seem to still only be able to get aggregated data, whereas i still want the individual rows. — Seth, Sep 23 '14 at 17:56

Nhibernate get rows grouped by column

1 Answers1