Here is the problem I am having. I have a database with over 100 records in it. I am paging through the data to get 9 results at a time. When I added a check to see if items are active, it caused the results to start doubling up.

A little background: "Product" is the actual product line "ProductSkus" are the actual products that exist in the product line When there is more then 1 ProductSku within Product, it causes a duplicate entry to be returned.

See the NHibernate Query below:

result = this.Session.CreateCriteria<Model.Product>()                 
                .Add(Expression.Eq("IsActive", true))
                .AddOrder(new Order("Name", true))
                .SetFirstResult(indexNumber).SetMaxResults(maxNumber)

                // This part of the query duplicates the products
                .CreateAlias("ProductSkus", "ProdSkus", JoinType.InnerJoin)
                .Add(Expression.Eq("ProdSkus.IsActive", true))

                .CreateAlias("ProductToSubcategory", "ProdToSubcat")
                .CreateAlias("ProdToSubcat.ProductSubcategory", "ProdSubcat")
                .Add(Expression.Eq("ProdSubcat.ID", subCatId))

                // This part takes out the duplicate products - Removes too many items...
                // Turns out that with .SetFirstResult(indexNumber).SetMaxResults(maxNumber)
                // it gets 9 records back then the duplicates are removed.  
                // Example: 
                //      Total Records over 100
                //      Max = 9
                //      4 Duplicates removed
                //      Yields 5 records when there should be 9
                // Why???  This line is ran in NHibernate on the data after it has been extracted from the SQL server.
                .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())

                .List<Model.Product>();

I added the DistinctRootEntityResultTransformer to clean up the duplicates. The problem is that it pulls 9 records back that contains duplicates. DistinctRootEntityResultTransformer then cleans up the duplicates in the 9 records. I am basically needing a distinct statement to be ran on the SQL server to begin with.

However, distinct on SQL is not going to work since NHibernate by default wants to add every field from every table in the select part of the statement. I am only using the fields that belong to the root table to begin with (Model.Product). If I can tell NHibernate to not add the fields to the joined tables into the select part of the statement along with adding Distinct, it would work.

I use NHibernare Profiler to see the actual query:

SELECT   top 9 this_.ID                          as ID351_3_,
           this_.Name                        as Name351_3_,
           this_.Description                 as Descript3_351_3_,
           this_.IsActive                    as IsActive351_3_,
           this_.ManufacturerID              as Manufact5_351_3_,
           prodskus1_.ID                     as ID373_0_,
           prodskus1_.Description            as Descript2_373_0_,
           prodskus1_.PartNumber             as PartNumber373_0_,
           prodskus1_.Price                  as Price373_0_,
           prodskus1_.IsKit                  as IsKit373_0_,
           prodskus1_.IsActive               as IsActive373_0_,
           prodskus1_.IsFeaturedProduct      as IsFeatur7_373_0_,
           prodskus1_.DateAdded              as DateAdded373_0_,
           prodskus1_.Weight                 as Weight373_0_,
           prodskus1_.TimesViewed            as TimesVi10_373_0_,
           prodskus1_.TimesOrdered           as TimesOr11_373_0_,
           prodskus1_.ProductID              as ProductID373_0_,
           prodskus1_.OverSizedBoxID         as OverSiz13_373_0_,
           prodtosubc2_.ID                   as ID362_1_,
           prodtosubc2_.MasterSubcategory    as MasterSu2_362_1_,
           prodtosubc2_.ProductID            as ProductID362_1_,
           prodtosubc2_.ProductSubcategoryID as ProductS4_362_1_,
           prodsubcat3_.ID                   as ID352_2_,
           prodsubcat3_.Name                 as Name352_2_,
           prodsubcat3_.ProductCategoryID    as ProductC3_352_2_,
           prodsubcat3_.ImageID              as ImageID352_2_,
           prodsubcat3_.TriggerShow          as TriggerS5_352_2_
 FROM     Product this_
     inner join ProductSku prodskus1_
       on this_.ID = prodskus1_.ProductID
          and (prodskus1_.IsActive = 1)
     inner join ProductToSubcategory prodtosubc2_
       on this_.ID = prodtosubc2_.ProductID
     inner join ProductSubcategory prodsubcat3_
       on prodtosubc2_.ProductSubcategoryID = prodsubcat3_.ID
 WHERE    this_.IsActive = 1 /* @p0 */
     and prodskus1_.IsActive = 1 /* @p1 */
     and prodsubcat3_.ID = 3 /* @p2 */
 ORDER BY this_.Name asc

If I hand modify the query and run it directly on the SQL server I get the result set I want (I removed all the extra fields in the select section and added DISTINCT):

SELECT DISTINCT  top 9 this_.ID              as ID351_3_,
           this_.Name                        as Name351_3_,
           this_.Description                 as Descript3_351_3_,
           this_.IsActive                    as IsActive351_3_,
           this_.ManufacturerID              as Manufact5_351_3_,
 FROM     Product this_
     inner join ProductSku prodskus1_
       on this_.ID = prodskus1_.ProductID
          and (prodskus1_.IsActive = 1)
     inner join ProductToSubcategory prodtosubc2_
       on this_.ID = prodtosubc2_.ProductID
     inner join ProductSubcategory prodsubcat3_
       on prodtosubc2_.ProductSubcategoryID = prodsubcat3_.ID
 WHERE    this_.IsActive = 1 /* @p0 */
     and prodskus1_.IsActive = 1 /* @p1 */
     and prodsubcat3_.ID = 3 /* @p2 */
 ORDER BY this_.Name asc

The big question I now must ask is...

What must I change in the NHibernate Query to ultimately get the exact same result?

Thanks in advance.