Hi,
I have a Product Class which has a one to many relationship to a Price class. So a product can have multiple prices.
As an example scenario, I need to query the db to get me 10 products which have Price.amount < $2. In this case its to populate a UI with 10 items in a page. so i wrote the following code:
Code:
ICriteria criteria = session.CreateCriteria(typeof(Product));
criteria.SetFirstResult(pageNumber);
criteria.SetMaxResults(numberOfItemInPage);
criteria = criteria.CreateCriteria("PriceCollection");
criteria.Add(Restrictions.Le("Amount", new Decimal(2)));
criteria.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
Now instead of getting 10 Product inthe list, I'm getting less than that (e.g. 5). The reason being SetMaxResults(10) return me 10 Products but with duplicates. The duplicates are then removed by SetResultTransformer(DistinctRootEntity).
Can anyone tell me any way for me to get 10 unique Products and maintain some paging. In reality the total number of products is >500k.
Login
FAQ
Search