How to retrieve unique entities through NHibernate Criteria API?

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 开发者_C百科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

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