Unnecessary Columns in generated SQL with Complex Types + Object Closures in Projections

[d-2k]

Bug description

Summary

EF Core includes all entity columns when projecting complex types alongside non-primitive closure variables. This does not affect owned types.

Impact

Performance: Unnecessary columns, larger payloads, inefficient index usage. Severe with TPH hierarchies.

AspNetCore OData: Directly affects $select and $expand. OData uses SelectExpandWrapper (reference type) as closure when building projections, triggering this bug. Result: $select queries retrieve all columns instead of selected ones, defeating the purpose.

Steps to Reproduce

Models

public class DeadWeight { }

public class FlatShape
{
    public int Id { get; set; }
    public string Color { get; set; }
    public int Size { get; set; }  
    public Owned Owned { get; set; }
    public Complex Complex { get; set; }
}

public class Complex
{
    public int Key { get; set; }
    public string Value { get; set; }
}

public class Owned
{
    int Id { get; set; }
    public string Name { get; set; }
}

public abstract class Shape
{
    public int Id { get; set; }
    public string Color { get; set; }
    public Owned Owned { get; set; }
    public Complex Complex { get; set; }
}

public class Circle : Shape
{
    public double Radius { get; set; }
}

public class Rectangle : Shape
{
    public double Width { get; set; }
    public double Height { get; set; }
}

DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Shape>()
        .HasDiscriminator<string>("ShapeType")
        .HasValue<Circle>("Circle")
        .HasValue<Rectangle>("Rectangle");

    modelBuilder.Entity<Shape>()
        .OwnsOne(s => s.Owned);
    modelBuilder.Entity<Shape>()
        .ComplexProperty(i => i.Complex);

    modelBuilder.Entity<FlatShape>()
        .OwnsOne(s => s.Owned);
    modelBuilder.Entity<FlatShape>()
        .ComplexProperty(i => i.Complex);
}

Failing Test 1: Flat Entity

[Fact]
public void ProjectionWithComplexType_NoTPH_WithDeadWeight_Fails()
{
    var dw = new DeadWeight(); // Object closure
    using var context = new ShapesContext();
    
    var q = context.Set<FlatShape>().AsNoTracking()
        .Select(s => new
        {
            DeadWeight = dw,  // Reference type closure
            s.Id,
            s.Complex
        });
    var sql = q.ToQueryString();
    
    // BUG: Color and Size are included
    Assert.DoesNotContain(nameof(FlatShape.Color), sql); // FAILS
    Assert.DoesNotContain(nameof(FlatShape.Size), sql);  // FAILS
}

Failing Test 2: TPH Hierarchy

[Fact]
public void ProjectionWithComplexType_WithTPH_WithDeadWeight_Fails()
{
    var dw = new DeadWeight();
    using var context = new ShapesContext();
    
    var q = context.Set<Shape>().AsNoTracking()
        .Select(s => new
        {
            DeadWeight = dw,
            s.Id,
            s.Complex
        });
    var sql = q.ToQueryString();
    
    // BUG: All hierarchy columns included
    Assert.DoesNotContain(nameof(Shape.Color), sql);      // FAILS
    Assert.DoesNotContain(nameof(Circle.Radius), sql);    // FAILS
    Assert.DoesNotContain(nameof(Rectangle.Height), sql); // FAILS
    Assert.DoesNotContain(nameof(Rectangle.Width), sql);  // FAILS
}

Expected vs Actual

Expected (Flat Entity):

SELECT [f].[Id], [f].[Complex_Key], [f].[Complex_Value]
FROM [FlatShape] AS [f]

Actual (Flat Entity):

SELECT [f].[Id], [f].[Color], [f].[Size], [f].[Complex_Key], [f].[Complex_Value]
FROM [FlatShape] AS [f]

Expected (TPH):

SELECT [s].[Id], [s].[Complex_Key], [s].[Complex_Value]
FROM [Shape] AS [s]

Actual (TPH):

SELECT [s].[Id], [s].[ShapeType], [s].[Color], [s].[Radius], [s].[Height], [s].[Width], 
       [s].[Complex_Key], [s].[Complex_Value]
FROM [Shape] AS [s]

Working Cases

No closure

[Fact]
public void ProjectionWithComplexType_NoTPH_NoDeadWeight_Succeeds()
{
    using var context = new ShapesContext();
    var q = context.Set<FlatShape>().AsNoTracking()
        .Select(s => new { s.Id, s.Complex });
    var sql = q.ToQueryString();
    
    Assert.DoesNotContain(nameof(FlatShape.Color), sql);
    Assert.DoesNotContain(nameof(FlatShape.Size), sql);
}

Primitive closure

[Fact]
public void ProjectionWithComplexType_NoTPH_PrimitiveDeadWeight_Succeeds()
{
    var dw = "DeadWeight";
    using var context = new ShapesContext();
    var q = context.Set<FlatShape>().AsNoTracking()
        .Select(s => new { DeadWeight = dw, s.Id, s.Complex });
    var sql = q.ToQueryString();
    
    Assert.DoesNotContain(nameof(FlatShape.Color), sql);
    Assert.DoesNotContain(nameof(FlatShape.Size), sql);
}

Object closure with owned type

[Fact]
public void ProjectionOwnedType_NoTPH_WithDeadWeight_Succeeds()
{
    var dw = new DeadWeight();
    using var context = new ShapesContext();
    var q = context.Set<FlatShape>().AsNoTracking()
        .Select(s => new { DeadWeight = dw, s.Id, s.Owned }); // Owned, not Complex
    var sql = q.ToQueryString();
    
    Assert.DoesNotContain(nameof(FlatShape.Color), sql);
    Assert.DoesNotContain(nameof(FlatShape.Size), sql);
}

Your code

https://github.com/d-2k/EFBug

Stack traces

Verbose output

EF Core version

10.0.2

Database provider

No response

Target framework

.NET 10

Operating system

Windows 10

IDE

VSCode

[roji]

Confirmed bug/perf issue, not a regression (happens also in previous versions).

Given that this requires an object closure to reproduce, this can be worked around by adding the local object projection outside of EF (via AsEnumerableAsync() and then Select().

Out of curiosity, what kind of real-world scenario do you have which relies on projecting a local object like this within an EF query?

Minimal repro

using var context = new ShapesContext();

var dw = new DeadWeight();
var query = context.Set<FlatShape>()
    .Select(s => new
    {
        DeadWeight = dw,
        s.Id,
        s.Complex
    });

var sql = query.ToQueryString();
Console.WriteLine(sql);

public class ShapesContext : DbContext
{
    public DbSet<FlatShape> FlatShapes { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<FlatShape>().ComplexProperty(i => i.Complex);
    }
}

public class DeadWeight
{
    public int Foo { get; set; }
}

public class FlatShape
{
    public int Id { get; set; }
    public string Color { get; set; }
    public int Size { get; set; }
    public Complex Complex { get; set; }
}

public class Complex
{
    public int Key { get; set; }
    public string Value { get; set; }
}

SQL:

SELECT [f].[Id], [f].[Color], [f].[Size], [f].[Complex_Key], [f].[Complex_Value]
FROM [FlatShapes] AS [f]

Color and Size should not be projected out.

[d-2k]

@roji About real-world scenario, the real impact of this bug is in using AspNetCore.OData against EFCore model with complex types. Expressions OData lib generates hits this issue. I just simplified the repro.

[roji]

It's quite an odd thing to do in a query, so I'm interesting in understanding the scenario. But never mind, the fix is ready and will be merged for EF 11.