Dynamic filtering, sorting, and pagination with EF Core specifications becomes risky when a list endpoint accepts arbitrary filters, property names, or expressions from a caller. The safe alternative is a bounded query envelope: a known set of optional filters, an allowlist of sort modes, a fully unique ordering, a maximum page size, and a visible terminal operation.
This article builds that envelope for a product search. It covers dynamic filtering, sorting, offset pagination, keyset pagination, optional total counts, cancellation, and relational tests. It does not load entity graphs or choose tracking modes. It also does not turn user input into arbitrary member access or executable expressions.
The examples target net10.0, C# 14, and EF Core 10.0.10. SQLite provides relational execution evidence for the sample. Production-provider translation, collation, index use, SQL shape, and query plans still require validation against the production database provider.
Treat Dynamic Filtering, Sorting, and Pagination as Data
A list request often contains values such as:
- An optional search term.
- Optional minimum and maximum prices.
- An optional active-state filter.
- A requested sort label.
- A page number and page size.
- A request for an exact total count.
Those values are data. They should select from query behavior the application already owns. They should not choose any property through reflection, submit a serialized expression tree, or name an arbitrary method to run.
That boundary is important for correctness as well as safety. EF Core's client evaluation guidance states that an untranslatable expression outside the final projection throws at runtime. Accepting an arbitrary expression does not make it translatable. It merely postpones the failure until execution.
A safe full query envelope validates the request once and exposes only application-defined choices. The resulting specification is immutable. Callers can vary values, but they cannot invent new query structure.
Build an Allowlisted Query Envelope
The following sample uses a positional request record, a validated specification record, and an enum-backed sort allowlist. The caller can request newest, name, name-desc, price, or price-desc. No other property can become an OrderBy.
The filter is an Expression<Func<Product, bool>>, which keeps it visible to Queryable.Where. The .NET 10 Queryable.Where API accepts an expression tree rather than a compiled delegate. That is the provider-visible form an EF Core specification needs.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
namespace SafeDynamicSpecifications;
public sealed class Product
{
private Product()
{
}
public Product(
long id,
string name,
decimal price,
bool isActive,
long createdSequence)
{
Id = id;
Name = name;
Price = price;
IsActive = isActive;
CreatedSequence = createdSequence;
}
public long Id { get; private set; }
public string Name { get; private set; } = string.Empty;
public decimal Price { get; private set; }
public bool IsActive { get; private set; }
public long CreatedSequence { get; private set; }
}
public sealed class CatalogDbContext(DbContextOptions<CatalogDbContext> options)
: DbContext(options)
{
public DbSet<Product> Products => Set<Product>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var product = modelBuilder.Entity<Product>();
product.HasKey(item => item.Id);
product.Property(item => item.Name).HasMaxLength(200);
product.Property(item => item.Price).HasPrecision(18, 2);
product.HasIndex(item => new { item.CreatedSequence, item.Id });
product.HasIndex(item => new { item.Name, item.Id });
product.HasIndex(item => new { item.Price, item.Id });
}
}
public sealed record ProductSearchRequest(
string? Term,
decimal? MinimumPrice,
decimal? MaximumPrice,
bool? IsActive,
string? Sort,
int PageNumber,
int PageSize,
bool IncludeTotalCount);
public enum ProductSort
{
Newest,
NameAscending,
NameDescending,
PriceAscending,
PriceDescending,
}
public sealed class ProductSearchSpecification
{
public const int MaximumPageSize = 100;
private ProductSearchSpecification(
string? term,
decimal? minimumPrice,
decimal? maximumPrice,
bool? isActive,
ProductSort sort,
int pageNumber,
int pageSize,
bool includeTotalCount)
{
Term = term;
MinimumPrice = minimumPrice;
MaximumPrice = maximumPrice;
IsActive = isActive;
Sort = sort;
PageNumber = pageNumber;
PageSize = pageSize;
IncludeTotalCount = includeTotalCount;
}
public string? Term { get; }
public decimal? MinimumPrice { get; }
public decimal? MaximumPrice { get; }
public bool? IsActive { get; }
public ProductSort Sort { get; }
public int PageNumber { get; }
public int PageSize { get; }
public bool IncludeTotalCount { get; }
public static ProductSearchSpecification Create(
ProductSearchRequest request)
{
ArgumentNullException.ThrowIfNull(request);
ValidatePageNumber(request.PageNumber, nameof(request.PageNumber));
ValidatePageSize(request.PageSize, nameof(request.PageSize));
if (request.MinimumPrice is < 0)
{
throw new ArgumentOutOfRangeException(
nameof(request.MinimumPrice));
}
if (request.MaximumPrice is < 0)
{
throw new ArgumentOutOfRangeException(
nameof(request.MaximumPrice));
}
if (request.MinimumPrice > request.MaximumPrice)
{
throw new ArgumentException(
"MinimumPrice cannot exceed MaximumPrice.",
nameof(request));
}
var term = string.IsNullOrWhiteSpace(request.Term)
? null
: request.Term.Trim();
if (term?.Length > 100)
{
throw new ArgumentException(
"Term cannot exceed 100 characters.",
nameof(request));
}
var sort = request.Sort?.Trim().ToLowerInvariant() switch
{
null or "" or "newest" => ProductSort.Newest,
"name" => ProductSort.NameAscending,
"name-desc" => ProductSort.NameDescending,
"price" => ProductSort.PriceAscending,
"price-desc" => ProductSort.PriceDescending,
_ => throw new ArgumentException(
"The requested sort field is not supported.",
nameof(request)),
};
return new ProductSearchSpecification(
term,
request.MinimumPrice,
request.MaximumPrice,
request.IsActive,
sort,
request.PageNumber,
request.PageSize,
request.IncludeTotalCount);
}
public ProductSearchSpecification WithPageNumber(int pageNumber)
{
ValidatePageNumber(pageNumber, nameof(pageNumber));
return new ProductSearchSpecification(
Term,
MinimumPrice,
MaximumPrice,
IsActive,
Sort,
pageNumber,
PageSize,
IncludeTotalCount);
}
public Expression<Func<Product, bool>> BuildCriteria()
{
var term = Term;
var minimumPrice = MinimumPrice;
var maximumPrice = MaximumPrice;
var isActive = IsActive;
return product =>
(term == null || product.Name.Contains(term)) &&
(!minimumPrice.HasValue ||
product.Price >= minimumPrice.Value) &&
(!maximumPrice.HasValue ||
product.Price <= maximumPrice.Value) &&
(!isActive.HasValue ||
product.IsActive == isActive.Value);
}
public IOrderedQueryable<Product> ApplyOrdering(
IQueryable<Product> products)
{
return Sort switch
{
ProductSort.Newest => products
.OrderByDescending(product => product.CreatedSequence)
.ThenByDescending(product => product.Id),
ProductSort.NameAscending => products
.OrderBy(product => product.Name)
.ThenBy(product => product.Id),
ProductSort.NameDescending => products
.OrderByDescending(product => product.Name)
.ThenByDescending(product => product.Id),
ProductSort.PriceAscending => products
.OrderBy(product => product.Price)
.ThenBy(product => product.Id),
ProductSort.PriceDescending => products
.OrderByDescending(product => product.Price)
.ThenByDescending(product => product.Id),
_ => throw new ArgumentOutOfRangeException(
nameof(Sort),
Sort,
"Unsupported sort mode."),
};
}
private static void ValidatePageNumber(
int pageNumber,
string parameterName)
{
if (pageNumber < 1)
{
throw new ArgumentOutOfRangeException(parameterName);
}
}
private static void ValidatePageSize(
int pageSize,
string parameterName)
{
if (pageSize is < 1 or > MaximumPageSize)
{
throw new ArgumentOutOfRangeException(parameterName);
}
}
}
public sealed record ProductListItem(
long Id,
string Name,
decimal Price,
long CreatedSequence);
public sealed record OffsetPage<T>(
IReadOnlyList<T> Items,
int PageNumber,
int PageSize,
int? TotalCount);
public sealed record ProductCursor(
long CreatedSequence,
long Id);
public sealed record KeysetPage<T>(
IReadOnlyList<T> Items,
ProductCursor? NextCursor,
bool HasMore);
public static class ProductSearchQueries
{
public static IQueryable<ProductListItem> BuildOffsetQuery(
IQueryable<Product> products,
ProductSearchSpecification specification)
{
var filtered = products.Where(specification.BuildCriteria());
var ordered = specification.ApplyOrdering(filtered);
var offset = checked(
(specification.PageNumber - 1) * specification.PageSize);
return ordered
.Skip(offset)
.Take(specification.PageSize)
.Select(product => new ProductListItem(
product.Id,
product.Name,
product.Price,
product.CreatedSequence));
}
public static async Task<OffsetPage<ProductListItem>> SearchOffsetAsync(
CatalogDbContext dbContext,
ProductSearchSpecification specification,
CancellationToken cancellationToken)
{
var filtered = dbContext.Products
.Where(specification.BuildCriteria());
int? totalCount = null;
if (specification.IncludeTotalCount)
{
totalCount = await filtered.CountAsync(cancellationToken);
}
var items = await BuildOffsetQuery(
dbContext.Products,
specification)
.ToListAsync(cancellationToken);
return new OffsetPage<ProductListItem>(
items,
specification.PageNumber,
specification.PageSize,
totalCount);
}
public static async Task<KeysetPage<ProductListItem>> SearchNextAsync(
CatalogDbContext dbContext,
ProductSearchSpecification specification,
ProductCursor? after,
CancellationToken cancellationToken)
{
if (specification.Sort != ProductSort.Newest)
{
throw new ArgumentException(
"This cursor format is defined only for newest sorting.",
nameof(specification));
}
IQueryable<Product> query = dbContext.Products
.Where(specification.BuildCriteria());
if (after is not null)
{
var cursorSequence = after.CreatedSequence;
var cursorId = after.Id;
query = query.Where(product =>
product.CreatedSequence < cursorSequence ||
(product.CreatedSequence == cursorSequence &&
product.Id < cursorId));
}
var candidates = await query
.OrderByDescending(product => product.CreatedSequence)
.ThenByDescending(product => product.Id)
.Select(product => new ProductListItem(
product.Id,
product.Name,
product.Price,
product.CreatedSequence))
.Take(specification.PageSize + 1)
.ToListAsync(cancellationToken);
var hasMore = candidates.Count > specification.PageSize;
var items = candidates
.Take(specification.PageSize)
.ToArray();
ProductCursor? nextCursor = null;
if (hasMore && items.Length > 0)
{
var last = items[^1];
nextCursor = new ProductCursor(
last.CreatedSequence,
last.Id);
}
return new KeysetPage<ProductListItem>(
items,
nextCursor,
hasMore);
}
}
CreatedSequence represents an application-owned monotonic creation value. It keeps the relational sample independent of provider-specific date mappings. A production model can use DateTimeOffset plus a unique ID when its provider mapping and indexes have been validated.
The filter expression uses captured scalar values. It does not inject Expression.Constant nodes manually. Microsoft's advanced performance guidance explains that dynamically constructed trees with changing constant nodes can defeat query-shape caching and pollute the database plan cache. Captured scalar values are the normal parameterized path.
The finite sort switch intentionally produces a finite set of known query shapes. That is a reasonable tradeoff. Supporting five reviewed orderings is more code than accepting a property string, but every ordering has known semantics and a unique tie-breaker.
Define Filter Semantics Before Building the Expression
Optional filters need an explicit meaning before they become expression-tree nodes. Otherwise, different endpoints tend to make slightly different assumptions and a reusable specification becomes a bag of accidental behavior.
The sample defines these semantics:
- A null, empty, or whitespace-only term means no text filter.
- A non-empty term is trimmed and limited to 100 characters.
- A null minimum or maximum means that bound is absent.
- A minimum greater than the maximum is invalid.
- A null active-state value means both active and inactive products are eligible.
- An unsupported sort label is rejected rather than replaced with an unrelated fallback.
These are application decisions. Another product may intentionally treat an empty term as invalid, clamp negative prices to zero, or default an unknown sort to newest. What matters is that the choice is made during envelope creation rather than buried inside a LINQ chain.
Text search needs additional care. string.Contains is easy to read, but case sensitivity, collation, available functions, and index use are database concerns. The specification should not promise "case-insensitive search" unless the production provider, column collation, and generated SQL prove that behavior. If the application requires linguistic search, accent handling, ranking, or tokenization, define a separate application-owned search mode rather than accepting arbitrary string methods from a client.
Keeping validation outside BuildCriteria also helps testing. Pure request tests can prove the accepted ranges and allowlist without starting a database. Relational tests can then focus on the smaller question: does each accepted query shape translate and return the intended rows on the target provider?
Make Invalid Pagination States Unrepresentable
The validated specification requires a positive page number and a page size from 1 through 100. The offset calculation uses checked, so a request large enough to overflow an int fails instead of wrapping into a different offset.
The sealed class has a private constructor, so Create is the initial construction path. WithPageNumber creates an immutable copy only after applying the same positive-page validation. A stricter design could still replace raw integers with dedicated PageNumber and PageSize value objects.
The key idea is that Skip and Take should never receive unreviewed request values. By the time query construction begins, the specification should already represent a valid, bounded request.
Fully Unique Ordering Is Not Optional
Microsoft's pagination guidance warns that pagination ordering must be fully unique. Relational databases do not add primary-key ordering automatically.
Ordering only by Name is insufficient because multiple products can share a name. Ordering only by Price is insufficient because multiple products can share a price. Ordering only by a creation value is insufficient if two products can receive the same value.
That is why every allowlisted sort ends with Id:
Name, thenId.Price, thenId.CreatedSequence, thenId.
The direction of the tie-breaker follows the primary ordering in this example. What matters is that the final ordering is deterministic and that cursor comparisons use the same direction and fields.
The broader LINQ ordering guide explains ordering operators in more depth. For a specification envelope, the additional responsibility is to make total ordering part of the contract rather than an endpoint convention.
Offset Pagination Is Simple but Has Tradeoffs
Offset pagination uses Skip and Take. It supports direct navigation to page 12, which is valuable for administrative grids and interfaces that display numbered pages. It is also easy to explain and easy to include in a result envelope.
Microsoft's EF Core pagination guidance documents these offset tradeoffs:
- The database still processes skipped rows.
- Work can grow as the offset grows.
- Concurrent inserts or deletes can cause rows to be skipped or repeated between requests.
Those facts do not make offset pagination invalid. They define when it fits. If the user genuinely needs random page access, offset pagination may be the clearer contract. Bound the page size, use fully unique ordering, and validate representative deep pages with the production database and indexes.
The sample rejects invalid page sizes instead of silently accepting an unbounded request. A maximum of 100 is an application policy, not a universal EF Core number. Choose a bound that matches the payload and use case.
Remember that Skip and Take still operate before ToListAsync. The deferred execution guide is useful background for understanding why query construction and execution are separate.
Keyset Pagination Is Better for Sequential Navigation
Keyset pagination, also called seek pagination, asks for rows after the last row already seen. The sample's ProductCursor contains the two values that define the newest ordering:
CreatedSequence DESC, Id DESC
The next-page predicate mirrors that ordering:
CreatedSequence < cursor.CreatedSequence
OR
(CreatedSequence = cursor.CreatedSequence AND Id < cursor.Id)
The query takes one extra row. If the extra row exists, the response reports HasMore and emits the cursor from the last returned item.
The pros and cons are straightforward:
- Choose offset pagination when random page access is a real requirement.
- Choose keyset pagination for next and previous navigation over a stable ordering.
- Consider supporting both when the product genuinely needs both behaviors.
Do not reuse one cursor across different filters or sort modes. In a production API, encode or sign the filter version and sort mode with the cursor, then validate them when the cursor returns. The code keeps the cursor transparent so the ordering logic remains easy to inspect.
Total Counts Are a Separate Query Decision
An exact total count is useful when a UI must display "2,431 results" or calculate a final page number. It is not free metadata attached to the item query. In EF Core, CountAsync asynchronously executes the count over the query.
The sample makes the count optional. When requested, it executes the filtered count first and the bounded item query second. It awaits them sequentially because EF Core does not support parallel operations on the same DbContext. The EF Core async guidance also explains that cancellation tokens are passed to the provider, although a provider may not honor cancellation immediately.
Sequential execution avoids parallel use of one DbContext, but it does not make the two commands an atomic snapshot. A concurrent write between the count and page query can make the values disagree. When the use case requires snapshot consistency, choose a provider-supported transaction and isolation strategy deliberately; EF Core transaction guidance describes the atomic boundary, while stronger consistency can add locking, version-store, retry, or throughput costs depending on the database.
Options include:
- Return an exact count when the interface requires it.
- Omit the count for infinite scrolling or simple next-page navigation.
- Return
HasMoreby fetching one extra row. - Use an application-specific approximate or cached count only when its freshness semantics are explicit.
There is no responsible universal statement that a count is cheap or expensive. Its cost depends on the filter, indexes, data distribution, provider, and database plan. Measure it on the production system if it matters.
The LINQ element access guide provides useful context for terminal operators and count-related APIs, but an EF Core count remains a provider-executed query.
Keep the IQueryable Boundary Visible
The sample keeps filtering, ordering, projection, and bounds on IQueryable<T> until CountAsync or ToListAsync. It does not call:
Compile()on the specification criteria.AsEnumerable()before filtering.ToList()before ordering or pagination.- A local method supplied by the caller.
That is how the query remains available to the EF Core provider for translation. The EF Core query guide covers the broader server-versus-client boundary, while this specification keeps that boundary explicit in one place.
The term filter uses string.Contains. Translation and comparison semantics can differ by provider and collation. A passing SQLite test proves SQLite execution for this sample. It does not prove case behavior, index use, or production SQL for SQL Server, PostgreSQL, MySQL, or another provider.
Relational Tests Prove Translation and Ordering
The following tests use SQLite in memory as a relational smoke test. They prove that the sample translates and executes on SQLite, that duplicate primary sort values remain deterministic, that keyset pages do not overlap for the seeded data, and that requesting an exact count produces two reader commands.
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Xunit;
namespace SafeDynamicSpecifications;
public sealed class ReaderCommandCounter : DbCommandInterceptor
{
public int Count { get; private set; }
public void Reset()
{
Count = 0;
}
public override ValueTask<InterceptionResult<DbDataReader>>
ReaderExecutingAsync(
DbCommand command,
CommandEventData eventData,
InterceptionResult<DbDataReader> result,
CancellationToken cancellationToken = default)
{
Count++;
return ValueTask.FromResult(result);
}
}
public sealed class ProductSearchRelationalTests
{
[Fact]
public void Create_UnknownSort_Throws()
{
var request = new ProductSearchRequest(
null,
null,
null,
null,
"drop-table",
1,
25,
false);
Assert.Throws<ArgumentException>(
() => ProductSearchSpecification.Create(request));
}
[Fact]
public void Create_PageNumberBelowOne_Throws()
{
var request = new ProductSearchRequest(
null,
null,
null,
null,
"newest",
0,
25,
false);
Assert.Throws<ArgumentOutOfRangeException>(
() => ProductSearchSpecification.Create(request));
}
[Theory]
[InlineData(0)]
[InlineData(ProductSearchSpecification.MaximumPageSize + 1)]
public void Create_InvalidPageSize_Throws(int pageSize)
{
var request = new ProductSearchRequest(
null,
null,
null,
null,
"newest",
1,
pageSize,
false);
Assert.Throws<ArgumentOutOfRangeException>(
() => ProductSearchSpecification.Create(request));
}
[Fact]
public void WithPageNumber_BelowOne_Throws()
{
var specification = ProductSearchSpecification.Create(
new ProductSearchRequest(
null,
null,
null,
null,
"newest",
1,
25,
false));
Assert.Throws<ArgumentOutOfRangeException>(
() => specification.WithPageNumber(0));
}
[Fact]
public void WithPageNumber_ValidValue_ReturnsValidatedCopy()
{
var first = ProductSearchSpecification.Create(
new ProductSearchRequest(
null,
null,
null,
null,
"newest",
1,
25,
false));
var second = first.WithPageNumber(2);
Assert.NotSame(first, second);
Assert.Equal(1, first.PageNumber);
Assert.Equal(2, second.PageNumber);
Assert.Equal(first.PageSize, second.PageSize);
}
[Fact]
public async Task OffsetPages_DuplicateNames_HaveDeterministicOrder()
{
await using var database = await TestCatalogDatabase.CreateAsync();
await using var context = database.CreateContext();
var first = ProductSearchSpecification.Create(
new ProductSearchRequest(
null,
null,
null,
null,
"name",
1,
2,
false));
var second = first.WithPageNumber(2);
var firstQuery = ProductSearchQueries.BuildOffsetQuery(
context.Products,
first);
var generatedSql = firstQuery.ToQueryString();
var firstPage = await firstQuery.ToListAsync(
CancellationToken.None);
var secondPage = await ProductSearchQueries.BuildOffsetQuery(
context.Products,
second)
.ToListAsync(CancellationToken.None);
Assert.False(string.IsNullOrWhiteSpace(generatedSql));
Assert.Equal([1L, 2L], firstPage.Select(item => item.Id));
Assert.Equal([3L, 5L], secondPage.Select(item => item.Id));
Assert.Empty(firstPage.Select(item => item.Id)
.Intersect(secondPage.Select(item => item.Id)));
}
[Fact]
public async Task KeysetPages_DuplicateSequenceValues_DoNotOverlap()
{
await using var database = await TestCatalogDatabase.CreateAsync();
await using var context = database.CreateContext();
var specification = ProductSearchSpecification.Create(
new ProductSearchRequest(
null,
null,
null,
null,
"newest",
1,
2,
false));
var firstPage = await ProductSearchQueries.SearchNextAsync(
context,
specification,
null,
CancellationToken.None);
var secondPage = await ProductSearchQueries.SearchNextAsync(
context,
specification,
firstPage.NextCursor,
CancellationToken.None);
Assert.Equal([2L, 1L], firstPage.Items.Select(item => item.Id));
Assert.Equal([3L, 4L], secondPage.Items.Select(item => item.Id));
Assert.Empty(firstPage.Items.Select(item => item.Id)
.Intersect(secondPage.Items.Select(item => item.Id)));
}
[Fact]
public async Task ExactCountAndRows_ExecuteTwoReaderCommands()
{
await using var database = await TestCatalogDatabase.CreateAsync();
await using var context = database.CreateContext();
var specification = ProductSearchSpecification.Create(
new ProductSearchRequest(
null,
null,
null,
true,
"price",
1,
2,
true));
database.Counter.Reset();
var page = await ProductSearchQueries.SearchOffsetAsync(
context,
specification,
CancellationToken.None);
Assert.Equal(4, page.TotalCount);
Assert.Equal(2, page.Items.Count);
Assert.Equal(2, database.Counter.Count);
}
}
internal sealed class TestCatalogDatabase : IAsyncDisposable
{
private readonly SqliteConnection _connection;
private readonly DbContextOptions<CatalogDbContext> _options;
private TestCatalogDatabase(
SqliteConnection connection,
DbContextOptions<CatalogDbContext> options,
ReaderCommandCounter counter)
{
_connection = connection;
_options = options;
Counter = counter;
}
public ReaderCommandCounter Counter { get; }
public static async Task<TestCatalogDatabase> CreateAsync()
{
var connection = new SqliteConnection("Data Source=:memory:");
await connection.OpenAsync();
var counter = new ReaderCommandCounter();
var options = new DbContextOptionsBuilder<CatalogDbContext>()
.UseSqlite(connection)
.AddInterceptors(counter)
.Options;
await using var context = new CatalogDbContext(options);
await context.Database.EnsureCreatedAsync();
context.Products.AddRange(
new Product(1, "Alpha", 10m, true, 100),
new Product(2, "Alpha", 11m, true, 100),
new Product(3, "Beta", 12m, true, 99),
new Product(4, "Gamma", 13m, true, 98),
new Product(5, "Delta", 14m, false, 97));
await context.SaveChangesAsync();
counter.Reset();
return new TestCatalogDatabase(connection, options, counter);
}
public CatalogDbContext CreateContext()
{
return new CatalogDbContext(_options);
}
public async ValueTask DisposeAsync()
{
await _connection.DisposeAsync();
}
}
These are relational tests, but SQLite is still not the production provider for many applications. Microsoft's testing strategy guidance explains that providers can differ in translation and database behavior. Run equivalent tests against the production provider before claiming that text search, ordering, cursor comparisons, counts, or index use behave the same way.
The LINQ projection guide is relevant here because pagination should project the bounded rows into the result contract before materialization. The test's ToQueryString() call provides debugging evidence, while actual enumeration proves provider execution.
Balance the Design Tradeoffs
This query envelope has clear advantages:
- The accepted filters and sorts are reviewable.
- Query values remain parameters rather than executable input.
- Every paged query has fully unique ordering.
- Page size is bounded before query construction.
- Offset and keyset navigation have separate, explicit contracts.
- Exact count behavior is optional and visible.
- Cancellation reaches terminal async operations.
It also has costs:
- Every new filter or sort requires code and tests.
- Offset and keyset models may both be needed.
- Cursor versioning becomes part of the API contract.
- Production-provider tests require database infrastructure.
- Provider-specific text behavior may require a dedicated search design.
Those costs are preferable to pretending a general-purpose dynamic expression endpoint is simple. The allowlist creates a stable boundary. It says which query shapes the application supports and which ones it refuses.
Frequently Asked Questions
Why not accept a property name and build OrderBy with reflection?
An allowlisted switch gives each sort known type semantics, direction, tie-breaker, indexes, and tests. Reflection can be constrained, but accepting arbitrary properties broadens the public query surface and makes translation and deterministic ordering harder to reason about.
Should page sizes be clamped or rejected?
Either policy can work if it is documented. Rejection makes invalid requests visible and keeps tests unambiguous. Clamping can be friendlier for public clients but may hide a caller bug. The important rule is that the executed query remains bounded.
Is keyset pagination always better than offset pagination?
Does a compiled predicate make EF Core filtering faster?
Compiling an expression produces a delegate for in-memory execution. It removes the expression tree EF Core needs for provider translation. Keep the expression on IQueryable<T> and reserve compiled delegates for isolated in-memory tests.
Should every response include an exact total count?
Only when the product needs it. Exact counts require another query in this design. Infinite scrolling and next-page interfaces can often use the extra-row HasMore approach instead.
Keep Dynamic Queries Bounded and Deterministic
A safe EF Core pagination specification does not accept arbitrary query code. It accepts known values, maps them to known filters and sort modes, appends a unique tie-breaker, bounds the page size, and leaves execution visible.
Offset pagination and keyset pagination solve different navigation problems. Exact counts solve a separate display problem. Cancellation belongs on terminal operations. Relational tests prove translation for the tested provider, while production-provider validation proves the behavior that production actually depends on.
That is the useful form of dynamic querying: flexible enough for a real list or search screen, but constrained enough that every supported query shape can be understood and tested.