ASP.NET Core’s request pipeline is a powerful concept built entirely around middleware. Think of it as an assembly line for your HTTP requests. Each station on the line is a piece of middleware that can inspect, modify, or act upon the request before passing it to the next station.

While ASP.NET Core provides a rich set of built-in middleware for things like routing, authentication, and static files, there will inevitably come a time when you need to create your own. Whether it’s for custom logging, header manipulation, or a unique authentication scheme, writing custom middleware is a fundamental skill.

In this guide, we’ll break down the process into its simplest form, showing you two minimal ways to create and use your own middleware.

Approach 1: The Convention-Based Class

The most common and structured way to create middleware is by defining a class that follows a specific convention. This approach is clean, reusable, and testable.

A middleware class needs two things:

1. A constructor that accepts a RequestDelegate parameter. This delegate represents the next piece of middleware in the pipeline.
2. A public method named InvokeAsync (or Invoke) that accepts an HttpContext as its first parameter. This is the method that gets executed.

Let’s create a simple middleware that logs the incoming request path and the outgoing response status code.

Step 1: Create the Middleware Class

Create a new file named SimpleLoggingMiddleware.cs. We’ll use a primary …

Your EF Core unit tests are passing beautifully. You’re using the in-memory provider, tests run fast, and everything looks green. Then you deploy to production and suddenly encounter foreign key violations, constraint errors, and mysterious data consistency issues that your tests never caught.

The EF Core in-memory provider is a testing trap. It provides false confidence by behaving differently from real databases in critical ways.

The In-Memory Provider Problem

The in-memory provider seems perfect for testing:

[Test]
public async Task CreateOrder_ShouldSaveSuccessfully()
{
    // Arrange
    var options = new DbContextOptionsBuilder<OrderContext>()
        .UseInMemoryDatabase(databaseName: "TestDatabase")
        .Options;
        
    using var context = new OrderContext(options);
    var order = new Order { CustomerId = 999, Total = 100.00m }; // Invalid customer ID
    
    // Act
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    
    // Assert
    var savedOrder = await context.Orders.FindAsync(order.Id);
    Assert.IsNotNull(savedOrder);
    Assert.AreEqual(100.00m, savedOrder.Total);
}

This test passes even though CustomerId = 999 doesn’t exist. In a real database with foreign key constraints, this would fail with a constraint violation.

What the In-Memory Provider Doesn’t Enforce

Foreign Key Constraints:

// This passes in-memory but fails in SQL Server
var order = new Order { CustomerId = 999 }; // Non-existent …

Your e-commerce system has different types of products: physical products, digital downloads, and subscriptions. Each type has common properties (name, price) and specific properties (shipping weight for physical, download URL for digital, billing frequency for subscriptions).

How you map this inheritance hierarchy to SQL Server tables dramatically affects query performance, storage efficiency, and maintainability.

The Three Inheritance Strategies

EF Core provides three approaches to map inheritance hierarchies to relational databases:

Table-per-Hierarchy (TPH)

All types stored in a single table with a discriminator column

Table-per-Type (TPT)

Shared properties in base table, specific properties in derived tables

Table-per-Concrete-Class (TPC)

Each concrete type gets its own table with all properties

Let’s examine each approach with a practical example.

Domain Model Setup

public abstract class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

public class PhysicalProduct : Product
{
    public double Weight { get; set; }
    public string Dimensions { get; set; }
    public decimal ShippingCost { get; set; }
    public int StockQuantity { get; set; }
}

public class DigitalProduct : Product
{
    public string DownloadUrl { get; set; }
    public long FileSizeBytes { get; set …

Your Customer entity has 47 properties. Most queries only need the basic information: name, email, and contact details. But every time you load a customer, EF Core pulls all 47 columns from the database, including the large ProfileData JSON blob that’s rarely used.

This is where table splitting can help: split your large entity into multiple classes that map to the same table, loading only what you need when you need it.

But table splitting is a double-edged sword. Use it wrong, and you’ll make performance worse, not better.

Understanding Table Splitting

Table splitting maps multiple entity classes to the same database table:

// Single table with many columns
CREATE TABLE Customers (
    Id int PRIMARY KEY,
    Name nvarchar(100),
    Email nvarchar(200),
    Phone nvarchar(20),
    -- Frequently accessed columns above
    
    ProfileData nvarchar(max),
    Preferences nvarchar(max), 
    MarketingMetadata nvarchar(max),
    LastLoginDetails nvarchar(max),
    -- Rarely accessed columns below
    CreatedAt datetime2,
    ModifiedAt datetime2
);

Instead of one large entity, you create multiple focused entities:

// Frequently accessed data
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    
    // Navigation to extended data
    public CustomerExtended Extended { get; set; }
}

// Infrequently accessed data
public class CustomerExtended
{ …

Your EF Core LINQ query for monthly sales reporting takes 45 seconds to execute. You rewrite it as a stored procedure and it runs in 3 seconds. But now your business logic is split between C# and SQL, and your team is wondering if they made the right choice.

The decision between EF Core LINQ and stored procedures isn’t black and white. Each approach has strengths that match different scenarios.

When EF Core LINQ Excels

EF Core handles most database operations beautifully with clean, maintainable code:

// Clean, readable, and performs well
var customerOrders = await context.Customers
    .Where(c => c.Country == "USA")
    .Include(c => c.Orders.Where(o => o.OrderDate >= DateTime.Now.AddMonths(-3)))
    .Select(c => new CustomerOrderSummary
    {
        CustomerId = c.Id,
        CustomerName = c.Name,
        RecentOrderCount = c.Orders.Count(),
        TotalAmount = c.Orders.Sum(o => o.Total)
    })
    .ToListAsync();

EF Core generates efficient SQL for this type of query:

SELECT [c].[Id], [c].[Name], 
       COUNT([o].[Id]) AS RecentOrderCount,
       COALESCE(SUM([o].[Total]), 0.0) AS TotalAmount
FROM [Customers] AS [c]
LEFT JOIN [Orders] AS [o] ON [c].[Id] = [o].[CustomerId] 
    AND [o].[OrderDate] >= @__AddMonths_0
WHERE [c].[Country] = N'USA'
GROUP BY [c].[Id], [c].[Name]

This performs well and keeps all logic in your application layer.

When to Consider Stored Procedures

Complex Business Logic with Multiple Steps

Some …

You’re looking at a SQL Server execution plan that looks like a spider web of boxes and arrows. Your EF Core query takes 800ms, but you have no idea where to start optimizing.

Execution plans contain overwhelming detail, but EF Core developers only need to focus on specific elements. Here’s what actually matters for optimizing your LINQ queries.

Getting Execution Plans from EF Core

First, you need to capture the SQL that EF Core generates:

// Enable query logging to see generated SQL
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging(); // Shows parameter values
}

Run your EF Core query and copy the logged SQL:

var expensiveOrders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .Where(o => o.OrderDate >= DateTime.Now.AddDays(-30))
    .ToListAsync();

EF Core generates SQL like this:

SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[Total],
       [c].[Id], [c].[Name], [c].[Email],
       [o0].[Id], [o0].[OrderId], [o0].[ProductId], [o0].[Quantity],
       [p].[Id], [p].[Name], [p].[Price]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
LEFT JOIN [OrderItems] AS [o0] ON [o].[Id] = [o0].[OrderId]
LEFT JOIN [Products] AS [p] ON [o0].[ProductId] = [p].[Id]
WHERE [o …

Your API handles 50,000 requests per second, and each request executes the same customer lookup query. Profiling shows 15% of CPU time is spent translating LINQ expressions to SQL, not actually running the database query.

This is where EF Core compiled queries shine. They pre-compile the LINQ-to-SQL translation, eliminating that CPU overhead for frequently executed queries.

But compiled queries aren’t a magic performance solution. They have specific use cases and trade-offs that many developers misunderstand.

How EF Core Query Compilation Works

Every time you execute a LINQ query, EF Core goes through several steps:

1. Parse the LINQ expression tree
2. Translate LINQ to database-specific SQL
3. Cache the translation result
4. Execute the SQL query

// Normal query - goes through full pipeline every time
var customer = await context.Customers
    .Where(c => c.Id == customerId)
    .Include(c => c.Orders)
    .FirstOrDefaultAsync();

EF Core caches query plans, but complex LINQ expressions still require parsing and validation on each execution. For simple queries executed thousands of times per second, this overhead becomes significant.

Compiled Query Implementation

Compiled queries pre-compile the LINQ expression, skipping steps 1-2 on subsequent executions:

public static class CompiledQueries
{
    // Compiled query with single parameter
    public static readonly Func<CustomerContext, int, Task<Customer>> GetCustomerById =
        EF.CompileAsyncQuery(( …

Your e-commerce site processes millions in revenue daily. A simple database migration that takes your site offline for 10 minutes costs thousands in lost sales and damages customer trust.

Traditional migration approaches require downtime: stop the application, run migrations, restart with new code. Zero-downtime migrations eliminate this disruption using careful planning and deployment strategies that keep your application running throughout database updates.

The Zero-Downtime Challenge

Standard EF Core migrations assume a simple deployment model:

1. Stop application
2. Run dotnet ef database update
3. Deploy new application code
4. Start application

This works for small applications but creates unacceptable downtime for production systems. The challenge is that database schema changes often require corresponding application code changes, creating a chicken-and-egg problem.

Backward-Compatible Migration Patterns

Zero-downtime migrations require backward compatibility: the database must work with both old and new application versions during the transition.

Adding New Tables

Adding new tables is always safe:

public partial class AddOrderHistoryTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "OrderHistory",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", …

Your team of 8 developers is working on different features. Developer A adds a new table, Developer B modifies an existing column, Developer C renames a property. All three create migrations on the same day.

When you try to merge everything together, EF Core explodes with migration conflicts, database schema mismatches, and mysterious errors about missing tables. Sound familiar?

Large teams need structured approaches to EF Core migrations, or you’ll spend more time fixing database issues than building features.

The Migration Conflict Problem

EF Core migrations are sequential by design. Each migration has a timestamp-based filename and builds on the previous one:

20250923_081234_AddCustomerTable.cs
20250923_091456_AddOrderTable.cs      // Depends on Customer table
20250923_095612_AddCustomerEmail.cs   // Also modifies Customer table

When multiple developers create migrations simultaneously, you get parallel branches that can’t merge cleanly:

Feature Branch A: AddCustomerTable -> AddCustomerEmail
Feature Branch B: AddCustomerTable -> AddOrderTable
Feature Branch C: AddCustomerTable -> ModifyCustomerName

Merging these creates a broken migration history where later migrations reference schema changes that don’t exist in the merged timeline.

Team Workflow Strategy

1. Feature Branch Guidelines

Each developer works in feature branches with clear migration rules:

# Developer starts new feature
git checkout -b feature/customer-management
dotnet ef migrations …

You add a RowVersion property to your entity, mark it with [Timestamp], and think you’re done with concurrency control. Then production hits: concurrency exceptions aren’t caught properly, API clients can’t handle the binary data, and you’re not sure if your timestamps are working.

SQL Server’s rowversion is powerful but has specific quirks that can trip up EF Core developers. Here’s how to implement it correctly.

Understanding SQL Server RowVersion

SQL Server’s rowversion is not a timestamp despite the old name. It’s an 8-byte binary value that automatically increments whenever any column in the row changes:

CREATE TABLE [Products] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(100) NOT NULL,
    [Price] decimal(18,2) NOT NULL,
    [RowVersion] rowversion NOT NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY ([Id])
);

Every time you UPDATE this row, SQL Server automatically changes the RowVersion value. You never set it manually - the database engine handles everything.

EF Core RowVersion Implementation

The correct way to implement rowversion in EF Core:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    
    [Timestamp] // This maps to SQL Server rowversion
    public byte[] RowVersion { get; set; }
}

The [Timestamp] attribute tells EF Core this is a concurrency token that’s automatically generated by the database. EF Core …

Two users edit the same customer record simultaneously. User A updates the phone number, User B changes the address. User A saves first, then User B saves. Result: the phone number update disappears.

This is the classic lost update problem, and it happens more often than you think in production web applications. EF Core concurrency tokens solve this elegantly without the complexity of database locks.

The Lost Update Problem

Without concurrency control, this scenario plays out daily in production:

// User A loads customer
var customer = await context.Customers.FindAsync(customerId);
customer.Phone = "555-0123";

// User B loads the same customer (before A saves)
var customer2 = await context.Customers.FindAsync(customerId);
customer2.Address = "123 New Street";

// User A saves first
await context.SaveChangesAsync(); // Phone updated

// User B saves second  
await context.SaveChangesAsync(); // Address updated, but phone reverted!

User B’s save overwrote User A’s phone number change because EF Core generated an UPDATE statement based on the original values User B loaded. The phone number silently reverted to its original value.

Concurrency Tokens to the Rescue

Concurrency tokens enable optimistic concurrency control. EF Core includes the token value in UPDATE and DELETE statements, ensuring the operation only succeeds if no one else modified the record:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } …

Your application works perfectly in development, but in production, you’re seeing partial data updates and mysterious consistency issues. The problem isn’t your business logic, it’s how you’re handling transactions.

EF Core gives you two ways to manage transactions: the automatic approach with SaveChanges, and explicit transaction control. Each has its place, but using the wrong one can cost you data integrity or performance.

How EF Core SaveChanges Handles Transactions

SaveChanges automatically wraps all your changes in a single database transaction. This means multiple entity operations either all succeed or all fail together:

// This entire operation is one transaction
using var context = new OrderContext();

var customer = new Customer { Name = "John Doe" };
var order = new Order { CustomerId = customer.Id, Total = 100.50m };
var orderItem = new OrderItem { OrderId = order.Id, ProductId = 1 };

context.Customers.Add(customer);
context.Orders.Add(order);
context.OrderItems.Add(orderItem);

await context.SaveChangesAsync(); // All or nothing

If any part fails, everything rolls back. Your database stays consistent without any extra code.

When SaveChanges Transactions Aren’t Enough

Multiple SaveChanges Calls

The automatic transaction only covers a single SaveChanges call. If your business logic requires multiple save operations, you need explicit control:

// Problem: Two separate transactions
public async Task ProcessOrderAsync(Order …

Multi-tenant SaaS applications face a critical decision: how to isolate tenant data while maintaining performance and security. One wrong move and Tenant A sees Tenant B’s customer records. I’ve seen this exact scenario destroy customer trust overnight.

The connection-per-tenant approach offers strong isolation but introduces complexity that can sink your architecture if not handled correctly. Let’s look at the real-world patterns, pitfalls, and production-ready solutions.

The Multi-Tenant Connection Problem

Most SaaS applications start simple: one database, one connection string, everything shared. This works until you need compliance certifications, enterprise customers demand data isolation, or you face your first security audit.

Common Early Mistakes

Shared Connection String Chaos: Using the same connection string for all tenants with TenantId filtering sounds efficient. Until someone forgets a WHERE clause and exposes everything.

Per-Tenant Schema Confusion: Mapping different schemas in EF Core without proper context switching leads to runtime errors that are painful to debug.

Security Breach Example: I’ve consulted on incidents where a missing global query filter resulted in tenant data leakage. The fix took hours, but the damage to customer relationships lasted months.

Multi-Tenant Database Design Patterns

Here are three proven approaches for handling tenant connections, each with distinct trade-offs:

Database Per Tenant (Full Isolation) …

In one of our enterprise SaaS projects last year, we discovered a critical bug during a routine audit. Customer A could see order data belonging to Customer B when filtering by a specific date range. The root cause? A missing WHERE TenantId = @tenantId clause in a complex reporting query.

This wasn’t just embarrassing. It was a potential GDPR violation that could have resulted in significant penalties. That incident taught us that manual tenant filtering is prone to human error, especially in large codebases with multiple developers.

EF Core’s global query filters solved this problem by automatically applying tenant isolation at the ORM level. Here’s how we implemented bulletproof tenant isolation that passes SOC2 and HIPAA compliance requirements.

The Multi-Tenant Entity Foundation

Every entity in our system includes a TenantId property. This isn’t optional, it’s the foundation of data isolation.

public class Order
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string CustomerName { get; set; }
    public decimal Amount { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

The TenantId is resolved through dependency injection using a tenant provider service:

public interface ITenantProvider
{
    Guid TenantId { get; }
}

public …

A few years back, I spent a solid week chasing a weird performance bug. Queries that should have taken milliseconds were sometimes taking seconds to run. The culprit? A simple string property on a core entity. Nobody had set a max length, so EF Core defaulted it to nvarchar(max). SQL Server handles those columns differently, and it was wrecking our query plans.

This one burned me in production, and it taught me a hard lesson: your entity model is the foundation of your application’s performance. Get it wrong, and you’ll pay for it with slow queries and painful migrations.

This checklist is the result of modeling dozens of schemas for EF Core on SQL Server. I’m focusing on SQL Server here because its defaults and indexing behaviors are unique.

Getting the Basics Right: The Entity Itself

Let’s start with the entity class. If you mess this up, everything else gets more complicated.

• Primary Keys: Just use Id or [EntityName]Id. Don’t get clever. I avoid composite keys unless the domain model is impossible without them. They make joins and repository logic a pain to write and maintain.

  // Good: Simple, clean, effective.
  public int Id { get; private set; }
  
  // Avoid: This complicates everything.
  public class OrderItemKey
  {
      public int OrderId { get; set; }
      public int ProductId { get; set; }
  }
  

• Value Objects: For data that has no identity on its own, use Owned Types. An Address or a Money value doesn’t need its own Id. It belongs to its parent. This …

I once inherited a codebase where every value object was an EF Core Owned Type. The C# models looked clean, but the main Orders table in SQL Server had over 150 columns. Queries were timing out, and simple reports were a nightmare to build.

The culprit? A well-intentioned feature used in the wrong way.

Owned Types are a great tool, but if you don’t understand how they translate to SQL Server, you can create a maintenance headache that’s hard to untangle. After using them in production for years, I’ve learned where they shine and where they just cause pain.

This is my practical take for anyone using EF Core 8+ with SQL Server.

What’s actually happening under the hood?

Owned Types let you group related properties in your C# code without creating a separate table in the database. Think of an Address object on a Customer. In your code, it’s a neat, self-contained object. In the database, EF Core flattens it right into the Customers table.

It’s a way to keep your domain model expressive without cluttering your database schema with tiny tables and foreign keys for things that don’t need their own identity.

The simplest example

Here’s the classic Address value object stored with a Customer.

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address BillingAddress { get; set; }
}

[Owned]
public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public …

Three months into a new multi-tenant SaaS project, we shipped a simple delete feature. Hours later, our support channels lit up. Customers were confused—they’d delete an invoice, but it would mysteriously reappear in their quarterly reports.

The bug was a classic. A single, forgotten Where clause. A reporting query, written by someone new to the domain, didn’t include our manual !IsDeleted filter. Boom. Soft-deleted records were leaking straight into the UI.

That incident taught me a hard lesson: manual soft deletes are a ticking time bomb. EF Core’s global query filters are the only way I’ll implement them now. They’re not just a convenience; they’re a safety net.

The ‘Just Add an IsDeleted Flag’ Trap

Everyone starts here. You add an IsDeleted property to your entity and tell the team, “Just remember to filter it out.” It works, for a while.

// This works... until it doesn't.
var activeUsers = context.Users
    .Where(u => !u.IsDeleted)
    .ToList();

// Oops. A developer writing a new report forgets the filter.
var userReport = context.Users
    .Include(u => u.Orders)
    .ToList(); // Deleted users are back from the dead.

In a growing codebase with multiple developers, someone will forget the Where clause. It’s inevitable. In our case, it wasn’t just a weird bug; it broke customer trust. They thought our software was unreliable.

EF Core’s Global Query Filters to the Rescue

This is where you stop …

The difference between a One-to-One and a One-to-Many relationship in Entity Framework Core isn’t just academic. On my last project, choosing the wrong one led to two weeks of painful migrations, late nights, and a whole lot of dotnet ef database update anxiety.

It started with a simple feature: each tenant in our multi-tenant app needed a settings object. A Tenant has one Settings. Simple. So, we modeled it as a One-to-One relationship. What could go wrong?

Six months later, the requirements changed. Now, the business wanted an audit trail. They needed to see who changed a setting and when. Suddenly, our single Settings record per tenant was a huge problem. We needed a history, a collection of settings over time. Our rigid One-to-One schema was now a roadblock, and I was the one who had to fix it.

This experience taught me a hard lesson: your data model has to account for the future, not just the current sprint.

The Basics: What’s the Actual Difference?

Most of us get the concept, but let’s quickly recap how EF Core sees them.

A One-to-One relationship is a tight coupling. Think User and UserProfile. A user has exactly one profile. The profile can’t exist without the user. In the database, the primary key of UserProfile is also its foreign key back to User.

// The principal entity
public class User
{
    public int Id { get; set; }
    public string Username { get; set; }

    // Navigation property to the dependent
    public UserProfile Profile { …

I once inherited a dashboard page that took over five seconds to load. The API endpoint behind it looked innocent enough, but digging in, I found a classic case of death by a thousand cuts: lazy loading, bloated entities, and chatty database calls. It was a textbook example of default EF Core behavior backfiring under real-world load.

After fixing that mess (and many others like it), I’ve developed a small playbook of go-to optimizations. These aren’t wild, complex tricks. They’re five fundamental patterns that I apply to almost every high-traffic ASP.NET Core project.

Here’s what I do to keep my data layers fast and lean.

1. Ditch Full Entities, Project to DTOs

This one is my golden rule for read queries. If you’re just displaying data, stop loading full-blown EF Core entities.

The problem is that when you pull an entity, EF Core has to hydrate every single property. Even the ones you don’t need. This results in bigger SQL queries that join more tables and pull back way more data than your API client will ever see.

It’s pure waste.

Instead, use Select to project directly into a Data Transfer Object (DTO).

// The slow way
var users = await _context.Users
    .Include(u => u.Profile) // Pulls everything from two tables
    .ToListAsync();

// The fast, clean way
var users = await _context.Users
    .Select(u => new UserSummaryDto 
    {
        Id = u.Id,
        Name = u.Name,
        Email = u.Email
    })
    .ToListAsync();

On a …

Last month, our customer portal started timing out. Users browsing past page 1,000 of their order history were met with a dreaded loading spinner that never went away. A quick look at the logs confirmed it: our classic offset-based pagination was hitting a table with 2.3 million orders, and query times had jumped from 50ms to over 4 seconds. SQL Server was burning up CPU cycles.

The culprit was Skip() and Take(). This one has burned me in production more than once. The fix was switching to keyset pagination (sometimes called cursor-based pagination), a technique that uses the last seen value to find the next page instead of counting rows to skip.

Here’s how it works and how to implement it properly in EF Core.

Why Skip() and Take() Grind to a Halt

Standard pagination in LINQ uses Skip(page * pageSize).Take(pageSize). Under the hood, this translates to SQL’s OFFSET and FETCH NEXT. For SQL Server to skip 50,000 rows, it has to actually read all 50,000 rows first.

Here’s the generated SQL for fetching page 2,501 (with a page size of 20):

-- This gets painfully slow as the offset increases
SELECT * FROM Orders 
ORDER BY OrderId 
OFFSET 50000 ROWS FETCH NEXT 20 ROWS ONLY;

Even with a perfect index on OrderId, the database still has to scan 50,020 rows from the index just to return the final 20. It’s an enormous amount of wasted work that gets worse with every page you click.

The Fix: Keyset Pagination (The “Seek Method”)

Instead of telling the …