How to set max return result for many-to-one

Question

Hi,

I have a Product Class which has a one to many relationship to a Price class. So a product can have multiple prices.

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 writ the following 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);

Instead of getting 10 Product on the list, I'm getting less than that (i.e. 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 without increasing SetMaxResults()? I need to use pagenumber as some sort of indexing.

Answer 1

That would be up to the SQL to decide, depending on what happens in the methods that gets the list you need to change the SQL so that it behaves as you like.

But being Distinct, you shouldnt get any duplicates.

Answer 2

Would the order of the statements make a difference? It looks like it's setting the maximum count early, and weeding out duplicates at the end, which applied in that order could end up with less than what you limited it to, consistent with what you described happening.

I would think you would need to effectively get all of the results, then apply the restriction (and possibly a sort?) and weed out the duplicates, and then finally apply your paging or count limit to those to get the first 10, next 10, and so on. So reordering the statements to reflect this logical order might help fix your bug.

Answer 3

It seems your duplicates problem stems from the fact that you are joining two tables and so you can get the same product as many times as you have prices for it.

How about adding 2 extra columns to your product table:

MinimumPrice (numeric(18,2)
MaximumPrice (numeric(18,2)

Whenever your system amends pricing for a product you update these two fields on the product. Now you can write a SQL query like the following:

SELECT TOP 10 * FROM Product
WHERE MinimumPrice > 2.0

And you will not have duplicate products.

Answer 4

I ve got the same issue, i have a long list of criterias which they come together on selections from UI. At the end i have used this and that did the same thing just like yours.

query
   .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer());

Then i have tried this one and that gave me an error. saying "Unable to perform find[SQL: SQL not available]"

query.SetProjection(Projections.Distinct(Projections.Property("Id")));

(But without projection everything went fine, no errors but duplicate results)

I hope someone helps

Thanks