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

Please check the fetched graph at the specified level to see if only one entity was pulled from the database, or more than that.

Hi Walla,

It does fetch all the required entities properly (10 of them) and our product is already in production.

But it gets 5000+ records instead of TOP 1 record for the middle entity.

If would like to try with your data/code or like to see generated SQL and code - let me know a email address where I can send you the code. I can not able to post the company's code here.

Regards,

Kakaiya

kakaiya wrote:

But it gets 5000+ records instead of TOP 1 record for the middle entity.

Hi Kakaiya. It looks like a TEXT or IMAGE field is in the select list for that middle table. TOP can only be added if DISTINCT is addable. DISTINCT is not addable if you have those types in the fields. As DISTINCT can't be added, neither can TOP. This would otherwise potentially give bad results. The limiting is then done in the object fetch logic. Not that the 5000+ wont travel over the wire, LLBLGen will use a datareader and will stop when the maxNumberOfItems had been reached, in this case 1.

If you want the TOP clause in the SELECT, try to exclude the TEXT/IMAGE fields from the fetch for that path node. See this for more info.

kakaiya wrote:

If would like to try with your data/code or like to see generated SQL and code - let me know a email address where I can send you the code. I can not able to post the company's code here.

I think this is not necessary for the moment. And, if you want to attach repro-case files, you can open a HelpDesk thread, which is private.