Projection in SingleResultSpecification<TEntity, TResult> Causes Unnecessary Left Joins and Over-fetching

[vinayaroratech]

When using SingleResultSpecification<TEntity, TResult> with a projection to a DTO, the generated SQL includes unnecessary LEFT JOIN statements, causing over-fetching of related entities. The issue does not occur when using SingleResultSpecification, but in that case, the projection is applied in-memory rather than at the database level.

Expected Behavior:

The projection should be applied at the SQL level, avoiding unnecessary LEFT JOIN queries.
The query should only fetch the necessary columns, rather than all related entities.

Actual Behavior:

The generated SQL fetches all related entities using LEFT JOIN, even if they are filtered out in the projection.
This leads to over-fetching and performance degradation.
Steps to Reproduce:

Define a specification using SingleResultSpecification<TEntity, TResult>:

public class GetAdmissionWithMedicineDtoByIdSpec 
    : SingleResultSpecification<Domain.Admissions.Admission, FollowUpMedicineDto>
{
    public GetAdmissionWithMedicineDtoByIdSpec(DefaultIdType id)
    {
        Query
            .Where(a => a.Id == id);
    }
}

Mapster

TypeAdapterConfig<Domain.Admissions.Admission, FollowUpMedicineDto>
       .NewConfig()
       .Map(dest => dest.FollowUpAdvice, src => src.FollowUpAdvice)
       .Map(dest => dest.Medicines, src => src.Medicines.Adapt<IEnumerable<MedicineDto>>(), src => src.Medicines != null);

Override ApplySpecification in public class ApplicationDbRepository<T> : RepositoryBase<T>, IReadRepository<T>, IRepository<T>
    where T : class, IAggregateRoot

protected override IQueryable<TResult> ApplySpecification<TResult>(ISpecification<T, TResult> specification) =>
    specification.Selector is not null
        ? base.ApplySpecification(specification)
        : ApplySpecification(specification, false)
            .ProjectToType<TResult>();

Execute the query with SpecificationEvaluator.

Observe the generated SQL, which contains unnecessary LEFT JOIN queries fetching all medicines, even though they are filtered in the projection.

Generated SQL (Unexpected Behavior):

SELECT a."FollowUpAdvice", a."Id",
m."Id", m."AdmissionId", m."Name", m."MedicineType",
m2."Id", m2."AdmissionId", m2."Name", m2."MedicineType"
FROM "Admission"."Admissions" AS a
LEFT JOIN "Admission"."Medicines" AS m ON a."Id" = m."AdmissionId"
LEFT JOIN "Admission"."Medicines" AS m2 ON a."Id" = m2."AdmissionId"
WHERE a."Id" = @__id_0
ORDER BY a."Id", m."Id"
(Extra LEFT JOIN on Medicines even though only MedicineType.FollowUp is needed.)

Workarounds Tried:

Manually writing projection in specification.

Proposed Fix:
Ensure that SingleResultSpecification<TEntity, TResult> properly applies projection at the SQL level without adding unnecessary LEFT JOINs.

Environment:

Ardalis.Specification Version: 7.0.0
Database: PostgreSQL
.NET Version: .NET 6

[fiseni]

Hey @vinayaroratech,

It seems you're not even using the projections defined in the specification. Whenever you have a Specification<T, TResult> type, you must specify the Select expression in the spec itself.

You have overridden the below method, so you're applying the projection using Mapster. It has little to do with the specs.

protected override IQueryable<TResult> ApplySpecification<TResult>(ISpecification<T, TResult> specification) =>
    specification.Selector is not null
        ? base.ApplySpecification(specification)
        : ApplySpecification(specification, false)
            .ProjectToType<TResult>();

Other than that, we're not altering the expressions in any way. Also, we're not producing the SQL query, the EF provider does. Can you please try without specs at all, use DbContext directly. What do you see in that case?