NHibernate criteria to count unique visits

Question

I have two tables, for this example I'll them Vists and Cities.

Visits

• UserAccountID (Mapped to the UserAccount property)
• CityID (Mapped to the City property)
• VisitDate

Cities

• CityID
• Name

I want to write a criteria which will return the number of different cities a particular user has visited.

So far I have:

       return this.Framework.GetSession().CreateCriteria<Visits>()
            .Add(Restrictions.Eq("UserAccount", user))
            .SetProjection(Projections.Count(Projections.Id()))
            .UniqueResult<int>();

However when I have a user who has visited:

• London
• Manchester
• Leeds
• London

This query will return 4, I'm after the number of different cities visited (in this case 3).

How can I do this?

Answer 1

Have you tried using Projections.GroupProperty("CityID") (official documentation) ? It should be able to achieve your desired results.

This question might also prove helpful.