QuerySpec paging issue in derived table

Posts   
 
    
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 16-Aug-2016 22:53:10   

I have a query that needs a derived table using paging (OFFSET and LIMIT) to control the resultset.

For a Northwind example, let's say that I need to page through the orders by groups of 20 customers at a time, rather than 20 orders at a time. The criteria, sorting and page size/number should all be dynamic at runtime.

The SQL to do the paging in an efficient way looks like this. (If I were doing a stored procedure, I'd replace the derived table with a CTE.)


SELECT
    c.CustomerID
    ,CompanyName
    ,o.OrderDate
FROM
    dbo.Customers As c
    JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID
    JOIN 
    (
        SELECT
            s1.CustomerID
        FROM
            dbo.customers AS s1
        WHERE
        -- dynamic where clause items here
            s1.Country = 'USA'
        ORDER BY
        -- dynamic sort clause here 
            CustomerID
        -- dynamic paging here
        OFFSET 5 ROWS
        FETCH NEXT 5 ROWS ONLY  
    ) As c2
    ON c.CustomerID = c2.CustomerID
ORDER BY 
    c.CompanyName

My QuerySpec looks like this


var query = qf.Create()
    .Select(CustomerFields.CustomerId, CustomerFields.CompanyName, OrderFields.OrderDate)
    .From(qf.Customer
        .InnerJoin(qf.Order)
        .On(CustomerFields.CustomerId == OrderFields.CustomerId)
        //create the derived table here
        .InnerJoin(qf.Create("s1")
                .Select(CustomerFields.CustomerId)
                .Where(CustomerFields.Country == "USA")
                .OrderBy(CustomerFields.CustomerId.Ascending())
                //.Page(2,5) //same problem with this
                .Offset(5)
                .Limit(5)
                )
        .On(CustomerFields.CustomerId == qf.Field(CustomerFields.CustomerId.Name).Source("s1"))
        )
        .OrderBy(CustomerFields.CompanyName.Ascending());

Everything is almost right but the DQE does not create the correct SQL for the Offset/Limit or Page. For both of those situations, the derived table uses "TOP N" with the value from Limit or the value from the page size in Page method and ignores the value in OFFSET or page number from Page method.


SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
       [Northwind].[dbo].[Customers].[CompanyName],
       [Northwind].[dbo].[Orders].[OrderDate]
FROM   (( [Northwind].[dbo].[Customers]
          INNER JOIN [Northwind].[dbo].[Orders]
              ON [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID])
        INNER JOIN (SELECT TOP(5 /* @p2 */) [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
                    FROM   [Northwind].[dbo].[Customers]
                    WHERE  (([Northwind].[dbo].[Customers].[Country] = 'USA' /* @p3 */))
                    ORDER  BY [Northwind].[dbo].[Customers].[CustomerID] ASC) [LPA_s1]
            ON [Northwind].[dbo].[Customers].[CustomerID] = [LPA_s1].[CustomerId])
ORDER  BY [Northwind].[dbo].[Customers].[CompanyName] ASC

I'm using LLBLGen 5.0.4 and I have SqlServerDQECompatibilityLevel set to 6.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Aug-2016 09:37:50   

You can't page in a derived table, at least we don't support that. So you can only page the final resultset. This is because in general paging statements aren't allowed in nested queries/derived tables (OFFSET might, but that depends on the compatibility level, and the pipeline already decides way earlier what to do with a derived table, which is always seen as a nested query, so special restrictions apply)

If you want to page through orders by groups of 20 customers, I think you need to issue 2 queries, namely 1 for the customerIDs (using paging) and one with an IN clause which simply fetches the orders for these customerIDs.

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 17-Aug-2016 17:48:46   

I thought that might be the answer. In that case, what's my simplest path using QuerySpec to get a List<int> or List<string> of the keys from the database?

I tried this and the SQL is correct but the value for ID in the anonymous type is always null even through the count is correct.


            var pageKeyQuery = qf.Create()
                .Select(OrderFields.CustomerId)         
                .Where(OrderFields.OrderDate <= new DateTime(1997, 7, 1))
                .WithProjector(r => new
                {
                    ID = OrderFields.CustomerId.ToValue<string>(),
                });

            var keyList = new List<string>(); 

            using (var dataAdapter = new DataAccessAdapter())
            {
                var pageKeys = dataAdapter.FetchQuery(pageKeyQuery);
                 //ID is null for all of them 
                 keyList = pageKeys.Select(i => i.ID.ToString()).ToList();
            }

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Aug-2016 20:04:52   
.WithProjector(r => new
{
    ID = (String)r[0],
});
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 17-Aug-2016 22:57:16   

Yes, that works, thanks for setting me straight. I see my problem now. I was mixing my code between two different approaches. Even though I read this in the docs, I didn't understand it.

https://www.llblgen.com/Documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_projections.htm

This part caused me confusion

qf.Create() .Select(()=>new { CompanyName = CustomerFields.CompanyName.ToValue<string>() });

Under the hood, the above lambda is converted to the lambda:

r=>new { CompanyName = (string)r[0]};

I read that as meaning that the two were functionally the same. Well yes, but they are two different options to project the result.

First option. Specify both the fields AND the mapping in the SELECT method. This seems like the easiest approach to me. (At least until Frans enhances WithProjector in v5.1 smile )


 .Select(() => new 
    {
        ID = OrderFields.CustomerId.ToValue<string>(),
    })

Second option. Specify the fields in the SELECT and the mapping in the WithProjector but with a different syntax for the mapping.


.Select(OrderFields.CustomerId)
.WithProjector(r => new
    {
        ID = (String)r[0],
    })

BTW, I'm not a fan of referencing by index. For anyone else reading this thread later, this syntax also works and I'd prefer it.


.Select(OrderFields.CustomerId)
.WithProjector(r => new
{
    ID = (String)r[OrderFields.CustomerId.Name],
})

Again, thanks Walaa.

jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 19-Aug-2016 22:49:40   

For anyone following behind me, here is the approach I have taken. Given the limitation that I can't have a derived table with paging, this approach performs well and gets the job done.

I'll provide some Northwind code to show how it works although this doesn't do a good job of explaining our actual business case.

Get the derived table keys for a specific criteria and page number/size.


public List<string> GetCustomerKeys(PredicateExpression filter, int pageNumber, int pageSize)
        {
            var qf = new QueryFactory();
            var pageKeyQuery = qf.Create()
                .Select(() => OrderFields.CustomerId.ToValue<string>())
                .From(qf.Customer
                .InnerJoin(qf.Order)
                .On(CustomerFields.CustomerId == OrderFields.CustomerId)
                .InnerJoin(qf.OrderDetail)
                .On(OrderFields.OrderId == OrderDetailFields.OrderId)
                )
                .Where(filter)
                .OrderBy(OrderFields.CustomerId.Ascending())            
                .Page(pageNumber, pageSize)
                .Distinct();

            using (var dataAdapter = new DataAccessAdapter())
            {
                return dataAdapter.FetchQuery<string>(pageKeyQuery);
            }   
    }

Use the keys from that query to limit the rows in the next query.


 public void GetCustomerProducts(PredicateExpression filter, SortExpression sortClauses, int pageNumber, int pageSize)
        {
            var bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(filter);

            var keyFilter = new PredicateExpression();          
            //condition to skip the paging and return all matching rows
            if (pageNumber > 0 && pageSize > 0)
            {
                var inList = GetCustomerKeys(filter, pageNumber, pageSize);
                keyFilter.Add(OrderFields.CustomerId == inList.ToArray());              
                bucket.PredicateExpression.Add(keyFilter);
            }

            var qf = new QueryFactory();
            var query = qf.Create()
                .Select(() = new CustomerProductsModel()
                        CustomerId = CustomerFields.CustomerId.ToValue<string>(), 
                        CompanyName = CustomerFields.CompanyName.ToValue<string>(), 
                        City = CustomerFields.City.ToValue<string>(), 
                        Country = CustomerFields.Country.ToValue<string>(),
                        OrderDate = OrderFields.OrderDate.ToValue<DateTime>(), 
                        ProductId = ProductFields.ProductId.ToValue<int>(), 
                        ProductName = ProductFields.ProductName.ToValue<string>()
                        )
                .From(qf.Customer
                .InnerJoin(qf.Order)
                .On(CustomerFields.CustomerId == OrderFields.CustomerId)
                .InnerJoin(qf.OrderDetail)
                .On(OrderFields.OrderId == OrderDetailFields.OrderId)
                .InnerJoin(qf.Product)
                .On(OrderDetailFields.ProductId == ProductFields.ProductId))
                .Where(bucket.PredicateExpression)
                .OrderBy(sortClauses.Cast<SortClause>().ToArray()); 

            using (var dataAdapter = new DataAccessAdapter())
            {
                return dataAdapter.FetchQuery<CustomerProductsModel>();
            }
    }


And it would be called like this.


var filter = new PredicateExpression();
filter.Add(OrderFields.OrderDate <= new DateTime(1997, 9, 1));
filter.Add(CustomerFields.Country == "USA");

var sorter = new SortExpression();
sorter.Add(CustomerFields.CompanyName.Ascending());
GetCustomerProducts(filter, sorter, 2, 5);


daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 20-Aug-2016 09:19:53   

Thanks for the feedback sunglasses

David Elizondo | LLBLGen Support Team