Get Distinct result set from NHibernate using Criteria API?

Question

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.

Answer 1

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

session.CreateCriteria(typeof(Product)
    .Add(...)
    .SetResultTransformer(new DistinctEntityRootTransformer())

Answer 2

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 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.

Answer 3

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

Answer 4

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

Answer 5

Here is the 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);

    }