CODEWITHSUNDEEP
c#sqlnhibernate
Seth
Seth

Reputation: 984

Nhibernate get rows grouped by column

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?

Upvotes: 1

Views: 313

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

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

Upvotes: 2

Related Questions