nHibernate Distinct query

Question

Code

IList VendorList;

                VendorList = session
                    .CreateCriteria<VendorLookup>()
                    .Add(Expression.Eq("NetworkRunId", networkRunId))
                    .Add(Expression.Not(Expression.Eq("VendorName", " ")))  
                    .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())
                    .AddOrder(new Order("VendorName", true))
                    .List<IVendorLookup>().Distinct<IVendorLookup>().ToList();

Generated Query Please help me out

SELECT   this_.Sp    as HCO1_25_0_,
         this_.NID  as Network2_25_0_,
         this_.Vname as HCO3_25_0_
FROM     HCO_V_Lookup this_
WHERE    this_.NID = 5 /* @p0 */
         and not (this_.VNAME = ' ' /* @p1 */)
ORDER BY this_.VNAME asc

Answer 1

I presume you're wondering why the DISTINCT doesn't appear in the SQL. It's because it's applied in your criteria query as a result transformer - the SQL query results are filtered to make them distinct after the query has been run.

Answer 2

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