Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> QuerySpec paging issue in derived table
 

Pages: 1
Bugs & Issues
QuerySpec paging issue in derived table
Page:1/1 

  Print all messages in this thread  
Poster Message
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# 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.)

Code:

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
Code:

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.

Code:

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.
Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37605 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# 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.

Code:

            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();
            }


Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14520 posts
# Posted on: 17-Aug-2016 20:04:52.  
Code:
.WithProjector(r => new
{
    ID = (String)r[0],
});



  Top
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# 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
Quote:

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 Laugh)
Code:

.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.

Code:

.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.

Code:

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


Again, thanks Walaa.
Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# 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.
Code:

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.
Code:

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.
Code:

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);



Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8081 posts
# Posted on: 20-Aug-2016 09:19:53.  
Thanks for the feedback Cool
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.