Introduction

Every .NET developer who works with medium or large applications has run into at least one of these scenarios: a nightly routine that needs to write 50 thousand orders to the database, a report query that brings 300 thousand records at once and freezes the application, or an API that takes 8 seconds to respond because EF Core is executing 10 thousand individual INSERT statements in sequence.

SQL Server and Oracle are robust and mature databases, but neither was designed to receive thousands of writes in a burst, nor to return hundreds of thousands of rows at once at no cost. In most cases, the problem is not the database β€” it is the way the C# application interacts with it.

In this article I explore both faces of the bottleneck: bulk writes and reading large volumes. For each one, you will see the root cause, the particularities of SQL Server and Oracle with EF Core 8.0+, and the practical solution β€” messaging for writes and efficient pagination for reads. All the code is production-ready and compatible with .NET 8/9.

Prerequisites: Basic knowledge of C# and EF Core. I recommend reading the article on asynchronous programming with C# before continuing.

πŸ“¦ Source code: The complete implementation of this article is in the blog-zocateli-sample repository on GitHub. Clone it, explore it and adapt it to your context.


The Write Bottleneck: Why Writing Thousands of Records Hurts

The Real Cost of Each INSERT

When you save a list of entities with EF Core in the most common way, something like this happens:

1
2
3
4
5
6
// ❌ Naive approach β€” InsertOne at a time
foreach (var pedido in listaDe50MilPedidos)
{
    await context.Pedidos.AddAsync(pedido);
    await context.SaveChangesAsync(); // PROBLEM: 1 roundtrip per record!
}

Each SaveChangesAsync() inside the loop represents:

  1. A transaction opened β†’ committed β†’ closed in the database
  2. A network roundtrip (1–5ms latency per call)
  3. The database log being written for each operation
  4. Row locks being allocated and released 50 thousand times

For 50,000 orders with 2ms of latency per roundtrip, that means 100 seconds of pure I/O processing. And that is the best-case scenario, with no contention.

Particularities: SQL Server vs Oracle

Even though the solution is similar in both databases, there are important differences:

AspectSQL ServerOracle
Native bulk insertBULK INSERT / SqlBulkCopyODP.NET BulkCopy / INSERT ALL
Default maximum batch size1,000 rows per INSERTDepends on ArrayBindCount
Sequences / IdentityIDENTITY or SEQUENCEOnly SEQUENCE (mandatory)
Bulk rollbackCan be minimally loggedAlways logged (redo log)
EF Core providerMicrosoft.EntityFrameworkCore.SqlServerOracle.EntityFrameworkCore

In Oracle, a critical detail is that the official provider (Oracle.EntityFrameworkCore 8.x) requires you to configure key generation via SEQUENCE + TRIGGER (or GENERATED ALWAYS AS IDENTITY in Oracle 12c+). Ignoring this in bulk writes will generate N extra calls to the database just to obtain the IDs.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// Oracle: correct configuration in OnModelCreating
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Pedido>(entity =>
    {
        entity.HasKey(e => e.Id);

        // Oracle 12c+: native identity, avoids an extra roundtrip per ID
        entity.Property(e => e.Id)
              .UseIdentityColumn();

        // Alternative for Oracle 11g/legacy
        // entity.Property(e => e.Id)
        //       .HasDefaultValueSql("SEQ_PEDIDOS.NEXTVAL");
    });
}

The Immediate Solution: Batched SaveChanges with EF Core 8

Before introducing queues, the first optimization is to move SaveChangesAsync() out of the loop and use chunks so as not to overload the context:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// βœ… Better: batch by chunk + 1 SaveChanges per batch
public async Task GravarPedidosEmLoteAsync(
    IEnumerable<Pedido> pedidos,
    CancellationToken ct = default)
{
    const int tamanhoLote = 500;

    foreach (var chunk in pedidos.Chunk(tamanhoLote))
    {
        await context.Pedidos.AddRangeAsync(chunk, ct);
        await context.SaveChangesAsync(ct);

        // Clear the ChangeTracker so entities don't accumulate in memory
        context.ChangeTracker.Clear();
    }
}

πŸ’‘ Tip: The Chunk() method was introduced in .NET 6. Combined with ChangeTracker.Clear(), it prevents the EF Core context from growing indefinitely while tracking tens of thousands of entities.

ExecuteInsertAsync and BulkInsert in EF Core 8

EF Core 7 brought ExecuteUpdateAsync and ExecuteDeleteAsync. EF Core 8 improved support for bulk operations. For extremely high-performance scenarios, use the EFCore.BulkExtensions library:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// βœ… BulkInsert for SQL Server and Oracle (via EFCore.BulkExtensions)
// dotnet add package EFCore.BulkExtensions
public async Task BulkInsertPedidosAsync(
    List<Pedido> pedidos,
    CancellationToken ct = default)
{
    var bulkConfig = new BulkConfig
    {
        BatchSize = 1000,
        UseTempDB = true,              // SQL Server: temporary table for staging
        SetOutputIdentity = true,      // Fills in the IDs generated by the database
        PreserveInsertOrder = true
    };

    await context.BulkInsertAsync(pedidos, bulkConfig, cancellationToken: ct);
}

With BulkInsert, 50,000 records that took 100 seconds with individual SaveChanges are written in 2–4 seconds on SQL Server, and in 3–6 seconds on Oracle.


Messaging: The Architectural Solution for Bulk Writes

Optimizing the INSERT itself treats the symptom, but not the root cause. The real problem is that the application is trying to process a huge volume synchronously, blocking the thread and the request while it writes. The correct architectural solution is to decouple reception from writing using a message queue.

How Messaging Solves the Problem

Instead of writing directly to the database when it receives the data, the application:

  1. Publishes the records to a queue (RabbitMQ, Azure Service Bus, etc.) β€” a fast operation (~1ms per message)
  2. Returns immediately to the client with 202 Accepted
  3. A separate Consumer reads the queue in batches and writes to the database with bulk insert

Messaging architecture for bulk writes to the database

This separation brings benefits beyond performance:

  • Resilience: if the database goes down temporarily, the messages stay in the queue. There is no data loss.
  • Flow control: the consumer can process at the speed the database supports
  • Natural backpressure: the queue absorbs load spikes
  • Observability: you can monitor the queue size and know exactly the pending backlog

Implementation with RabbitMQ.Client

The official RabbitMQ.Client library is 100% open source (Apache 2.0) and provides direct access to the AMQP protocol. Combined with .NET’s BackgroundService, I implement a consumer that accumulates messages in a batch and writes everything at once with BulkInsert.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
// dotnet add package RabbitMQ.Client
// dotnet add package EFCore.BulkExtensions

// --- Message ---
public record PedidoCriadoMessage(
    Guid Id,
    string ClienteId,
    decimal Valor,
    DateTime DataCriacao);

// --- Producer ---
public class PedidoProducer(IConnection connection)
{
    private const string QueueName = "pedidos-queue";

    public async Task PublicarAsync(
        PedidoCriadoMessage mensagem,
        CancellationToken ct = default)
    {
        await using var channel = await connection.CreateChannelAsync(cancellationToken: ct);

        await channel.QueueDeclareAsync(
            queue: QueueName,
            durable: true,
            exclusive: false,
            autoDelete: false,
            cancellationToken: ct);

        var json = JsonSerializer.SerializeToUtf8Bytes(mensagem);
        var props = new BasicProperties { DeliveryMode = DeliveryModes.Persistent };

        await channel.BasicPublishAsync(
            exchange: string.Empty,
            routingKey: QueueName,
            mandatory: false,
            basicProperties: props,
            body: json,
            cancellationToken: ct);
    }
}

// --- Batch consumer (BackgroundService) ---
public class PedidoConsumerWorker(
    IConnection connection,
    IServiceScopeFactory scopeFactory,
    ILogger<PedidoConsumerWorker> logger) : BackgroundService
{
    private const string QueueName = "pedidos-queue";
    private const int TamanhoLote = 500;

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        await using var channel = await connection.CreateChannelAsync(cancellationToken: ct);

        await channel.QueueDeclareAsync(
            queue: QueueName, durable: true,
            exclusive: false, autoDelete: false,
            cancellationToken: ct);

        // Backpressure: limits in-flight messages so memory isn't overloaded
        await channel.BasicQosAsync(
            prefetchSize: 0,
            prefetchCount: TamanhoLote + 100,
            global: false,
            cancellationToken: ct);

        var lote = new List<(ulong Tag, PedidoCriadoMessage Msg)>();

        var consumer = new AsyncEventingBasicConsumer(channel);
        consumer.ReceivedAsync += async (_, ea) =>
        {
            var msg = JsonSerializer.Deserialize<PedidoCriadoMessage>(ea.Body.Span)!;
            lote.Add((ea.DeliveryTag, msg));

            if (lote.Count >= TamanhoLote)
                await ProcessarLoteAsync(channel, lote, ct);
        };

        await channel.BasicConsumeAsync(
            queue: QueueName,
            autoAck: false,
            consumer: consumer,
            cancellationToken: ct);

        // Flush timer: forces processing even if the batch isn't full
        using var timer = new PeriodicTimer(TimeSpan.FromSeconds(5));
        while (await timer.WaitForNextTickAsync(ct))
        {
            if (lote.Count > 0)
                await ProcessarLoteAsync(channel, lote, ct);
        }
    }

    private async Task ProcessarLoteAsync(
        IChannel channel,
        List<(ulong Tag, PedidoCriadoMessage Msg)> lote,
        CancellationToken ct)
    {
        var snapshot = lote.ToList();
        lote.Clear();

        var pedidos = snapshot.Select(x => new Pedido
        {
            Id           = x.Msg.Id,
            ClienteId    = x.Msg.ClienteId,
            Valor        = x.Msg.Valor,
            DataCriacao  = x.Msg.DataCriacao
        }).ToList();

        using var scope    = scopeFactory.CreateScope();
        var dbContext       = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        var bulkConfig      = new BulkConfig { BatchSize = 500 };

        await dbContext.BulkInsertAsync(pedidos, bulkConfig, cancellationToken: ct);

        // Multiple ACK: confirms the whole batch at once
        await channel.BasicAckAsync(
            deliveryTag: snapshot[^1].Tag,
            multiple: true,
            cancellationToken: ct);

        logger.LogInformation("Batch of {Count} orders written", pedidos.Count);
    }
}

Registering in Program.cs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// Program.cs
// dotnet add package RabbitMQ.Client
builder.Services.AddSingleton<IConnection>(_ =>
{
    var factory = new ConnectionFactory
    {
        HostName = "localhost",
        UserName = "guest",
        Password = "guest"
    };
    // CreateConnectionAsync returns a Task β€” I resolve it here for the DI container
    return factory.CreateConnectionAsync().GetAwaiter().GetResult();
});

builder.Services.AddScoped<PedidoProducer>();
builder.Services.AddHostedService<PedidoConsumerWorker>();

⚠️ Warning: On Oracle, EFCore.BulkExtensions’ BulkInsert requires the Oracle Data Provider for .NET (ODP.NET). Make sure you register the correct provider and that the sequences were configured correctly in the model, or the bulk insert will fail with a primary key constraint error.


RabbitMQ vs Azure Service Bus: Which One to Choose?

Both options solve the same problem β€” decoupling message production and consumption β€” but with very different operational and economic models. The choice depends on your context: self-hosted infrastructure vs managed cloud, variable vs fixed cost, and the degree of control you want.

CriterionRabbitMQAzure Service Bus
TypeOpen source (MPL 2.0), self-hostedPaaS managed by Microsoft
ProtocolAMQP 0-9-1 (native), AMQP 1.0, MQTT, STOMPAMQP 1.0
HostingYour own container, VM, KubernetesAzure (no infra to manage)
CostInfrastructure + operation (your team)Pay-per-use (~$0.10/million ops)
QueuesQueues, Exchanges, BindingsQueues and Topics/Subscriptions
Maximum message size128 MB (default)256 KB (Standard) / 100 MB (Premium)
Message retentionUntil the disk fills up / configurable TTL14 days (maximum)
Dead-letter queueβœ… Configurableβœ… Native
Sessions (guaranteed ordering)βœ… Via x-single-active-consumerβœ… Service Bus Sessions
Automatic retryManual (Dead-letter + requeue)Native (MaxDeliveryCount)
Horizontal scalabilityManual (Erlang cluster)Automatic
.NET libraryRabbitMQ.Client (Apache 2.0)Azure.Messaging.ServiceBus (MIT)
Best forOn-premises, multi-cloud, controlled costAzure ecosystem, small team, guaranteed SLA

Equivalent Implementation with Azure Service Bus

To migrate from RabbitMQ to Azure Service Bus in an Azure environment, the producer/consumer pattern is similar, using Azure.Messaging.ServiceBus:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
// dotnet add package Azure.Messaging.ServiceBus

// --- Producer with native batch sending ---
public class PedidoServiceBusProducer(ServiceBusSender sender)
{
    public async Task PublicarLoteAsync(
        IEnumerable<PedidoCriadoMessage> mensagens,
        CancellationToken ct = default)
    {
        // ServiceBusMessageBatch respects the size limit automatically
        using var batch = await sender.CreateMessageBatchAsync(ct);

        foreach (var msg in mensagens)
        {
            var sbMsg = new ServiceBusMessage(
                BinaryData.FromObjectAsJson(msg))
            {
                ContentType = "application/json",
                MessageId   = msg.Id.ToString()
            };

            if (!batch.TryAddMessage(sbMsg))
                throw new InvalidOperationException(
                    $"Message {msg.Id} exceeds the batch limit");
        }

        await sender.SendMessagesAsync(batch, ct);
    }
}

// --- Consumer with BackgroundService ---
public class PedidoServiceBusWorker(
    ServiceBusProcessor processor,
    IServiceScopeFactory scopeFactory,
    ILogger<PedidoServiceBusWorker> logger) : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        processor.ProcessMessageAsync += async args =>
        {
            var msg = args.Message.Body
                          .ToObjectFromJson<PedidoCriadoMessage>();

            using var scope   = scopeFactory.CreateScope();
            var dbContext      = scope.ServiceProvider
                                      .GetRequiredService<AppDbContext>();
            var pedido         = new Pedido
            {
                Id          = msg.Id,
                ClienteId   = msg.ClienteId,
                Valor       = msg.Valor,
                DataCriacao = msg.DataCriacao
            };

            await dbContext.BulkInsertAsync(
                new List<Pedido> { pedido },
                cancellationToken: ct);

            // Confirms processing β€” removes from the queue
            await args.CompleteMessageAsync(args.Message, ct);
        };

        processor.ProcessErrorAsync += args =>
        {
            logger.LogError(args.Exception,
                "Error processing Service Bus message");
            return Task.CompletedTask;
        };

        await processor.StartProcessingAsync(ct);
        await Task.Delay(Timeout.Infinite, ct);
    }

    public override async Task StopAsync(CancellationToken ct)
    {
        await processor.StopProcessingAsync(ct);
        await base.StopAsync(ct);
    }
}

// Program.cs
builder.Services.AddSingleton(provider =>
{
    var client = new ServiceBusClient(
        "Endpoint=sb://meu-namespace.servicebus.windows.net/;...");
    return client.CreateSender("pedidos-queue");
});

builder.Services.AddSingleton(provider =>
{
    var client = new ServiceBusClient(
        "Endpoint=sb://meu-namespace.servicebus.windows.net/;...");
    return client.CreateProcessor("pedidos-queue", new ServiceBusProcessorOptions
    {
        MaxConcurrentCalls   = 4,   // Parallelism in the consumer
        AutoCompleteMessages = false // Manual ACK control
    });
});

builder.Services.AddHostedService<PedidoServiceBusWorker>();

πŸ’‘ Tip: In production on Azure, prefer authentication via Managed Identity instead of a connection string, using new ServiceBusClient("meu-namespace.servicebus.windows.net", new DefaultAzureCredential()). This eliminates secrets from the configuration.


The Read Bottleneck: Why Bringing Everything at Once Is Dangerous

The Problem of the Unbounded Query

1
2
3
4
5
// ❌ NEVER do this in production with large tables
var todosPedidos = await context.Pedidos
    .Include(p => p.Itens)
    .Include(p => p.Cliente)
    .ToListAsync(); // It may bring 500 thousand records into memory!

This seemingly innocent query can:

  • Consume gigabytes of RAM on the application server
  • Block the database with a long-running table scan
  • Generate timeouts in high-concurrency environments
  • Saturate the network between the application and the database with unnecessary traffic
  • Choke the .NET GC with large objects that must go to the LOH (Large Object Heap)

On Oracle, an additional aspect is the use of ROWNUM (Oracle 11g) vs FETCH FIRST … ROWS ONLY (Oracle 12c+), which affects how pagination is expressed in SQL. The Oracle.EntityFrameworkCore provider abstracts this, but it is important to understand what is being generated.

Pagination Types: Offset vs Keyset (Cursor)

πŸ“– Dedicated article: For a complete guide to every pagination strategy (Offset, Keyset, Server-Side Cursor, Time-based and Opaque Token) with SQL Server, Oracle and PostgreSQL β€” including EF Core 8+ code for each combination β€”, see: REST API Pagination with C# and EF Core: Practical Guide. This section presents the essential concepts; the linked article goes deeper into when and why to use each approach.

There are two main pagination strategies, and each has distinct use cases:

CharacteristicOffset PaginationKeyset (Cursor) Pagination
Generated SQLOFFSET N ROWS FETCH NEXT MWHERE id > :lastId
Performance on early pagesβœ… Fastβœ… Fast
Performance on late pages❌ Slow (scan grows)βœ… Constant
Page jump supportβœ… Direct❌ Sequential only
New records between pages❌ May duplicate/omitβœ… Consistent
Ideal use caseUI with page numbersInfinite scroll / APIs

Offset Pagination: Simple and Suitable for UIs

Offset pagination is the most familiar one, and EF Core generates it correctly for SQL Server and Oracle:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
// Standardized request model
public record PaginacaoRequest(int Pagina = 1, int TamanhoPagina = 20)
{
    // Ensures safe limits
    public int TamanhoSeguro => Math.Min(Math.Max(TamanhoPagina, 1), 100);
    public int OffsetSeguro  => (Math.Max(Pagina, 1) - 1) * TamanhoSeguro;
}

// Standardized response model
public record PaginaResultado<T>(
    IReadOnlyList<T> Dados,
    int PaginaAtual,
    int TamanhoPagina,
    long TotalRegistros,
    int TotalPaginas);

// Paginated query service
public class PedidoQueryService(AppDbContext context)
{
    public async Task<PaginaResultado<PedidoResumoDto>> ListarPedidosAsync(
        PaginacaoRequest paginacao,
        string? clienteId = null,
        CancellationToken ct = default)
    {
        // Build the base query with optional filters
        var query = context.Pedidos
            .AsNoTracking()           // Reads: always AsNoTracking!
            .Where(p => clienteId == null || p.ClienteId == clienteId);

        // Separate COUNT for the total (EF Core 8 optimizes this)
        var total = await query.LongCountAsync(ct);

        // Page data
        var dados = await query
            .OrderBy(p => p.DataCriacao)   // OrderBy required for consistent pagination
            .Skip(paginacao.OffsetSeguro)
            .Take(paginacao.TamanhoSeguro)
            .Select(p => new PedidoResumoDto(   // Projection: only the needed fields
                p.Id,
                p.ClienteId,
                p.Valor,
                p.DataCriacao,
                p.Status))
            .ToListAsync(ct);

        return new PaginaResultado<PedidoResumoDto>(
            Dados: dados,
            PaginaAtual: paginacao.Pagina,
            TamanhoPagina: paginacao.TamanhoSeguro,
            TotalRegistros: total,
            TotalPaginas: (int)Math.Ceiling(total / (double)paginacao.TamanhoSeguro));
    }
}

πŸ’‘ Tip: Always use .Select() with a projection (DTO or record); never return the full entity in listing queries. This reduces the volume of data transferred and avoids loading unnecessary navigations.

Generated SQL: SQL Server vs Oracle

EF Core 8 generates correct and optimized SQL for both databases:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- SQL Server (generated by EF Core 8)
SELECT p.Id, p.ClienteId, p.Valor, p.DataCriacao, p.Status
FROM Pedidos AS p
WHERE p.ClienteId = N'cliente-123'
ORDER BY p.DataCriacao
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- Oracle 12c+ (generated by Oracle.EntityFrameworkCore 8)
SELECT p.Id, p.ClienteId, p.Valor, p.DataCriacao, p.Status
FROM Pedidos p
WHERE p.ClienteId = :clienteId_0
ORDER BY p.DataCriacao
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

⚠️ Warning: For Oracle 11g (without FETCH FIRST), the provider generates a query with nested ROWNUM, which may have additional cost on very large tables. Consider migrating to Oracle 12c+ or using materialized views for heavy reports.


Keyset Pagination: High Performance for Infinite Scroll and APIs

When the user navigates to page 500 of a result with 10 thousand items, the database needs to perform a scan of 10,000 rows just to skip the first 9,980. With keyset pagination, you use the value of the last fetched row as the starting point of the next query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
// Request with cursor (keyset)
public record KeysetRequest(
    Guid? UltimoId = null,
    DateTime? UltimaDataCriacao = null,
    int Limite = 20);

// Result with cursor for the next page
public record KeysetResultado<T>(
    IReadOnlyList<T> Dados,
    bool TemProximaPagina,
    Guid? ProximoCursorId,
    DateTime? ProximoCursorData);

public class PedidoKeysetService(AppDbContext context)
{
    public async Task<KeysetResultado<PedidoResumoDto>> ListarComCursorAsync(
        KeysetRequest request,
        CancellationToken ct = default)
    {
        // Keyset filter: fetches records "after" the cursor
        // Using (DataCriacao, Id) as a composite key for stability
        var query = context.Pedidos
            .AsNoTracking()
            .Where(p =>
                request.UltimaDataCriacao == null ||
                p.DataCriacao > request.UltimaDataCriacao ||
                (p.DataCriacao == request.UltimaDataCriacao &&
                 p.Id.CompareTo(request.UltimoId!.Value) > 0))
            .OrderBy(p => p.DataCriacao)
            .ThenBy(p => p.Id);

        // Fetches Limite + 1 to know whether there is a next page
        var dados = await query
            .Take(request.Limite + 1)
            .Select(p => new PedidoResumoDto(p.Id, p.ClienteId, p.Valor, p.DataCriacao, p.Status))
            .ToListAsync(ct);

        var temProxima = dados.Count > request.Limite;
        if (temProxima) dados.RemoveAt(dados.Count - 1); // Removes the extra item

        var ultimo = dados.LastOrDefault();

        return new KeysetResultado<PedidoResumoDto>(
            Dados: dados,
            TemProximaPagina: temProxima,
            ProximoCursorId: ultimo?.Id,
            ProximoCursorData: ultimo?.DataCriacao);
    }
}

This pattern keeps constant performance regardless of the page number, because the generated SQL uses a simple indexed WHERE instead of OFFSET.

1
2
3
4
5
6
-- Generated SQL (SQL Server / Oracle)
SELECT TOP(21) p.Id, p.ClienteId, p.Valor, p.DataCriacao, p.Status
FROM Pedidos AS p
WHERE p.DataCriacao > '2025-06-01'
   OR (p.DataCriacao = '2025-06-01' AND p.Id > 'abc-123')
ORDER BY p.DataCriacao, p.Id;

πŸ’‘ Tip: Create a composite index on the keyset columns to guarantee that the query uses an index seek instead of a table scan:

1
2
3
4
5
-- SQL Server
CREATE INDEX IX_Pedidos_Keyset ON Pedidos (DataCriacao, Id) INCLUDE (ClienteId, Valor, Status);

-- Oracle
CREATE INDEX IX_Pedidos_Keyset ON Pedidos (DataCriacao, Id);

Putting It All Together: The Complete Architecture

With both solutions combined, the application architecture looks like this:

  • Writes: the API receives β†’ publishes to the queue β†’ returns 202 Accepted β†’ the Consumer processes in batches with BulkInsert
  • Reads: the API receives a paginated request β†’ runs the query with Skip/Take (offset) or WHERE (keyset) β†’ returns only the necessary data
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// ASP.NET Core endpoint β€” asynchronous write
app.MapPost("/pedidos/lote", async (
    [FromBody] List<CriarPedidoRequest> requests,
    PedidoService service,
    CancellationToken ct) =>
{
    var ids = await Task.WhenAll(
        requests.Select(r => service.CriarPedidoAsync(r, ct)));

    // 202 Accepted: the orders were queued, not necessarily written
    return Results.Accepted("/pedidos/status", new { Ids = ids });
});

// ASP.NET Core endpoint β€” paginated read
app.MapGet("/pedidos", async (
    [AsParameters] PaginacaoRequest paginacao,
    [FromQuery] string? clienteId,
    PedidoQueryService queryService,
    CancellationToken ct) =>
{
    var resultado = await queryService.ListarPedidosAsync(paginacao, clienteId, ct);
    return Results.Ok(resultado);
});

Tips and Good Practices

  • Proper indexes: Every column used in OrderBy, Where or a keyset cursor must have an index. On Oracle, use function-based indexes for columns with transformations (e.g., UPPER(nome)).
  • AsNoTracking always for reads: It disables the Change Tracker for read queries, reducing memory and CPU usage by up to 30%.
  • Avoid Count() on large tables: For keyset pagination, you don’t need the total number of records. For offset, consider caching the total for a few seconds.
  • Set explicit timeouts: Configure context.Database.SetCommandTimeout(30) or use WithTimeout per query to prevent long queries from blocking the application indefinitely.
  • Monitor the queue size: Configure alerts for when the queue exceeds N messages β€” it is the signal that the consumer can’t keep up with the volume.
  • Use IAsyncEnumerable for streaming: For CSV exports or processing large volumes that don’t need pagination, use AsAsyncEnumerable() to process row by row without loading everything into memory:
1
2
3
4
5
6
7
8
// Streaming with IAsyncEnumerable β€” without loading everything into memory
await foreach (var pedido in context.Pedidos
    .AsNoTracking()
    .AsAsyncEnumerable()
    .WithCancellation(ct))
{
    await exportador.EscreverLinhaAsync(pedido, ct);
}
  • Explicit transactions for consistency: When using BulkInsert with multiple tables (e.g., writing Pedido and ItensPedido together), wrap the operation in an explicit transaction to guarantee atomicity.

Conclusion

Database bottlenecks with EF Core are rarely the fault of the database itself. In the overwhelming majority of cases, the problem lies in how the application interacts with it: inserting one record at a time instead of in batches, and bringing the whole result instead of paginating.

The combination of messaging (RabbitMQ or Azure Service Bus) to decouple bulk writes and efficient pagination (offset for UIs, keyset for APIs and infinite scroll) solves both bottlenecks in an elegant, scalable and resilient way. With EF Core 8.0+ and the techniques presented here, you can support much larger volumes without changing the database structure, just by adjusting how the application communicates with it.

The natural next step is observability: instrumenting slow queries with Application Performance Monitoring (APM), creating alerts for queries above N seconds, and mapping missing indexes via Query Store (SQL Server) or AWR (Oracle).


Read Also


References