I am using your software for a while now and I am bumping into a problem. I want to select the top 5 of a selection and fetch the entities by a getMulti.

The SQL code looks like:

SELECT * FROM Tags WHERE TagID IN 
(
   SELECT TOP 5 ProductsTags.TagID
   FROM ProductsTags
   GROUP BY ProductsTags.TagID
   ORDER BY COUNT(ProductsTags.TagID) DESC
)

This is the llblgen code I do have so far:

TagsCollection tc = new TagsCollection();
                
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(ProductsTagsFields.TagId);

SortExpression sorter = new SortExpression();
sorter.Add(new SortClause(ProductsTagsFields.TagId.SetAggregateFunction(AggregateFunction.Count), SortOperator.Descending));

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(TagsFields.TagId, ProductsTagsFields.TagId, SetOperator.In, null, null, "", 5, sorter, false, groupBy));

tc.GetMulti(filter);

The problem is that the generated llblgen SQL code doesn't contain the count(ProductsTags.TagID) in the Sort By. In other threads I was reading this is possible since version 2. However with my current version 2.5 it doesn't work. Hope you can help me out.

Generated:

"( [Development].[dbo].[Tags].[TagID] IN (SELECT TOP 5 [Development].[dbo].[ProductsTags].[TagID] AS [TagId] FROM [Development].[dbo].[ProductsTags]  GROUP BY [Development].[dbo].[ProductsTags].[TagID] ORDER BY TagId DESC))"

Which LLBLGen Pro runtime library are you using?

Version 2.5 final.

Please check the following thread to know how to get the runtime library version: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

Thanks.

Oeps sorry. The fileversion is 2.5.7.1119. Thanks.

That's a relatively old version, would you please download and try the latest available version.

I have updated my codebase to the newest version (2.6.8.804). However it's still not working. The error is now: " The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

jdscomp wrote:

I have updated my codebase to the newest version (2.6.8.804). However it's still not working. The error is now: " The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Having sortclauses not in the projection is a bit problematic in many occasions but often leads to good results, however in edge cases the query can give errors.

By default a SortClause will result in a [fieldname (or alias) ] [operator] fragment, though if you want to have its Aggregate/expression be used instead, because it's not used in the project (and this is the case in your query), you've to set the parameter: ISortClause.EmitAliasForExpressionAggregateField to false (default is true). This was added in v2.6

So instead of:

sorter.Add(new SortClause(ProductsTagsFields.TagId.SetAggregateFunction(AggregateFunction.Count), SortOperator.Descending));

do

ISortClause clause = new SortClause(ProductsTagsFields.TagId.SetAggregateFunction(AggregateFunction.Count), SortOperator.Descending);
clause.EmitAliasForExpressionAggregateField =false;
sorter.Add(clause);

If you still get sql errors, you have to post the exact query you get otherwise we are in the dark how it looks like at runtime.

Thanks for your reply. Seems to be we are making progress but still not there. The SORT BY is now working. However the TOP 5 is still not omitted.

My C# code:

TagsCollection tc = new TagsCollection();

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(ProductsTagsFields.TagId);

SortExpression sorter = new SortExpression();
ISortClause clause = new SortClause(ProductsTagsFields.TagId.SetAggregateFunction(AggregateFunction.Count), SortOperator.Descending);
clause.EmitAliasForExpressionAggregateField = false;
sorter.Add(clause);

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(TagsFields.TagId, ProductsTagsFields.TagId, SetOperator.In, null, null, "", 5, sorter, false, groupBy));

tc.GetMulti(filter);

The query generated by de filter:

"( [Development].[dbo].[Tags].[TagID] IN (SELECT [Development].[dbo].[ProductsTags].[TagID] AS [TagId] FROM [Development].[dbo].[ProductsTags]  GROUP BY [Development].[dbo].[ProductsTags].[TagID] ORDER BY COUNT([Development].[dbo].[ProductsTags].[TagID]) DESC))"

Hope this will help out to solve my problem. Thanks in advance anyway.

Might be due to a restriction on the TOP clause which needs to be emited with distinct however your sorter is on a field not in the selectlist. (it's not equal to the field in the selectlist). Your query's SQL works, so TOP should be emitted, I'll see if I can find the reason it's not emitted and adjust the restriction a bit.

It doesn't emit the TOP clause because it can't determine if duplicates will arise and it can't emit DISTINCT, at least it assumes that, because the order by is on a field not in the select list. However, a grouped query never results in a duplicate. I'll add code which tests on that too and will see if that fixes things.

(edit) see the attached DQE for the fix.

Thanks a lot! This did fix my issue.