Get Distinct result set from NHibernate using Criteria API?

by

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));
criteria.SetProjection(
    Projections.Distinct(Projections.ProjectionList()
        .Add(Projections.Alias(Projections.Property("FirstName"), "FirstName"))
        .Add(Projections.Alias(Projections.Property("LastName"), "LastName"))));

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

IList<Person> people = criteria.List<Person>();

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.

More Related Contents:

  1. How to Eager Load Associations without duplication in NHibernate?
  2. Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?
  3. What are the First and Second Level caches in (N)Hibernate?
  4. Inverse Attribute in NHibernate
  5. Fighting cartesian product (x-join) when using NHibernate 3.0.0
  6. What’s the difference between session.Merge and session.SaveOrUpdate?
  7. How do you map an enum as an int value with fluent NHibernate?
  8. JSON.NET and nHibernate Lazy Loading of Collections
  9. List vs Set vs Bag in NHibernate
  10. When to use inverse=false on NHibernate / Hibernate OneToMany relationships?
  11. nHibernate mapping to custom types
  12. IndexOutOfRangeException Deep in the bowels of NHibernate
  13. Is this the right way to eager load child collections in NHibernate
  14. Unable to cast object of type NHibernate.Collection.Generic.PersistentGenericBag to List
  15. How to create OR statements for NHibernate?
  16. Binary Blob truncated to 8000 bytes – SQL Server 2008 / varbinary(max)
  17. How to save a child with assigned id in nhibernate
  18. NHibernate configuration for uni-directional one-to-many relation
  19. Generate XML mappings from fluent Nhibernate
  20. What is the difference between JoinQueryOver and JoinAlias?
  21. How do I view the SQL that is generated by nHibernate?
  22. DateTime precision in NHibernate and support for DateTime2 in NHibernate SchemeExport
  23. Why doesn’t Entity Framework support ODBC?
  24. NHibernate: difference between Interceptor and Listener
  25. What exactly is “persistence ignorance”?
  26. How do add NOLOCK with nHibernate?
  27. Syntax to define a NHibernate Filter with Fluent Nhibernate?
  28. NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  29. Mapping collection of strings with NHibernate
  30. How to partially project a child object with many fields in nHibernate

Leave a Comment