Please post back in this thread if you can confirm that other queries which previously failed now work, so I can merge the fix into the main code base.

Currently we have two nested queries using "First()" method call which were failing before this fix and they are now working fine as expected.

Thank you for your indeed quick responses and excellent support.

Regards, Arash.

Glad your initial problem is now solved . I'll build a release build for you and attach this to this post.

Hi, I guess the fix you applied has a side effect, so I post it here again.

I'm using "SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll" version "2.6.9.1222"

With the following query :

var count = metaData.Categories.Count();

the count is always 1 even if there are more than one records in the database, I guess the "rownum <= 1" is causing this problem in generated oracle query.

I test it with an old dll and it was working as expected.

Would you please look into it.

Thanks, Arash.

The only solution I see is that when an aggregate function is in the projection, the original query has to be emitted, as there's no other way this will work. This thus might create an edge case where the original issue pops up (although less likely).

I'll rework the DQE code so it emits the original query if an aggregate is in the projection.

PLease see the attached Oracle DQE. It still fixes your initial First() etc. related issue, so it doesn't fall back onto the initial erroneous code.

Yes, it works fine now. Thank you very much.

There's another problem: with order by and group by: the limit is always on the set returned, and the WHERE clause is included in the main query so when where rownum <= x is included in the query, the orderby/groupby is applied on the limited set, while the query's intention is to limit the resulting set of the groupby/orderby.

So we've to fall back to the old scenario as well for queries which contain groupby/orderby clauses.

(ref: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17360)

I've attached a new 2.6 build which fixes this problem.

Otis wrote:

So we've to fall back to the old scenario as well for queries which contain groupby/orderby clauses.

So, do you mean we will have the previous problem with First() in the nested query?

arash wrote:

Otis wrote:

So we've to fall back to the old scenario as well for queries which contain groupby/orderby clauses.

So, do you mean we will have the previous problem with First() in the nested query?

Only if you supply an orderby. Which is logical, as the orderby has to be applied BEFORE the limit. Order of sql operators: from -> where -> group by -> projection -> distinct -> order by -> limit.

placing the limit inside the ordered set is therefore not correct (as the set will then always be 1 element big ).

It's not a problem though, as First + Orderby is the same as doing Max() or Min()

OK, Thanks.