2

We have a query we're attempting to write for paging, where we want to use Fetch to eager load a collection for an object, then return a paged list of that object. The query below does not work because when we look in the profiler, it skips the Skip and Take settings, and just returns the list of objects (eagerly loaded):

var result = _repostitory.All<MediaFile>()
            .Skip(10)
            .Take(10)
            .Fetch(mf => mf.Tags);

Everything compiles and executes but, the skip and take parts are ignored and all the data is being brought back. Is it possible to use Fetch in conjunction with Skip and Take for paging?

Vadim Rybak
  • 1,677
  • 20
  • 25

1 Answers1

2

Have you tried calling fetch before skip and take 

var result = _repostitory.All<MediaFile>()
.Fetch(mf => mf.Tags)            
.Skip(10)
.Take(10);

From what I remember NH cannot handle join eager loading with limits on the query (it has to do with SQL and not NH). It might work with sub select though.

**UPDATE

Consider this example

TableA
-------------
Id|Value
-------------
1 |Value1
2 |Value2
3 |Value3

TableB
-------------------------
ChildId|ParentId|ChildValue
-------------------------
1      |       1|Value1
2      |       1|Value2
3      |       2|Value3
4      |       2|Value4
5      |       3|Value5
6      |       3|ValueA
7      |       3|ValueA
8      |       3|ValueA



SELECT * FROM TableA LEFT JOIN TableB ON TableA.Id = TableB.ParentId

Result
--------------------------------------
Id|Value |ChildId|ParentId|ChildValue
--------------------------------------
1 |Value1|1      |       1|Value1
1 |Value1|2      |       1|Value2
2 |Value1|3      |       2|Value3
2 |Value1|4      |       2|Value4
3 |Value1|5      |       3|Value5
3 |Value1|6      |       3|ValueA
3 |Value1|7      |       3|ValueA
3 |Value1|8      |       3|ValueA


SELECT TOP 2 * FROM TableA LEFT JOIN TableB ON TableA.Id = TableB.ParentId

Result
--------------------------------------
Id|Value |ChildId|ParentId|ChildValue
--------------------------------------
1 |Value1|1      |       1|Value1
1 |Value1|2      |       1|Value2

Therefore the .Take(2) will return one object with two children (Id 1). Where you would want to return the first two objects (Id 1,2) with their children.

Yavor Shahpasov
  • 1,453
  • 1
  • 12
  • 19
  • I tried putting Fetch before the skip and take and after. I still got the same result. It shouldn't be a SQL limitation because in Entity Framework there is an equivalent method called Include which lets you eager load different relationships and that works just fine. It looks like it is specific to NHibernate LINQ provider. – Vadim Rybak Aug 08 '11 at 21:05
  • 1
    A fetch by default translates to SQL join. A join produces a cartesian product of the two tables, and the skip/take must be applied to the parent table only. The only way to do this is either use two queries, one for parent object and second for children. Anything else will produce incorrect result. Not sure how EF handles this problem. You can expeiment with .Fetch.Subselect() && .Not.LazyLoad() in your mapping (assuming you are using Fluent Nhibernate). This will cause two sql queries to be used and I have never tried it with the new Linq in NH3.1 – Yavor Shahpasov Aug 09 '11 at 07:30
  • Nhibernate criteria objects support this functionality without doing 2 queries. I dont see why a join would affect the skip and take. "Take" for example translates to "select top x" SQL statement. That would work on a standard select or on a join. – Vadim Rybak Aug 09 '11 at 20:11
  • Thanks... the example clarified it. – Vadim Rybak Aug 09 '11 at 21:31
  • btw what do you use for your mapping FluentNhibernate or xml files. – Yavor Shahpasov Aug 09 '11 at 21:45