CODEWITHSUNDEEP
nhibernatefluent-nhibernatenhibernate-criteriarowcount
Nicke Manarin
Nicke Manarin

Reputation: 3358

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.

Upvotes: 0

Views: 427

Answers (2)

Nicke Manarin
Nicke Manarin

Reputation: 3358

Finally, this worked! 

var crit = StatelessSession.CreateCriteria<PeopleProduct>()
    .SetProjection(Projections.ProjectionList()
            .Add(Projections.Count(Projections.Distinct(
                    Projections.Property<PeopleProduct>(p => p.PeopleId)))));

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

EDIT: There's a better answer (and less verbose)...

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

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.

Upvotes: 1

Related Questions