Unneeded columns in the projection of a JOIN subquery

[zulander1]

When running the query, EF is loading all the fields from the Product table,

var test = _dbContext.Orders
    .Select(x => new
    {
        total = x.OrderDetails.Where(f => f.Product.IncludeInSalesLevel == true).Select(f => f.Quantity).Sum()
    }).FirstOrDefault();

Generated SQL:

SELECT TOP(1) (
                  SELECT SUM(t0.Quantity)
                  FROM tOrderDetails AS t0
                  INNER JOIN
                  (
                      SELECT t2.Id, t2.Code, t2.CreatedBy, t2.CreatedDate, t2.IncludeInSalesLevel, t2.IsDeleted, t2.LastModifiedBy, t2.LastModifiedDate, t2.Name
                      FROM tProduct AS t2
                      WHERE Not(t2.IsDeleted)
                  ) AS t1 ON t0.ProductId == t1.Id
                  WHERE (Not(t0.IsDeleted) && ((t.Id != NULL) && (t.Id == t0.OrderId))) && (t1.IncludeInSalesLevel == CAST(1 AS bit))) AS total
              FROM tOrder AS t
              WHERE Not(t.IsDeleted))

Db context:

public partial class dbContext : DbContext
{
    public dbContext()
    {
    }

    public dbContext(DbContextOptions<dbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Order> Orders { get; set; }

    public virtual DbSet<OrderDetail> OrderDetails { get; set; }

    public virtual DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer("Server=...").LogTo(Console.WriteLine);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__tOrder__3214EC0752726C6D");

            entity.ToTable("Order");
            entity.HasQueryFilter(f => !f.IsDeleted);
        });

        modelBuilder.Entity<OrderDetail>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__tOrderDe__3214EC076E49BF39");

            entity.ToTable("OrderDetails");
            entity.HasQueryFilter(f => !f.IsDeleted);

            entity.HasOne(d => d.Order).WithMany(p => p.OrderDetails)
                .HasForeignKey(d => d.OrderId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Order");

            entity.HasOne(d => d.Product).WithMany(p => p.OrderDetails)
                .HasForeignKey(d => d.ProductId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Order_Product");
        });

        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__tProduct__3214EC07500B452F");

            entity.ToTable("Product");
            entity.HasQueryFilter(f => !f.IsDeleted);

            entity.Property(e => e.Code)
               .HasMaxLength(255)
               .IsUnicode(false);
            entity.Property(e => e.CreatedBy).HasMaxLength(255);
            entity.Property(e => e.CreatedDate).HasColumnType("datetime");
            entity.Property(e => e.LastModifiedBy).HasMaxLength(255);
            entity.Property(e => e.LastModifiedDate).HasColumnType("datetime");
            entity.Property(e => e.Name)
               .HasMaxLength(255)
               .IsUnicode(false);
        });
    }
}

using version:

Microsoft.EntityFrameworkCore Version="8.0.8"
Microsoft.EntityFrameworkCore.SqlServer Version="8.0.8"

[roji]

Are you referring to the fact that the SELECT inside the INNER JOIN has all the columns, as opposed to just t2.Id and t2.IncludeInSalesLevel?

SELECT SUM(t0.Quantity)
FROM tOrderDetails AS t0
INNER JOIN
(
    SELECT t2.Id, t2.Code, t2.CreatedBy, t2.CreatedDate, t2.IncludeInSalesLevel, t2.IsDeleted, t2.LastModifiedBy, t2.LastModifiedDate, t2.Name
  . FROM tProduct AS t2
    WHERE Not(t2.IsDeleted)
) AS t1 ON t0.ProductId == t1.Id
WHERE (Not(t0.IsDeleted) && ((t.Id != NULL) && (t.Id == t0.OrderId))) && (t1.IncludeInSalesLevel == CAST(1 AS bit))) AS total

If so, that projected column list can indeed be cleaned up, but those columns aren't "loaded" in any real sense - they're not referenced or projected out of the actual query, so there's little chance they affect actual performance in any way. Do you have any indication that these extra columns are a problem? Otherwise, I'll keep this as a SQL cleanup issue.

[zulander1]

Yes that correct, they are not "loaded". I believe cleaning up the query would resolve this issue.

[roji]

@zulander1 can you please check this with the latest 9.0 preview (9.0.0-preview.7)? I've done some work in 9.0 to prune unneeded columns, and there's a good chance this is already taken care of...

[zulander1]

@roji you are right, it looks like it is resolved ! Thank you!

      SELECT TOP(1) (
          SELECT COALESCE(SUM([t0].[Quantity]), 0)
          FROM [tOrderDetails] AS [t0]
          INNER JOIN (
              SELECT [t1].[Id], [t1].[IncludeInSalesLevel]
              FROM [tProduct] AS [t1]
              WHERE [t1].[IsDeleted] = CAST(0 AS bit)
          ) AS [t2] ON [t0].[ProductId] = [t2].[Id]
          WHERE [t0].[IsDeleted] = CAST(0 AS bit) AND [t].[Id] = [t0].[OrderId] AND [t2].[IncludeInSalesLevel] = CAST(1 AS bit)) AS [total]
      FROM [tOrder] AS [t]
      WHERE [t].[IsDeleted] = CAST(0 AS bit)

[roji]

Great, thanks for confirming!

[roji]

Duplicate of #31083