nhibernatefluent-nhibernatenhibernate-criteriarowcount

Get row count from unique ID's


Let's suppose that I have this simple n-n table (between people and product):

//id    people_id    product_id
  1         1            1  
  2         1            3  
  3         1            5
  4         2            1

And this class (already mapped):

public class PeopleProduct
{
    public virtual int TableId { get; set; } //Mapped to id
    public virtual int PeopleId { get; set; } //Mapped to people_id
    public virtual Product Product { get; set; } //Mapped to product_id
}

As you can see, there's two people, the first one with 3 products and the second one with just 1.

How can I get the count of unique people_id using CreateCriteria?

I'm currently trying to using this one:

var crit = StatelessSession.CreateCriteria<PeopleProduct>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Count<PeopleProduct>(c => c.PeopleId))
        .Add(Projections.Group<PeopleProduct>(g => g.PeopleId)));

var count = Convert.ToInt64(crit.UniqueResult());

But it always returns a list with an array with [count, id]:

[3, 1] and [2, 1]

This is not the best result, since this table could return thousands of people_id.


Solution

  • Use CountDistinct.

    var numberOfDistinctPeople = StatelessSession.CreateCriteria<PeopleProduct>()
        .SetProjection(Projections.CountDistinct<PeopleProduct>(c => c.PeopleId))
        .UniqueResult();
    

    By the way, did you know that you can use QueryOver, which is the same with a better syntax? HQL / Linq is even more powerful and better to use for static queries like this one.