Fixing Slow EF Core Read Endpoints Without Returning Database Entities from Your .NET API

An event platform has an endpoint that lists upcoming conferences. The first implementation returns Entity Framework Core entities directly and uses Include to load venues, sessions, speakers, and ticket options. It works with a small database, but the endpoint becomes slower as the relationships grow.

The response needs only the event name, venue, start date, and lowest ticket price. The application is loading a much larger object graph, exposing persistence classes outside the data layer, and making the API contract depend on the database model.

The fix is not to remove Entity Framework Core or replace every LINQ query with handwritten database commands. The fix is to separate read and write requirements. Read endpoints should project only the required fields into dedicated Data Transfer Objects, while write operations should load the aggregate state needed to enforce business rules.

Context and Scope

The example contains four parts:

• An ASP.NET Core API receives catalog and ticketing requests.
• An application layer coordinates queries and commands.
• A database driver contains the Entity Framework Core configuration.
• A relational database stores events, venues, and ticket options.

HTTP Request
    |
    v
Application Service
    |
    v
Repository or Query Handler
    |
    v
EF Core DbContext
    |
    v
Relational Database

The read path returns compact response models. The write path loads entities that Entity Framework Core can track, exposes them through a domain aggregate, applies business rules, and persists the detected changes with SaveChangesAsync().

The desired outcome is:

• Read endpoints transfer only the data they need.
• Database entities remain inside the database driver.
• Domain rules are not bypassed by setting public properties from controllers.
• Related collections are loaded only when an operation requires them.
• Queries and updates that must remain consistent run in one transaction.
• Schema changes are managed through migrations instead of manual drift.

Why the Original Endpoint Becomes Expensive

Entity Framework Core represents mapped tables through DbSet<T> properties on a custom DbContext. LINQ operations that start from those sets usually produce an IQueryable<T>.

An IQueryable<T> describes a database query, but it does not execute the query immediately. Execution normally occurs when the application requests results with methods such as:

• ToListAsync()
• ToArrayAsync()
• FirstOrDefaultAsync()
• CountAsync()

This deferred execution is useful because Entity Framework Core can combine filters, ordering, navigation, and projections before sending the final operation to the database.

Problems appear when the application asks for complete entities and several related collections even though the response needs only a few scalar values.

A typical over-fetching flow looks like this:

API needs:
  Event name
  Venue name
  Start date
  Lowest ticket price

Query loads:
  Event
  Venue
  All sessions
  All speakers
  All ticket options
  Additional nested relationships

The application transfers unnecessary columns and rows, creates a larger in-memory graph, and couples the outer layers to entity classes that were designed for persistence.

Step 1: Put the DbContext in a Dedicated Database Driver

The DbContext, mapped entities, provider configuration, and migration artifacts belong in a separate library. This keeps the dependency on the selected database technology at the outer boundary of the application.

A small event catalog model can begin with VenueEntity and EventEntity.

public sealed class VenueEntity
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required string City { get; set; }

    public ICollection<EventEntity> Events { get; set; } =
        new List<EventEntity>();
}

public sealed class EventEntity
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public DateTime StartsAt { get; set; }
    public decimal BaseTicketPrice { get; set; }

    public int VenueId { get; set; }
    public required VenueEntity Venue { get; set; }

    public ICollection<TicketOptionEntity> TicketOptions { get; set; } =
        new List<TicketOptionEntity>();
}

The custom context exposes mapped collections and completes configuration in OnModelCreating.

public sealed class EventsDbContext : DbContext
{
    public EventsDbContext(DbContextOptions options)
        : base(options)
    {
    }

    public DbSet<EventEntity> Events => Set<EventEntity>();
    public DbSet<VenueEntity> Venues => Set<VenueEntity>();
    public DbSet<TicketOptionEntity> TicketOptions =>
        Set<TicketOptionEntity>();

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<VenueEntity>()
            .HasMany(venue => venue.Events)
            .WithOne(eventItem => eventItem.Venue)
            .HasForeignKey(eventItem => eventItem.VenueId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.Entity<EventEntity>()
            .Property(eventItem => eventItem.BaseTicketPrice)
            .HasPrecision(10, 2);

        builder.Entity<EventEntity>()
            .HasIndex(eventItem => eventItem.StartsAt);
    }
}

The relationship mapping tells Entity Framework Core how the navigation properties and foreign key belong together. The precision configuration defines how decimal ticket values are represented. The index declaration records a database concern close to the mapping rather than spreading it through application code.

Step 2: Project Read Results Directly into DTOs

A read endpoint should not return EventEntity. Create a response type that contains exactly what the client needs.

public sealed record UpcomingEventDto(
    int Id,
    string Name,
    DateTime StartsAt,
    string VenueName,
    string VenueCity,
    decimal LowestTicketPrice);

The query can use Select to project database data directly into the DTO.

public sealed class UpcomingEventsQuery
{
    private readonly EventsDbContext _context;

    public UpcomingEventsQuery(EventsDbContext context)
    {
        _context = context;
    }

    public Task<List<UpcomingEventDto>> ExecuteAsync(
        DateTime from,
        CancellationToken cancellationToken)
    {
        return _context.Events
            .Where(eventItem => eventItem.StartsAt >= from)
            .OrderBy(eventItem => eventItem.StartsAt)
            .Select(eventItem => new UpcomingEventDto(
                eventItem.Id,
                eventItem.Name,
                eventItem.StartsAt,
                eventItem.Venue.Name,
                eventItem.Venue.City,
                eventItem.TicketOptions
                    .Min(option => option.Price)))
            .ToListAsync(cancellationToken);
    }
}

The projection navigates through related data without returning the related entity instances. Entity Framework Core translates the expression and retrieves the fields needed to build UpcomingEventDto.

This is different from loading a full event graph and mapping it after materialization. Projection reduces the amount of data exchanged with the database and prevents the API layer from depending on persistence classes.

For paged screens, use a separate CountAsync() query to determine the total number of matching records, then apply the page-specific query before calling ToListAsync().

Step 3: Materialize the Query Once

Because LINQ queries based on IQueryable<T> use deferred execution, the application must be deliberate about when materialization happens.

Consider this pattern:

var query = _context.Events
    .Where(eventItem => eventItem.StartsAt >= from)
    .Select(eventItem => new UpcomingEventDto(
        eventItem.Id,
        eventItem.Name,
        eventItem.StartsAt,
        eventItem.Venue.Name,
        eventItem.Venue.City,
        eventItem.TicketOptions.Min(option => option.Price)));

var events = await query.ToListAsync(cancellationToken);

The variable query contains the query definition. The database operation occurs at ToListAsync().

This separation is useful because additional filters can be composed before execution. It also means that careless repeated materialization can execute similar database work more than once.

Keep the flow clear:

1. Start from the mapped set.
2. Add filters.
3. Add ordering.
4. Project into the output type.
5. Materialize once.

Step 4: Inspect the Generated Query Before Guessing

A LINQ expression can look simple while producing a database operation that is more complex than expected. Entity Framework Core provides ToQueryString() so developers can inspect what a query will send to the relational provider.

var query = _context.Events
    .Where(eventItem => eventItem.StartsAt >= from)
    .Select(eventItem => new UpcomingEventDto(
        eventItem.Id,
        eventItem.Name,
        eventItem.StartsAt,
        eventItem.Venue.Name,
        eventItem.Venue.City,
        eventItem.TicketOptions.Min(option => option.Price)));

var generatedCommand = query.ToQueryString();

Use the output during diagnosis to answer questions such as:

• Are unnecessary columns being selected?
• Did a navigation create additional joins?
• Is the filter applied before the result is materialized?
• Did the query become more complex after adding a nested collection?
• Is the read model still aligned with the endpoint response?

Do not optimize by intuition alone. Inspect the generated operation, simplify the projection, and measure the affected workflow.

Step 5: Reserve Include for Operations That Need Entities

Include is appropriate when an operation must load related entities into the object graph. It should not be the automatic choice for every read endpoint.

Suppose an administrator changes an event and all ticket options in one operation. The write workflow needs the event and its related ticket options because those entities will be modified.

var eventToUpdate = await _context.Events
    .Include(eventItem => eventItem.TicketOptions)
    .FirstOrDefaultAsync(
        eventItem => eventItem.Id == eventId,
        cancellationToken);

if (eventToUpdate is null)
{
    throw new InvalidOperationException("Event was not found.");
}

foreach (var ticket in eventToUpdate.TicketOptions)
{
    ticket.Price += priceIncrease;
}

await _context.SaveChangesAsync(cancellationToken);

Entity Framework Core loads the required graph, detects the changes, and synchronizes them when SaveChangesAsync() is called.

Nested relationships can be loaded with ThenInclude. However, adding several included collections can cause Entity Framework Core to build one complex query. When that query becomes too expensive, AsSplitQuery() allows the load to be divided into several database operations.

var eventToUpdate = await _context.Events
    .AsSplitQuery()
    .Include(eventItem => eventItem.TicketOptions)
    .Include(eventItem => eventItem.Sessions)
        .ThenInclude(session => session.Speakers)
    .FirstOrDefaultAsync(
        eventItem => eventItem.Id == eventId,
        cancellationToken);

A split query is not a universal performance switch. Use it after the generated query shows that one large combined operation is the problem.

Step 6: Keep Domain Rules Out of Entity Setters

Returning database entities from controllers makes it easy for any caller to change properties directly. That bypasses rules such as:

• Ticket prices cannot be negative.
• A published event cannot move to a past date.
• Capacity cannot be reduced below the number of confirmed attendees.

A domain aggregate should expose methods that enforce these rules. The database entity can implement an interface defined by the domain layer, allowing the aggregate to modify the same state that Entity Framework Core tracks.

public interface IEventState
{
    int Id { get; }
    DateTime StartsAt { get; set; }
    decimal BaseTicketPrice { get; set; }
}

public sealed class EventAggregate
{
    private readonly IEventState _state;

    public EventAggregate(IEventState state)
    {
        _state = state;
    }

    public void Reschedule(DateTime newStart)
    {
        if (newStart <= DateTime.UtcNow)
        {
            throw new InvalidOperationException(
                "An event must start in the future.");
        }

        _state.StartsAt = newStart;
    }

    public void ChangeBasePrice(decimal newPrice)
    {
        if (newPrice < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(newPrice));
        }

        _state.BaseTicketPrice = newPrice;
    }
}

The persistence entity implements the state interface inside the database driver.

public sealed class EventEntity : IEventState
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public DateTime StartsAt { get; set; }
    public decimal BaseTicketPrice { get; set; }

    public int VenueId { get; set; }
    public required VenueEntity Venue { get; set; }

    public ICollection<TicketOptionEntity> TicketOptions { get; set; } =
        new List<TicketOptionEntity>();
}

A repository can load EventEntity, wrap it in EventAggregate, and return the aggregate to the application layer. When the aggregate changes the state interface, it changes the tracked entity. SaveChangesAsync() can then persist the update.

This arrangement keeps Entity Framework Core references outside the domain library while avoiding a second disconnected copy of the state.

Step 7: Use Explicit Transactions for Read-Then-Write Rules

A single SaveChangesAsync() call sends its detected changes in one transaction. That is sufficient for many updates.

Some workflows require a query and an update to be protected together. Ticket reservation is an example:

1. Read the remaining capacity.
2. Reject the request when no seats remain.
3. Reduce the available count.
4. Save the update.

Without one transaction around the complete sequence, another operation can interfere between the read and the update.

using var transaction = _context.Database.BeginTransaction();

try
{
    var ticket = await _context.TicketOptions
        .FirstOrDefaultAsync(
            option => option.Id == ticketOptionId,
            cancellationToken);

    if (ticket is null)
    {
        throw new InvalidOperationException(
            "Ticket option was not found.");
    }

    if (ticket.AvailableSeats <= 0)
    {
        throw new InvalidOperationException(
            "No seats remain.");
    }

    ticket.AvailableSeats--;

    await _context.SaveChangesAsync(cancellationToken);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Any SaveChangesAsync() call inside the explicit transaction uses that transaction instead of creating a separate one.

The transaction boundary should match the business consistency rule. Do not make every request one long transaction, and do not separate a read from the update when the decision must remain valid until persistence completes.

Step 8: Keep the Model and Database Synchronized with Migrations

Entity classes and fluent configuration describe the model expected by the application. Migrations describe how an existing relational database moves from one model version to the next.

A typical development workflow is:

dotnet ef migrations add CreateEventCatalog
dotnet ef database update

Every migration should contain both the forward operation and the corresponding rollback operation where possible.

In deployment, the application can apply pending migrations with Database.Migrate(). The connection used for migration may need broader privileges than the connection used during normal application requests.

In environments where application deployment is not allowed to create or alter database objects, generate the migration output for review and let the responsible database administrator apply it through the organization's controlled process.

Avoid a situation where developers update entity classes but leave the production schema to manual memory and undocumented changes.

When Compiled Models Are Worth Considering

Entity Framework Core can generate a compiled model for applications with very large mappings. This can reduce the model-initialization work performed by the framework.

Do not start with compiled models merely because a read endpoint is slow. First check:

• The DTO projection
• The generated query
• Included collections
• Nested relationships
• Filtering and ordering
• The database design and indexes

Compiled models are appropriate when even simple operations are delayed by model construction and the mapping contains many entities. Regenerate the compiled model whenever the mapping changes.

Testing the Refactoring

Test the read and write paths separately.

Read-path tests

Verify that the query:

• Returns only upcoming events.
• Produces the DTO shape expected by the API.
• Includes venue values through projection.
• Calculates the expected ticket summary.
• Applies ordering before materialization.

Write-path tests

Verify that:

• Aggregate methods reject invalid dates and prices.
• The repository returns an aggregate instead of an EF entity.
• A valid aggregate change is persisted by SaveChangesAsync().
• A failed reservation rolls back the transaction.
• Related collections are loaded only for operations that modify them.

Do not make tests depend on an exact generated query string unless the exact text is part of a deliberate provider-specific requirement. Query text can change while behavior remains correct. Use it primarily as a diagnostic tool.

Common Mistakes

Returning EF Core entities from the API

This exposes persistence structure, encourages direct property modification, and makes the public contract change when the database model changes.

Using Include for list endpoints

A list usually needs a projection, not a full updateable graph. Load entities only when the operation needs them.

Calling ToListAsync too early

Materializing before filtering or projecting moves work into memory and prevents the provider from translating the complete operation.

Assuming a short LINQ expression means a simple database operation

Inspect the generated query with ToQueryString() before deciding where the problem is.

Adding AsSplitQuery everywhere

Split queries solve a specific problem with large included graphs. They also create multiple database operations. Apply them deliberately.

Copying entity data into an aggregate and forgetting to copy changes back

Entity Framework Core detects changes on tracked entities. A disconnected aggregate requires an explicit mapping strategy. Wrapping an entity behind a domain state interface keeps both models connected.

Expecting SaveChangesAsync to protect an earlier query

One save operation is transactional, but a read-then-write rule needs an explicit transaction when both actions must remain consistent.

Running production migrations with normal application credentials

Schema modification may require separate privileges and a controlled deployment identity.

Refactoring Checklist

• [ ] Keep DbContext and provider configuration in a database driver library
• [ ] Keep EF Core entities out of API response contracts
• [ ] Project read-only queries directly into DTOs with Select
• [ ] Apply filters and ordering before ToListAsync
• [ ] Materialize each query deliberately
• [ ] Use CountAsync separately when paging needs a total
• [ ] Inspect complex queries with ToQueryString
• [ ] Use Include only when an operation needs related entities
• [ ] Consider AsSplitQuery only after diagnosing a large included graph
• [ ] Apply domain changes through aggregate methods
• [ ] Keep the domain layer independent of EF Core packages
• [ ] Catch persistence failures around SaveChangesAsync
• [ ] Use an explicit transaction for read-then-write consistency rules
• [ ] Create and review migrations for every relational model change
• [ ] Use separate deployment privileges when production schema changes require them
• [ ] Consider compiled models only after model initialization is identified as the bottleneck

Conclusion

Slow EF Core endpoints are often caused by a mismatch between what the application needs and what the query loads. Returning complete entity graphs may feel convenient, but it transfers unnecessary data and lets persistence concerns spread into the API and domain layers.

Project read models directly into DTOs, delay materialization until the query is complete, inspect generated operations, and load related entities only for writes that need them. Keep domain rules inside aggregates, and use explicit transactions when a query and update form one consistency boundary.

Entity Framework Core remains responsible for mapping and persistence, while the rest of the application works with models designed for its actual use cases.