How to do a SELECT DISTINCT equivalent for a page filter in NHibernate?

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.

Am I right to think all of these methods filter to DISTINCT after bringing back the data to the client? — BuddyJoe, Aug 16 '10 at 13:26
Yes. Have a look at using a Projection. See http://stackoverflow.com/a/385079/63295 — Castrohenge, Aug 19 '13 at 15:17

score 1 · Answer 1 · edited Mar 17 '15 at 05:10

1

See nhibernate.info/doc/howto/various/get-unique-results-from-joined-queries.html

edited Mar 17 '15 at 05:10

hazzik

13,019
9
47
86

answered Feb 18 '09 at 00:59

Mauricio Scheffer

98,863
23
192
275

score 0 · Answer 2 · answered Oct 14 '09 at 18:24

0

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);

}

answered Oct 14 '09 at 18:24

bluwater2001

7,829
5
24
21

Does this .Distinct() call require the NH LINQ provider? – BuddyJoe Oct 14 '09 at 18:51
No. It's being applied after the data has been returned. – Castrohenge Aug 19 '13 at 15:15

score -2 · Answer 3 · answered Feb 17 '09 at 22:31

-2

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

answered Feb 17 '09 at 22:31

Alex Reitbort

13,504
1
40
61

1

be careful, result transformers actually filter the results *on the client*. Use a projection instead. – Mauricio Scheffer Feb 18 '09 at 00:57
@Bruno It's criterion for "SELECT Title FROM Employees ORDER BY Title". I am not near VS and I don't' remember the exact syntax. – Alex Reitbort Feb 18 '09 at 06:39
mausch, when you use a projection what kind of object is being returned? An anonymous-typed one? similar to LINQ? – BuddyJoe Feb 18 '09 at 22:02

How to do a SELECT DISTINCT equivalent for a page filter in NHibernate?

3 Answers3

Linked