Hello Gen Pro fam,

First off, this tools has been the foundation for many of my projects for over in the past year. Excellent work by Frans and the whole LLBL design team.

With that said, I have run into my first issue with the tool. I am using a typedlist to query a random set of data from SQL 2005. Usind the method:

DataAccessAdapter.FetchTypedList(ITypedListLgp2 typedListToFill, IPredicateExpression additionalFilter, int maxNumberOfItemsToReturn, ISortExpression sortClauses, bool allowDuplicates)

The sortClauses contains the NEWID() trick:

            IEntityField2 newIDField = EntityFieldFactory.Create(DbEntityFieldIndex.EntityId);
            newIDField.Alias = "NEWID()";
            newIDField.ExpressionToApply = new Expression();

But issue is when the maxNumberOfItemsToReturn parameter is set to a > 0 value and the allowDuplicates parameter is set to false. The query executes, but the list of results are not limited or distinct. For example:

If I want to retreive only the top 10, distinct rows from a random result list of 200 rows. The returned list will contain all 200 rows. It seems that the method is ignoring the maxNumberOfItemsToReturn and allowDuplicates value when generating the query.

But if you remove the NEWID() from the sortClause, the maxNumberOfItemsToReturn and allowDuplicates value is obeyed and generated in the query.

These results where confirmed using SQL Profiler. Also, if take the query that what generated from LLBL and add the DISTINCT TOP 10 to it and run it in SQL Management Studio, the query runs fine and returns the unique top 10 rows from the query.

Any help/ideas on how to fix this issue would be helpful.

I am using LLBL v 2.5, June 3rd build.

Thanks,

First thing to do is to try the latest available LLBLGen runtime library version. Then if it still not working, would you please post a complete code snippet.

Thanks.

Hey Walaa, thanks for the suggestion. I have updated the project using LLBL v2.6, the August 18th build and the same issue is appearing as in my original post.

Here is section of the source code:

int maxReturnRows = 100;
using (DataAccessAdapter adapter = new DataAccessAdapter(false))
{
    PredicateExpression filter = ActiveFilter2;                     

    //Random fields
    IEntityField2 newIDField = EntityFieldFactory.Create(DbEntityFieldIndex.EntityId);
    newIDField.Alias = "NEWID()";
    newIDField.ExpressionToApply = new Expression();

    SortExpression sorter = new SortExpression(DbEntityFields.Priority | SortOperator.Descending);
    sorter.Add(new SortClause(newIDField, null, SortOperator.Ascending));

    //...

    filter.AddWithAnd(
        new FieldCompareSetPredicate(DbEntityFields.EntityId, null, ShoppingCartVActiveEntityFields.EntityId, null, SetOperator.In,
            new PredicateExpression(ShoppingCartVActiveEntityFields.ApplicationId == "App1"), true));

    adapter.FetchTypedList(list, filter, maxReturnRows, sorter, false);         
}

return list;

The SQL generated is missing the TOP maxReturnRows and DISTINCT expressions.

Any help would be greatly appreciated.

Thanks

that might be caused by the fact it can't determine if duplicates will be occuring and it can't apply DISTINCT because you're likely sorting on a field not in the selectlist. It will do client-side top filtering /distinct filtering using a datareader and read one row at a time (so it's not fetching all rows first, it's reading rows from the datareader till the requested set is complete.

Is the result of the query correct?

The query result is correct, including the number records to return (I am not so concerned about the unique records). But this type of query would be more effective and efficient if the limiting was done in the sql server process. Because, if the query enter returns back thousands of records and you want only the first X random records, the sql server has to send all the matching records to the webserver for the .NET instance to process.

Doing it in sql server shaves seconds off the response time to the user (in my tests!), and that is what it is all about, delievering the best user experience possible.

Aoone wrote:

The query result is correct, including the number records to return (I am not so concerned about the unique records). But this type of query would be more effective and efficient if the limiting was done in the sql server process. Because, if the query enter returns back thousands of records and you want only the first X random records, the sql server has to send all the matching records to the webserver for the .NET instance to process.

Though it's likely (haven't seen the query you get) that adding TOP + DISTINCT will cause an error. DISTINCT is necessary because it can't determine if there are or aren't duplicates, and TOP is necessary for the limiting. As it can't emit DISTINCT, it switches to client side filtering.

It's not as bad as you think though A datareader receives its data in small batches, so even if the query results in millions of rows and you just want the first 10, it's likely your client only receives for example 25 rows (the first batch for example) and the client code then already has enough rows read to fulfill the request.

An example of a query which fails is:

SELECT DISTINCT CustomerId
FROM Orders
-- some where clause here. 
order by Orderdate DESC

When LLBLGen pro has to emit such a query, it doesn't add 'DISTINCT' to it, as it will cause a problem in the db, and switches to client side distinct filtering, so you get the right resultset without an exception, just with a slightly different approach.

We tweaked the runtime a bit on august 19th, so using a build later than that date uses the tweaked code), so less queries are limited on the client, though we can't do that for every query as the DQE can't always make a decision if the query indeed results in solely unique values, which is required for TOP to be useful at all.