How to retrieve unique entities through NHibernate Criteria API?

Question

Hi!

My entities look something like that (simplified):

public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }

    public IList<Department> Departments { get; set; }
}

public class Department
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

I'm querying the database through criteria api for all persons that have a department with a certain name that should match a like-pattern.

It happens that a person contains two or more departments whose names contain the same character sequence which is used by the query. Therefore the same person is returned multiple times. To surpress this, I know that I can use criteria.SetResultTransformer(Transformers.DistinctRootEntity); but this works only as long as the result is not paged.

When I'm paging the result I don't only need to get the first page but I also need to know how many entities there are in total. Unfortunately the result transformer does not work when calling criteria.SetProjection(Projections.RowCount()) as there is no result to be transformed.

Can I somehow avoid retrieving the whole list of person with the result transformer and then manually taking the right part out of the collection?

Best Regards
Oliver Hanappi

Answer 1

You need to include distinct in your sql request. Some information you can find here. Second answer mostly