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