nhibernate take skip not taking as much as it should

Question

I have this code

var items = query.Skip(0).Take(25).List();

what I want is that I get 25 entrance from my list(list got 402 entrance). I never got 25 entrance after this call. Does someone know why?

Answer 1

A .Take() and .Skip() is at DB side (e.g. on SQL Server - Implement paging (skip / take) functionality with this query) converted into something like this

SELECT col1, col2, ...
 FROM ...
 WHERE ... 
 ORDER BY 
OFFSET      0 ROWS       -- skip  0 rows
FETCH NEXT 25 ROWS ONLY; -- take 25 rows

And that would always work if our query is about one TABLE (no JOIN). I.e. that many rows will result in that many C# items in the result list

But if a query would use JOIN, joining some collection, we would still get 25 rows on DB side..

parent_id, child_id
        1,       10      
        1,       11      
        2,       12      
        2,       13      
        ...
        -- 25 rows

But these would result in less root entities (see parent 1 and 2 has 4 rows). These will, during transformation, be simply converted in just few parents with very uncertain amount of items in collection

My suggestion would be:

do not join collection when paging is needed. NEVER. Always page on joins related to star schema (many-to-one)