NHibernate - SELECT DISTINCT equivalent for a page filter - Northwind

Question

Lets say you are working in SQL 2005 with a copy of Northwind database installed. Your working on an ASP.NET application with an Employees "browse" page. At the top of the page you have a "Title" filter where you would like to display these 5 choices in a dropdown:

[ALL]
Vice President, Sales
Sales Representative
Sales Manager
Inside Sales Coordinator

In T-SQL you would use something like the statement below to get your list.

SELECT DISTINCT Title FROM Employees ORDER BY Title

What is the best way of doing this in NHibernate? Assume that the initial database design is somewhat out of your control (just like Northwind)... Meaning that you won't be creating a Titles or Positions table for normalization.

Thanks.

Answer 1

criterion =  ... // SELECT Title FROM Employees ORDER BY Title
criterion.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())

Answer 2

See http://nhforge.org/wikis/howtonh/get-unique-results-from-joined-queries.aspx

Answer 3

There is another similar post that already has an accepted answer, probably check that out first:

http://stackoverflow.com/questions/318157/get-distinct-result-set-from-nhibernate-using-criteria-api

Answer 4

ANSWER: I have 29 records in the table with 4 records repeated twice. I want only Distinct results. So, the total number of records should be 25.

below is test method

[TestMethod]
public void UserApplicationsTest()
{
    int usercount = 25;
    string query = "select from User u left outer join fetch u.ApplicationRequests ar";

    ISession session = NHibernateSessionManager.GetSession();


    IList<User> users = session.CreateQuery
       (
       query
       )
       .List<User>()
       .Distinct<User>().ToList();


    Assert.AreEqual(usercount, users.Count);

}