A production API I consulted on had a GET endpoint that loads a single aggregate by its ID. Nothing fancy, just one entity with a few levels of child collections. Over a 24 hour period, it served around 51,800 requests with an average response time of 5.45 seconds and a 95th percentile of 11.1 seconds.
After a one-line change in the repository, the same endpoint now averages 155 milliseconds, with a 95th percentile of 189 milliseconds.
No new indexes, no caching, and no hand-written SQL. The problem was the combination of AsSplitQuery() and SingleOrDefaultAsync(), and the way Entity Framework Core translates this into SQL that PostgreSQL just can't optimize.
In this article, we'll build a model that mirrors the real one, look at the SQL EF Core actually sends to the database, understand why PostgreSQL struggles with it, and fix it with a tiny extension method. As usual, we'll use a cat-themed example, but this time a veterinary clinic.
Let's dive in!
The Vet Clinic Domain Model
The real application isn't about cats, but its data model has approximately the same shape: a deep aggregate, composite keys, and plenty of auto-included lookups. Our clinic tracks visits. Each visit has treatments, each treatment has procedures, and each procedure uses a number of supplies.
Let's start with the aggregate root:
public class Visit
{
public int Id { get; set; }
public DateTime ScheduledAt { get; set; }
public int CatId { get; set; }
public Cat Cat { get; set; } = null!;
public int OwnerId { get; set; }
public Owner Owner { get; set; } = null!;
public int ClinicId { get; set; }
public Clinic Clinic { get; set; } = null!;
public int AttendingVetId { get; set; }
public Vet AttendingVet { get; set; } = null!;
public int VisitStatusId { get; set; }
public VisitStatus Status { get; set; } = null!;
public List<Treatment> Treatments { get; set; } = [];
}
A Visit references five lookup entities: Cat, Owner, Clinic, Vet, and VisitStatus. They're simple classes with an Id and a Name, so we'll skip them here.
Next, we have the treatments:
public class Treatment
{
public int VisitId { get; set; }
public int TreatmentId { get; set; }
public required string Diagnosis { get; set; }
public int TreatmentTypeId { get; set; }
public TreatmentType TreatmentType { get; set; } = null!;
public int VetId { get; set; }
public Vet Vet { get; set; } = null!;
public int RoomId { get; set; }
public Room Room { get; set; } = null!;
public int NurseId { get; set; }
public Nurse Nurse { get; set; } = null!;
public int WardId { get; set; }
public Ward Ward { get; set; } = null!;
public int PriorityId { get; set; }
public Priority Priority { get; set; } = null!;
public int AnesthesiaTypeId { get; set; }
public AnesthesiaType AnesthesiaType { get; set; } = null!;
public int BillingCodeId { get; set; }
public BillingCode BillingCode { get; set; } = null!;
public int CurrencyId { get; set; }
public Currency Currency { get; set; } = null!;
public int InsurancePlanId { get; set; }
public InsurancePlan InsurancePlan { get; set; } = null!;
public int EquipmentId { get; set; }
public Equipment Equipment { get; set; } = null!;
public int SpecialtyId { get; set; }
public Specialty Specialty { get; set; } = null!;
public int ReferralSourceId { get; set; }
public ReferralSource ReferralSource { get; set; } = null!;
public List<Procedure> Procedures { get; set; } = [];
public List<TreatmentNote> Notes { get; set; } = [];
}
That's a lot of lookups, and it's on purpose. The child entity in the real application has 13 reference navigational properties too. Keep that number in mind, it's going to matter.
Also note that a Treatment doesn't have its own Id. It's identified by the VisitId and a TreatmentId that is only unique within its visit. The same goes for the next levels:
public class Procedure
{
public int VisitId { get; set; }
public int TreatmentId { get; set; }
public int ProcedureId { get; set; }
public required string Name { get; set; }
public DateTime PerformedAt { get; set; }
public List<ProcedureSupply> Supplies { get; set; } = [];
}
public class ProcedureSupply
{
public int VisitId { get; set; }
public int TreatmentId { get; set; }
public int ProcedureId { get; set; }
public int SupplyId { get; set; }
public decimal Quantity { get; set; }
public int MedicationId { get; set; }
public Medication Medication { get; set; } = null!;
}
A procedure has a three-part key, and a supply has a four-part key. There's also a TreatmentNote entity with a VisitId, TreatmentId, and NoteId key.
Configuring Composite Keys and Auto-Includes
Now, let's configure our entities. Here's the Treatment configuration:
public class TreatmentConfiguration : IEntityTypeConfiguration<Treatment>
{
public void Configure(EntityTypeBuilder<Treatment> builder)
{
builder.HasKey(x => new
{
x.VisitId,
x.TreatmentId
});
builder.Property(x => x.TreatmentId)
.ValueGeneratedNever();
builder.HasMany(x => x.Procedures)
.WithOne()
.HasForeignKey(x => new
{
x.VisitId,
x.TreatmentId
});
builder.HasMany(x => x.Notes)
.WithOne()
.HasForeignKey(x => new
{
x.VisitId,
x.TreatmentId
});
builder.Navigation(x => x.TreatmentType)
.AutoInclude();
builder.Navigation(x => x.Vet)
.AutoInclude();
builder.Navigation(x => x.Room)
.AutoInclude();
// The other 10 lookups are also auto-included the same way
builder.Navigation(x => x.Notes)
.AutoInclude();
}
}
We define the composite primary key and then configure the relationships to procedures and notes with composite foreign keys.
Next, we mark all 13 lookups and the notes collection as AutoInclude(). Auto-included properties are loaded every time we query the entity, without having to call Include().
The Visit configuration auto-includes its five lookup properties in the same way. ProcedureSupply auto-includes its Medication. The Procedure and ProcedureSupply entity configurations follow the same pattern, just with one more key column at each level.
Loading the Aggregate With a Split Query
Our repositories inherit from a small base class that exposes a GetByCondition() method:
public abstract class RepositoryBase<T>(ClinicDbContext context) where T : class
{
protected ClinicDbContext Context { get; } = context;
protected IQueryable<T> GetByCondition(
Expression<Func<T, bool>> expression,
bool trackChanges)
{
return trackChanges
? Context.Set<T>().Where(expression)
: Context.Set<T>().Where(expression).AsNoTracking();
}
}
And here's the method that loads a whole visit, exactly as it looked before the fix:
public class VisitRepository(ClinicDbContext context)
: RepositoryBase<Visit>(context), IVisitRepository
{
public async Task<Visit?> GetVisitAsync(
int visitId,
bool trackChanges,
CancellationToken cancellationToken = default)
{
return await GetByCondition(x => x.Id == visitId, trackChanges)
.Include(x => x.Treatments)
.ThenInclude(t => t.Procedures)
.ThenInclude(p => p.Supplies)
.AsSplitQuery()
.SingleOrDefaultAsync(cancellationToken);
}
}
We filter by the primary key and include the collections, then we call the SingleOrDefaultAsync() method. This is the most common way to load an entity by ID in EF Core, and I bet you've written something like it many times.
We use the AsSplitQuery() method for a good reason. With a single query, EF Core loads all collections with JOINs in one very large SQL statement. Every treatment gets multiplied by its procedures, every procedure by its supplies, and every row repeats all of the visit's columns. This is known as cartesian explosion, and by using split queries we avoid it by loading each collection with a separate SQL query.
So far, so good, but let's see what actually reaches the database.
The SQL EF Core Actually Generates
If we've enabled command logging by setting the Microsoft.EntityFrameworkCore.Database.Command category to Information, we would be able to see that our method executes five SQL queries: one for the visit, then one each for the treatments, the auto-included notes, the procedures, and the supplies.
The first query loads the visit itself, together with its auto-included lookups:
-- I've trimmed the remaining columns after ...
SELECT v."Id", v."AttendingVetId", v."CatId", ...
FROM "Visits" AS v
INNER JOIN "Vets" AS v0 ON v."AttendingVetId" = v0."Id"
INNER JOIN "Cats" AS c ON v."CatId" = c."Id"
INNER JOIN "Clinics" AS c0 ON v."ClinicId" = c0."Id"
INNER JOIN "Owners" AS o ON v."OwnerId" = o."Id"
INNER JOIN "VisitStatuses" AS v1 ON v."VisitStatusId" = v1."Id"
WHERE v."Id" = @visitId
ORDER BY v."Id", v0."Id", c."Id", c0."Id", o."Id", v1."Id"
LIMIT 2
The first thing to note is the LIMIT 2 call. That's how the SingleOrDefaultAsync() method works. It asks for two rows, so it can throw an exception if more than one matching row exists.
That LIMIT 2 statement is harmless on its own. The problem comes from what happens in the child queries that follow. For example, here's the one that loads the treatments:
-- I've trimmed the remaining columns after ...
SELECT s1."VisitId", s1."TreatmentId", s1."Diagnosis", ...
FROM (
SELECT v."Id", v0."Id" AS "Id0", c."Id" AS "Id1", c0."Id" AS "Id2", ...
FROM "Visits" AS v
INNER JOIN "Vets" AS v0 ON v."AttendingVetId" = v0."Id"
INNER JOIN "Cats" AS c ON v."CatId" = c."Id"
INNER JOIN "Clinics" AS c0 ON v."ClinicId" = c0."Id"
INNER JOIN "Owners" AS o ON v."OwnerId" = o."Id"
INNER JOIN "VisitStatuses" AS v1 ON v."VisitStatusId" = v1."Id"
WHERE v."Id" = @visitId
ORDER BY v."Id", v0."Id", c."Id", c0."Id", o."Id", v1."Id"
LIMIT 1
) AS s0
INNER JOIN (
-- I've trimmed the remaining columns after ...
SELECT t."VisitId", t."TreatmentId", t."Diagnosis", ...
FROM "Treatments" AS t
INNER JOIN "AnesthesiaTypes" AS a ON t."AnesthesiaTypeId" = a."Id"
INNER JOIN "BillingCodes" AS b ON t."BillingCodeId" = b."Id"
INNER JOIN "Currencies" AS c1 ON t."CurrencyId" = c1."Id"
INNER JOIN "Equipment" AS e ON t."EquipmentId" = e."Id"
INNER JOIN "InsurancePlans" AS i ON t."InsurancePlanId" = i."Id"
INNER JOIN "Nurses" AS n ON t."NurseId" = n."Id"
INNER JOIN "Priorities" AS p ON t."PriorityId" = p."Id"
INNER JOIN "ReferralSources" AS r ON t."ReferralSourceId" = r."Id"
INNER JOIN "Rooms" AS r0 ON t."RoomId" = r0."Id"
INNER JOIN "Specialties" AS s ON t."SpecialtyId" = s."Id"
INNER JOIN "TreatmentTypes" AS t0 ON t."TreatmentTypeId" = t0."Id"
INNER JOIN "Vets" AS v2 ON t."VetId" = v2."Id"
INNER JOIN "Wards" AS w ON t."WardId" = w."Id"
) AS s1 ON s0."Id" = s1."VisitId"
-- I've trimmed the remaining columns after ...
ORDER BY s0."Id", s0."Id0", s0."Id1", s0."Id2", s0."Id3", s0."Id4", ...
For EF Core to load the children, it needs to know which parent they belong to. With split queries, it gets that by repeating the root query in every child query. Because the root query had a row limit, EF Core keeps a limit in the copy too, so the root query ends up in a subquery with ORDER BY and LIMIT 1.
The treatments, on the other hand, end up in a second derived table, together with all 13 of their auto-included lookup properties.
Now, take a closer look at where our WHERE v."Id" = @visitId filter is. It's inside the limited subquery. The Treatments table is only connected to it through the s0."Id" = s1."VisitId" join condition.
The same pattern repeats in all four child queries. The notes, procedures, and supplies queries join the same two derived tables, and after that they add their own table on top of that.
Why PostgreSQL Can't Optimize the LIMIT Subquery
PostgreSQL's planner tries to "pull up" subqueries in the FROM clause and merge them into the outer query, so it can plan everything as one big join. But it can't do that for a subquery that has a LIMIT in it. If it merged it, it would change the result, as the limit has to be applied to the subquery's rows before the outer joins happen. In practice, a subquery with a LIMIT acts as an optimization fence.
This has an unpleasant consequence for us - when the filter is part of the outer query, the query planner sees a chain of equalities:
v."Id" = @visitIdv."Id" = t."VisitId"t."VisitId" = p."VisitId"
From those, it derives that t."VisitId" and p."VisitId" must also equal @visitId. Every child table gets its own filter on the first column of its primary key, and each one becomes a quick index lookup.
When the filter is locked inside a limited subquery that chain gets broken. The planner knows that the treatments have to match the row that comes out of the subquery. To use the index on Treatments, it would have to plan the treatments as a lookup that runs once for that row.
And here's where our 13 lookups come in. PostgreSQL only merges a derived table into its parent query as long as the number of tables stays within from_collapse_limit and join_collapse_limit. Both default to 8. Our treatments derived table alone has 14 tables. That's over the limit, so PostgreSQL plans it on its own, as a separate join problem, without any knowledge of the visit we asked for.
Without a visit ID to filter by, the cheapest plan for that piece is to read every single treatment then join them with their 13 lookup tables, and only then match the result against the result from the limited subquery. That's exactly what happens here.
If you are interested in having a deep dive into Minimal APIs, you can check my Minimal APIs in ASP.NET Core course here.
The Fix: Loading by Key Without a LIMIT
When we filter by the full primary key, the database can return at most one row anyway and the LIMIT doesn't protect us from anything, so we can drop it from the SQL and keep the SingleOrDefault() check in memory.
Let's create an extension method for that:
internal static class QueryableExtensions
{
/// <summary>
/// Executes a single-entity query without applying the SQL-level
/// <c>LIMIT 2</c> that <c>SingleOrDefaultAsync</c> adds. Only use it when the
/// query is guaranteed to return at most one entity
/// (e.g. a complete primary-key lookup).
///
/// Intended for EF Core split queries, where the <c>LIMIT</c> is repeated
/// in every child query and can prevent PostgreSQL from optimizing them.
/// </summary>
internal static async Task<T?> SingleOrDefaultByKeyAsync<T>(
this IQueryable<T> query,
CancellationToken cancellationToken = default)
{
var results = await query.ToListAsync(cancellationToken);
return results.SingleOrDefault();
}
}
The SingleOrDefaultByKeyAsync() method executes the query by calling the ToListAsync() method. It doesn't add any row limit. Then we call the in-memory SingleOrDefault() method on that result. If nothing matches, we get null. If exactly one entity matches, we get it back. And if more than one matches, we still get an InvalidOperationException exception, just like with the SingleOrDefaultAsync() method.
I made the class internal on purpose. It lives in the infrastructure layer next to the repositories, and I don't want it to look like a general-purpose replacement for the SingleOrDefaultAsync() method. The XML documentation states when it's safe to use, so the next developer who finds it knows the rules (if they bother to read them).
Now, let's update our repository method:
public async Task<Visit?> GetVisitByKeyAsync(
int visitId,
bool trackChanges,
CancellationToken cancellationToken = default)
{
return await GetByCondition(x => x.Id == visitId, trackChanges)
.Include(x => x.Treatments)
.ThenInclude(t => t.Procedures)
.ThenInclude(p => p.Supplies)
.AsSplitQuery()
.SingleOrDefaultByKeyAsync(cancellationToken);
}
The only change is the last line.
Let's execute the code and look at the query again:
SELECT s0."VisitId", s0."TreatmentId", s0."Diagnosis", ...
FROM "Visits" AS v
INNER JOIN "Vets" AS v0 ON v."AttendingVetId" = v0."Id"
INNER JOIN "Cats" AS c ON v."CatId" = c."Id"
INNER JOIN "Clinics" AS c0 ON v."ClinicId" = c0."Id"
INNER JOIN "Owners" AS o ON v."OwnerId" = o."Id"
INNER JOIN "VisitStatuses" AS v1 ON v."VisitStatusId" = v1."Id"
INNER JOIN (
SELECT t."VisitId", t."TreatmentId", t."Diagnosis", ...
FROM "Treatments" AS t
INNER JOIN "AnesthesiaTypes" AS a ON t."AnesthesiaTypeId" = a."Id"
-- ... the same 13 lookup joins as before
INNER JOIN "Wards" AS w ON t."WardId" = w."Id"
) AS s0 ON v."Id" = s0."VisitId"
WHERE v."Id" = @visitId
ORDER BY v."Id", v0."Id", c."Id", c0."Id", o."Id", v1."Id", s0."VisitId", ...
And just like that, the limited subquery is gone. The visit is joined directly, and WHERE v."Id" = @visitId is part of the outer query. The root query loses its LIMIT 2 as well.
The treatments derived table still has 14 tables, so PostgreSQL still plans it as a separate part, but this time it doesn't matter. From v."Id" = @visitId and v."Id" = s0."VisitId", the planner can decide that t."VisitId" must equal our visit ID too, and that condition goes straight into the treatments piece. Even when it's planned on its own, it now starts with an index lookup for a single visit.
We load exactly the same data and the only thing that changes, is how much the database knows when it plans the query.
The Same Trick for FirstAsync
The FirstAsync() and FirstOrDefaultAsync() methods have the same problem. They add a LIMIT 1 and EF Core repeats it in every single split query that it generates. In the real application, a background job loaded a parent entity like this:
var parentVisit = await context.Visits
.Include(x => x.Treatments)
.ThenInclude(t => t.Procedures)
.AsSplitQuery()
.FirstAsync(x => x.Id == parentVisitId, cancellationToken);
The fix I applied follows the same idea - we move the predicate to Where(), load with ToListAsync(), and only then pick the entity in memory:
var parentVisit = (await context.Visits
.Where(x => x.Id == parentVisitId)
.Include(x => x.Treatments)
.ThenInclude(t => t.Procedures)
.AsSplitQuery()
.ToListAsync(cancellationToken))
.First();
Again, this is only safe because we filter by the primary key, so you have to keep that in mind.
Comparing the PostgreSQL Execution Plans
To show the difference, I ran both versions against PostgreSQL 17. In production, the three tables behind this endpoint have respectively around 150,000, 300,000, and 350,000 rows. So I seeded the sample with the same shape: 150,000 visits, 300,000 treatments, and 350,000 procedures, plus notes and supplies entries.
Once this was done, I captured the SQL query EF Core sent and ran it with EXPLAIN (ANALYZE, BUFFERS). Here's the interesting part for the treatments query before the fix:
Sort (...) (actual time=761.674..761.696 rows=2 loops=1)
...
-> Hash Join (...) (actual time=181.390..761.513 rows=2 loops=1)
Hash Cond: (t."VisitId" = v."Id")
-> Hash Join (...) (actual time=0.142..723.507 rows=300000 loops=1)
...
-- 6 more Hash Joins with the lookup tables, each producing 300,000 rows
...
-> Seq Scan on "Treatments" t (...) (actual time=0.009..36.511 rows=300000 loops=1)
...
-> Hash (...)
-> Limit (...) (actual time=0.063..0.069 rows=1 loops=1)
...
-> Index Scan using "PK_Visits" on "Visits" v (...)
Index Cond: ("Id" = 75000)
...
Buffers: shared hit=3750
Planning Time: 25.528 ms
Execution Time: 761.938 ms
Let's read it from the bottom up. On one side, we have our Limit subquery, which finds the single visit in a fraction of a millisecond. On the other side, PostgreSQL does a Seq Scan over all of the 300,000 treatment rows and joins every single one of them with the lookup tables. Only at the very top does a Hash Join on t."VisitId" = v."Id" throw away everything except the 2 treatments we actually need.
That's 762 milliseconds to load 2 rows. And because the notes, procedures, and supplies queries all join the same treatments derived table, each of them repeats the same full scan.
And here's the same query after the fix:
Sort (...) (actual time=0.318..0.323 rows=2 loops=1)
...
-> Index Scan using "PK_Visits" on "Visits" v (...)
Index Cond: ("Id" = 75000)
...
-> Index Scan using "PK_Treatments" on "Treatments" t (...) (actual time=0.009..0.012 rows=2 loops=1)
Index Cond: ("VisitId" = 75000)
...
Buffers: shared hit=33
Planning Time: 21.586 ms
Execution Time: 0.631 ms
The visit ID made it into the index condition of the Treatments table, which now returns exactly the 2 rows we need. The query reads 33 buffers instead of 3,750, and runs in 0.6 milliseconds instead of the 762 milliseconds it used to take.
Here are the numbers for all five split queries, and for the whole repository method, measured over a 1000 runs:
| Sample project (150k / 300k / 350k rows) | Before | After |
|---|---|---|
| Execution time, 5 queries | 2,759 ms | 2.75 ms |
| Planning time, 5 queries | 114 ms | 97 ms |
| Repository method, median | 2,345 ms | 148 ms |
| Repository method, 95th percentile | 2,595 ms | 179 ms |
The sample goes from more than 2 seconds to under 150 milliseconds. That's about 16 times faster!
Important: The 13 auto-included lookups are what makes this so expensive. When I first built the sample with only 3 lookups on Treatment, the treatments derived table stayed under the collapse limits, PostgreSQL found the index on its own, and the SingleOrDefaultByKeyAsync()fix made the endpoint only about twice as fast. If your aggregates have a lot of auto-includes, you're much more likely to hit this, but it's a good thing to check for anyway.
When to Use SingleOrDefaultByKeyAsync And When Not To
This is not a general replacement for the SingleOrDefaultAsync() method. The LIMIT statement exists for a good reason. If the filter can match many rows, our SingleOrDefaultByKeyAsync() method will load all of them together with all of their included collections, just to throw an exception at the end.
You should onl use it when the filter you use covers the complete primary key, or another unique constraint. For an entity with a composite key, that means every single key column:
return await GetByCondition(x =>
x.VisitId == visitId &&
x.TreatmentId == treatmentId &&
x.ProcedureId == procedureId,
trackChanges)
.AsSplitQuery()
.SingleOrDefaultByKeyAsync(cancellationToken);
If we filter only by VisitId here, or by VisitId and TreatmentId, we'd load all procedures of the visit and that is not a key lookup anymore.
Here's a quick comparison to keep in mind:
SingleOrDefaultAsync() | SingleOrDefaultByKeyAsync() | |
|---|---|---|
Adds a SQL LIMIT | ✅ | ❌ |
Repeats the root query in a LIMIT subquery | ✅ (split queries) | ❌ |
| Throws when more than one entity matches | ✅ | ✅ |
| Safe for non-unique filters | ✅ | ❌ |
| Lets PostgreSQL push the key into child queries | ❌ | ✅ |
You must also note that this only matters for split queries that include collections. A query without the AsSplitQuery() call, or one that only includes reference properties, is executed as a single SQL statement. There's no repeated subquery, so SingleOrDefaultAsync() is perfectly fine there.
The Results
Here are the numbers for the endpoint from the introduction, taken from the API's monitoring. The "before" column covers the last 24 hours before the release, with around 51,800 requests. The "after" column covers the time since the release:
| GET endpoint for a single aggregate | Before | After |
|---|---|---|
| Average response time | 5.45 s | 155 ms |
| 95th percentile | 11.1 s | 189 ms |
That's about 35 times faster on average and almost 60 times faster at the 95th percentile. The 95th percentile is now very close to the average, so the endpoint isn't just faster, it's also much more predictable.
Conclusion
Split queries are a great tool against cartesian explosion, but they come with a catch. When we combine AsSplitQuery() with SingleOrDefaultAsync(), FirstAsync(), or FirstOrDefaultAsync(), EF Core wraps the root query in a subquery with a LIMIT and repeats it in every child query. PostgreSQL can't pull that subquery up, so our key filter stays locked inside. Add enough auto-included lookups to push a child table's derived table over the collapse limits, and PostgreSQL ends up scanning the whole child table in every split query.
When we load an entity by its full primary key, the LIMIT gives us nothing. A small SingleOrDefaultByKeyAsync() extension method removes it from the SQL and keeps the SingleOrDefault() guarantees in memory. On that API, the one-line change turned a 5-second endpoint into a 155-millisecond one.
If you use split queries with PostgreSQL, turn on EF Core's command logging and look for LIMIT inside a subquery, especially on entities with a lot of auto-includes. Then run one of those queries with EXPLAIN (ANALYZE, BUFFERS) and look for a Seq Scan on a child table. You might find the same hidden cost.
If you want to read more about EF Core, check out my posts on named query filters in EF Core 10 and optimistic concurrency with DateTime timestamps.
Frequently Asked Questions
Why does SingleOrDefaultAsync generate LIMIT 2 in EF Core?
SingleOrDefault has to throw an exception if more than one element matches. EF Core asks the database for at most two rows, which is enough to detect a duplicate without loading the whole result.
Do FirstOrDefaultAsync and FirstAsync have the same problem with split queries?
Yes. They add LIMIT 1 to the root query, and EF Core repeats it in a subquery in every split query, just like with SingleOrDefaultAsync().
Why not use FindAsync instead?
FindAsync() loads an entity by its primary key, but it doesn't support Include(), so it can't load the collections of an aggregate in one call. It also always tracks the entity it returns, so it's not an option for read-only AsNoTracking() queries.
Why not switch back to a single query with AsSingleQuery()?
For a deep aggregate with several collections, a single query brings back the cartesian explosion that split queries were introduced to solve. The fix in this article keeps the benefits of split queries and only removes the limit.
Would a Select projection have avoided this?
It depends on how much the projection needs. Auto-includes don't apply to projections, so EF Core only joins the lookups we actually use, but SingleOrDefaultAsync() still produces the same LIMIT 1 subqueries. I measured both cases on the sample:
- A DTO that needs all 13 lookup names builds the same big derived table, and it's just as slow: 2,170 ms with
SingleOrDefaultAsync(), and 152 ms withSingleOrDefaultByKeyAsync(). - A slim DTO with just 3 lookup names and the other foreign key IDs stays under the collapse limits. PostgreSQL finds the index on its own, and it takes about 20 ms with either method.
So, a lean projection is great for read-only endpoints, and you should use it where you can. But it only hides the problem until someone adds a few more fields to the DTO. It's also not an option when you need tracked entities to update.
Can't I just raise from_collapse_limit and join_collapse_limit instead?
It does change the plan. When I raised both to 20 for the session, the "before" queries switched to index lookups, and their execution time dropped from 2.5 seconds to 3.4 milliseconds. But their planning time jumped to over 1 second, because the planner now has to consider far more join orders. Removing the LIMIT fixes the cause without touching any database settings.
Does this issue also affect SQL Server?
EF Core generates the same query shape for other relational providers, with TOP instead of LIMIT. How much it costs depends on the database's query optimizer. I've only measured the impact on PostgreSQL, so check your own logs and execution plans before changing anything.
Is it safe to replace every SingleOrDefaultAsync call with SingleOrDefaultByKeyAsync?
No. Only use it when the filter matches the complete primary key or another unique constraint. Otherwise, it may load a large number of rows into memory before throwing an exception.
Does SingleOrDefaultByKeyAsync change the behavior of my queries?
No. It returns null when nothing matches, the entity when one matches, and throws an InvalidOperationException when more than one matches, the same as SingleOrDefaultAsync(). The only difference is the SQL that's sent to the database.