Hi,

I have some code that returns a lot of rows. To optimize performance and because I only need about 100 rows I have set maxNumberOfItemsToReturn to 100.

Using profiler I can see that this isn't reflected in the query.

Is it possible to get this to work?

Version: LLBLgen Pro 1.2005.1 May 2th Selfserving Using MS SQL server 2005

            int siteGuid = 10382;
            int languageGuid = 1;
            
            string search = "%guld%";

            int maxResults = 100;

            CommerceItemCollection itemCollection = new CommerceItemCollection();

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(CommerceItemFields.SiteGuid == siteGuid);
            filter.Add(CommerceItemTextFields.LanguageGuid == languageGuid);
            filter.Add(CommerceItemTextFields.Name % search);

            SortExpression sortExpression = new SortExpression();
            sortExpression.Add(CommerceItemTextFields.Name | SortOperator.Ascending);

            IRelationCollection relations = new RelationCollection();
            relations.Add(CommerceItemEntity.Relations.CommerceItemTextEntityUsingItemGuid);

            itemCollection.GetMulti(filter, maxResults, sortExpression,relations);

Generated query

exec sp_executesql N'SELECT [masterpiece].[dbo].[TBLcommerce_item].[Guid], [masterpiece].[dbo].[TBLcommerce_item].[SiteGuid], [masterpiece].[dbo].[TBLcommerce_item].[DateCreated], [masterpiece].[dbo].[TBLcommerce_item].[ItemNo], FROM ( [masterpiece].[dbo].[TBLcommerce_item]  INNER JOIN [masterpiece].[dbo].[TBLcommerce_itemText]  ON  [masterpiece].[dbo].[TBLcommerce_item].[Guid]=[masterpiece].[dbo].[TBLcommerce_itemText].[ItemGuid]) WHERE ( ( [masterpiece].[dbo].[TBLcommerce_item].[SiteGuid] = @SiteGuid1 AND [masterpiece].[dbo].[TBLcommerce_itemText].[LanguageGuid] = @LanguageGuid2 AND [masterpiece].[dbo].[TBLcommerce_itemText].[Name] LIKE @Name3)) ORDER BY [masterpiece].[dbo].[TBLcommerce_itemText].[Name] ASC',N'@SiteGuid1 int,@LanguageGuid2 int,@Name3 nvarchar(6)',@SiteGuid1=10382,@LanguageGuid2=1,@Name3=N'%guld%'

By removing my SortExpression a "TOP" will be reflected in the query, but that's not an option.

The code above have also been tested in LLBLgen 2.5 Final (December 5th, 2007) with same result.

Regards, Jacob Andersen

Regardless of the generated SQL, do you get 100 entities in your fetched collection? Would you please post the table DDL? And does the issue occur when sorting with any field or only with specific one(s)?

The following thread seems relevant: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6220

You have a relation in your query. This means there can be duplicates in the set returned. This can be avoided by emitting 'DISTINCT' in the query. However DISTINCT is only valid if the ORDER BY fields are in the select list. Otherwise, you'll get an error in SQLServer. So DISTINCT can't be emitted, and because of that, TOP can't be emitted.

This makes LLBLGen Pro switch to client-side limiting. It will read as much rows as required (100) and will after that close the datareader.

Example: SELECT DISTINCT TOP 10 C.* FROM Customers C inner join Orders O ON C.CustomerID = O.CustomerID ORDER BY O.EmployeeID

This will fail. (northwind): Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

So distinct can't be emitted. But: SELECT TOP 10 C.* FROM Customers C inner join Orders O ON C.CustomerID = O.CustomerID ORDER BY O.EmployeeID

gives duplicate rows. As LLBLGen Pro filters out the duplicate entities anyway, you'll get 9 entities returned, not 10. Hence the client-side limiting.