Hi,
I have a deep prefetch graph of A -> B -> C -> D -> E -> F ... -> J (about 10 level from top)
and in the middle way - lets say at level F (for a example) would like to only get TOP 1 record but generated SQL get 5000+ records from that table instead of TOP 1 for a given prefetch path.
Note: full graph (hardly get about 20 rows in total) fetch it is very efficient and the only issue is not generating TOP 1 in SQL)
in below line where filterC <= today's date (I want to get TOP 1 record from current date)
IPrefetchPathElement2 ppeD = C.SubPath.Add(DAL.EntityClasses.C.PrefetchPathD, 1, filterC, null, null);
Checking the generated query in SQL profiler do not have TOP 1 clause.
Also note that it works (TOP 1 is generated) when maxAmountOfItemsToReturn parameter is 1 on root entity (eg. level A) but when it is at deep level then it is respecting TOP clause.
Regards,
Kakaiya