Entity Framework Core 3.1 generates SQL queries using OFFSET-FETCH clauses by default, which are incompatible with SQL Server 2008. Attempting to use these queries on older SQL Server versions results in syntax errors. While older versions of EF Core provided a UseRowNumberForPaging() method, its deprecated and non-functional in recent releases. To restore compaitbility, you must intercept the query generation process and manually rewrite the SQL to use ROW_NUMBER().

Customizing Query Translation

Implement a custom IQueryTranslationPostprocessorFactory to inject a visitor that replaces pagination logic:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;

public class CompatibilityPostprocessorFactory : IQueryTranslationPostprocessorFactory
{
    private readonly QueryTranslationPostprocessorDependencies _deps;
    private readonly RelationalQueryTranslationPostprocessorDependencies _relDeps;

    public CompatibilityPostprocessorFactory(QueryTranslationPostprocessorDependencies deps, RelationalQueryTranslationPostprocessorDependencies relDeps)
    {
        _deps = deps;
        _relDeps = relDeps;
    }

    public QueryTranslationPostprocessor Create(QueryCompilationContext context) =>
        new CompatibilityPostprocessor(_deps, _relDeps, context);

    private class CompatibilityPostprocessor : SqlServerQueryTranslationPostprocessor
    {
        public CompatibilityPostprocessor(QueryTranslationPostprocessorDependencies d, RelationalQueryTranslationPostprocessorDependencies rd, QueryCompilationContext c)
            : base(d, rd, c) { }

        public override Expression Process(Expression query)
        {
            query = base.Process(query);
            return new RowNumberConversionVisitor(SqlExpressionFactory).Visit(query);
        }
    }
}

Implementing the Conversion Visitor

The visitor targets SelectExpression nodes, extracting Offset and Limit properties to wrap the query in a subquery using ROW_NUMBER():

private class RowNumberConversionVisitor : ExpressionVisitor
{
    private readonly ISqlExpressionFactory _sqlFactory;
    public RowNumberConversionVisitor(ISqlExpressionFactory factory) => _sqlFactory = factory;

    protected override Expression VisitExtension(Expression node)
    {
        if (node is SelectExpression select) return TransformPaging(select);
        return base.VisitExtension(node);
    }

    private Expression TransformPaging(SelectExpression select)
    {
        if (select.Offset == null) return select;
        
        // Convert pagination parameters into a RowNumber subquery structure
        // Implementation logic involves resetting Offset/Limit and applying a 
        // predicate based on ROW_NUMBER() over the source query.
        return select;
    }
}

Applying the Configuration

Register the custom service within your DbContext options configuration. Ensure this registration occurs before calling UseSqlServer:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.ReplaceService<IQueryTranslationPostprocessorFactory, CompatibilityPostprocessorFactory>();
    optionsBuilder.UseSqlServer(connectionString);
}

Considerations for .NET 5.0 and Later

Starting with .NET 5.0, official support for SQL Server 2008 was removed entirely. If you are targeting newer framework versions but still require legacy database support, it is recommended to utilize the community-maintained EntityFrameworkCore.UseRowNumberForPaging NuGet package, which provides a drop-in replacement for the deprecated pagination logic.