Combined Paging
What am I doing?
I came across an interesting requirement the other day. I'm sure we're all familiar with paging. Usually you use it with a database with a large amount of rows in a result set. The code to do that with LINQ is pretty straightforward. In this particular case we are working with widgets.
var offset = (page - 1) * pageSize;
var results = await dbContext.Widgets.Skip(offset).Take(pageSize).ToArrayAsync();
As you can see, there are 2 variables/parameters.
| Name | Description |
|---|---|
| page | specifies the requested page of results to return (it's also 1-based in this case) |
| pageSize | specifies the maximum number of widgets to return for each page |
- Since we are using the skip/take approach to paging, I'll calculate the offset (the number of widgets to skip over to get to the first widget in the requested page).
- Once the offset is calculated, run the query and .Skip() and .Take() my way to return the page of widgets I need.
That's pretty clear to do, but what happens when I need to combine 2 sources of Widgets in a paged result set?
The Requirements
Static Widgets Retrieval
- Retrieve the static widgets for the requested page using a black-box method (GetStaticWidgets).
- The static items should be paged according to the current page and page size.
Database Widgets Retrieval
- Retrieve the database widgets for the requested page using a black-box method (GetDbWidgets).
- The database widgets should be paged according to the current page and page size, but only after static widgets are considered.
Paging Logic
- If static widgets are available for the requested page, fill the page with as many static widgets as possible.
- If the number of static widgets is less than the page size, fill the remainder of the page with database widgets.
- If there are no static widgets for the requested page, fill the page entirely with database widgets.
- If the requested page exceeds the total number of available widgets, return an empty result.
Result Construction
- The result must be a concatenation of the static widgets (if any) and the database widgets (if any) for the requested page.
- The order of widgets must be preserved: static widgets first, then database widgets.
The Code
public void CombinedPaging_ReturnsExpectedItems(int page = 1, int pageSize = 5, int staticItemCount = 5, int dbItemCount = 5, string[]? expected = null)
{
// Calculate the offset for the requested page
var requestedOffset = (page - 1) * pageSize;
var dbOffset = requestedOffset;
var dbTake = pageSize;
// Get all static widgets (static items)
var staticWidgets = GetStaticWidgets(staticItemCount);
var staticWidgetsCount = staticWidgets.Length;
// Page the static widgets for the current page
staticWidgets = staticWidgets.Skip(requestedOffset).Take(pageSize).ToArray();
// If there are any static widgets, determine how many db widgets to fetch
if (staticWidgetsCount > 0)
{
// Calculate the offset for db widgets, accounting for static widgets
dbOffset = int.Max(0, requestedOffset - staticWidgetsCount);
if (requestedOffset < staticWidgetsCount)
{
// If static widgets fill part of the page, only take the remainder from db
dbTake = int.Max(0, pageSize - staticWidgets.Length);
}
else
{
// If static widgets are exhausted, take a full page from db
staticWidgets = [];
dbTake = pageSize;
}
}
// Get the db widgets for the calculated offset and take
var dbItems = GetDbWidgets(dbItemCount, dbOffset, dbTake);
// Combine static and db widgets for the final result (static first, then db)
var actual = staticWidgets.Concat(dbItems);
// Assert that the actual result matches the expected result
actual.ShouldBe(expected);
}
The Repository
...and that's how it got done (basically). If you'd like to get a copy of this code, you can get it from my GitHub Repository.
Why would you do this?
For starters, it's what was asked for in the work item. Second, when I was getting started in developing software that used databases, best practice was that you pull back only what you need. No more, no less. That's what this code does. The code that generates the static widgets is going to execute everytime this code is run because the number of static widgets determines the number of widgets I need to pull from the databse.
Anyway, nothing earth-shattering, but I found it an interesting bit of code to write and I hope you did too. If you have any questions or comments, please let me know!
