Hello,

to retrieve data from a large database with self-references I'm using the following code:

MethodStart: var predicate = ... some initial predicate expression var qf=new QueryFactory(); var q=qf.From(qf....).Where(predicate)

if (recurisonLevel == 1) {q=q.Limit(maxRows).OrderBy(TimeStamp)}

... FetchQuery(q.Select(...)) ... // now check the clients predicate=<EntityName>Fields.ParentRef.In(q.Select(ParentRef)) goto MethodStart;

(Recursion level 1..3)

The call on recursion level 1 generates the ORDER and TOP clause. My expectation for recursion level 2+3 is, that the subquery still has to use the initial ORDER+TOP clause. But only the ORDER clause survive. The top clause on the sub query is removed. The problem is on MS SQL Srever side. MS SQL Srever accepts a ORDER for a sub query only, if there is a TOP clause. Oracle works fine with the implemented approach (fast, correct).

It seems to me, that the limit function is restricted and not being called for sub queries. I can see the same generated code for ORACLE - which is accepted.

Thanks a lot for any help with this issue.

What's teh LLBLGen Pro version you're using and the runtime buildnr? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

Newest versions from Feb 2016. Actually I've tested with different combination of DISTINCT and ORDER. In one combination the TOP clause has been created (no DISTINCT, no ORDER).

For the sub query something is wrong - in general I'm really happy with your ORM mapper.

For sqlserver order by in a subquery requires a limit (TOP). With Oracle, things are a bit differently because it always has had a limit system outside the Select / projection (it uses a where using rownum). 12c introduced LIMIT, but internally this doesn't make a difference. I think with sqlserver it's a performance optimization thing.

So if you specify an order by in a subquery on sqlserver, you have to specify a row limit.

Your query doesn't say anything about distinct though.

Fetching a hierarchy is often done with a CTE however. This is in many cases more efficient. CTEs aren't possible with our query systems so you have to define a view with the CTE, but unless your table is small, it might be worth the effort. Oracle and SQL Server both support CTEs so you can define a view in both databases, and use them to obtain the data you want.

Thanks for your answer.

Here some comments: a) in my special use case a predefined view does not work. As I told it is an recursive approach. In details I have a flexible inner WHERE CLAUSE and any number of encapsulations (ParentRef in ()) b) I've copied your generated SQL statement and added the TOP clause in the most inner sub view. This statement worked well against the RDBMS and showed an acceptable profile. c) If there is any chance I would appreciate having this support in a future version of LLBLGEN

So far I use a workaround. The inner view also contains an ORDER CLAUSE (LIMIT makes only sense with an ORDER). For the nesting level 2++ I convert the LIMIT clause into a WHERE clause with the predicate (TimeStamp >= {latest_time_stamp_from_limit_clause_on_level_1})

This works fine and the change of the predicate is an acceptable risk.

Thanks a lot,

Matthias

I'm confused though. Your initial code doesn't contain all statements and it's unclear what exactly is created. I also have a bit of a hard time understanding what exactly the problem is: you say a TOP/limit is missing, but you didn't post any code where you added it.

So, I'd like to ask you to create a small piece of code which creates the query you're using, on AdventureWorks.HumanResources.Employee. This entity has a relationship with self so you can use that to do a recursive query.

We can then examine what SQL it creates at runtime and whether that matches with what's expected and if not, why it doesn't do as expected.

At the moment I don't fully understand what you're after.