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.
