- Home
- LLBLGen Pro
- Bugs & Issues
QuerySpec paging issue in derived table
Joined: 23-Jan-2005
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.
Joined: 17-Aug-2003
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.
Joined: 23-Jan-2005
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();
}
Joined: 23-Jan-2005
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.
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 )
.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.
Joined: 23-Jan-2005
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);