Take the 2-minute tour ×

I'm trying to get distinct results using the Criteria API in NHibernate. I know this is possible using HQL, but I would prefer to do this using the Criteria API, because the rest of my app is written using only this method. I found this forum post, but haven't been able to get it to work. Is there a way with the criteria API to get distinct result sets?

Edit: In doing this, I also wanted to exclude the Primary Key column, which is also an identity, and get the remaining distinct records. Is there a way to do this? As it is, the distinct records are returning duplicates because the primary key is unique for each row, but all other fields are the same.

share|improve this question

5 Answers 5

up vote 20 down vote accepted

Cannot see the forum post at this moment (broken link?), so maybe this is not the answer, but you can add a DistinctRootEntityResultTransformer:

    .SetResultTransformer(new DistinctEntityRootTransformer())
share|improve this answer
Ok, I've never done this before, so 1 quick question. I have a primary key, which is an identity field. How can I exclude that field from the result set so that the distinct results are really distinct? I can clarify if that doesn't make sense. –  Mark Struzinski Nov 25 '08 at 19:20
I don't know if that's possible. You could create a DTO with the other fields and query against that DTO, but it'd require using hql. –  Juanma Nov 26 '08 at 10:40
In newer versions of NHibernate, you can use Transformers.DistinctRootEntity (it's a static property) –  Juanma Feb 19 '09 at 13:12
It's very important to understand that this results in a client-side transformation of the data. Unlike the HQL equivalent, all data - whether distinct or not - is returned to NHibernate. NHibernate then does a client-side transformation of that data to yield only the distinct entities. This is potentially much less efficient than the HQL, or using a projection. –  Kent Boogaart Sep 20 '09 at 16:46
@Aydan boile's answer is better - it performs a distinct query. –  Yonatan Karni Aug 11 '10 at 7:21

To perform a distinct query you can set the projection on the criteria to Projections.Distinct. You then include the columns that you wish to return. The result is then turned back into an strongly-typed object by setting the result transformer to AliasToBeanResultTransformer - passing in the type that the result should be transformed into. In this example I am using the same type as the entity itself but you could create another class specifically for this query.

ICriteria criteria = session.CreateCriteria(typeof(Person));
        .Add(Projections.Alias(Projections.Property("FirstName"), "FirstName"))
        .Add(Projections.Alias(Projections.Property("LastName"), "LastName"))));

    new NHibernate.Transform.AliasToBeanResultTransformer(typeof(Person)));

IList people = criteria.List();

This creates SQL similar to (in SQL Server at least):

SELECT DISTINCT FirstName, LastName from Person

Please be aware that only the properties that you specify in your projection will be populated in the result.

The advantage of this method is that the filtering is performed in the database rather than returning all results to your application and then doing the filtering - which is the behaviour of DistinctRootEntityTransformer.

share|improve this answer
Do you know how I could have the child object graph hydrated? I can get it to select all colums of the root, including foreign key columns, but do not know how to get it to load them after query returns. –  longday Aug 10 '11 at 16:04
@longday I don't think it is possible to have NHibernate do this because by doing a distinct projection you are no longer dealing with actual entities (even if you are reusing the same class). What you can do is include columns from several tables in your projection and create a separate class to use in the result that has all of the properties you are interested in. –  Aidan Boyle Aug 18 '11 at 4:36
This does not work if you've defined an Alias in the criteria, because it will then start looking the the alias among the class members, which it of course cannot find. –  Fedor Steeman Sep 16 '11 at 11:48
thanks a million...good one –  dolphy Nov 14 '11 at 16:25
is there any way to find the count of the results after projection?! It always counts based on the database rows, with all the column combinations! –  Ruba Nov 6 '13 at 12:27

For what it is worth, NHibernate: Optimising Queries with Projections helped me with basically this same issue.

share|improve this answer
Note: The link is no longer found. –  Thierry Jul 23 at 18:20
@Thierry Thanks. I updated the link since the author moved their URL. –  Jon Adams Jul 23 at 18:41

We are using the most modern and powerful and impressively tiny means of all to handle this...read on only if you're prepared for the awesome...and it has NOTHING to do with criteria...


So, if you've come here hoping for a way to do this that has you avoiding messing with Criteria even though you though you would totally have to go in that direction just to add 'DISTINCT' into your SQL...search no further

share|improve this answer

I also ran into the problem of the non-distinct number of items (I use a fetch="join" in my mapping file). I used Linq To Nhibernate to solve the problem, which is used in the following way:

       var suppliers = (from supplier in session.Linq<Supplier>()
                        from product in supplier.Products
                        where product.Category.Name == produtCategoryName
                        select supplier).ToList().Distinct();
share|improve this answer
Incorrect. The Distinct in this case is being done client side. You are calling ToList(), which returns all the results. Look into NHProf. –  Mike Cole Jul 15 '10 at 17:53
SetResultTransformer(new DistinctEntityRootTransformer()) in answer stackoverflow.com/questions/318157/… does almost the same as .Distinct(). They both are client side transformations. And they are almost equivalent. –  Alexander K. Jan 25 '11 at 12:21

Your Answer


