Projecting IQueryable leads to SQL Server throwing exception

Question

I am using Automapper's queryable extension's ProjectTo to query my EF Core 6 entities based the DTO class queryable. The entities are quite large objects graphs, but the projection has been working well. Except for one entity.

Depending on the resulting filtered data (so this dependent on the returned data), EF Core receives an error message from SQL Server:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot create a row of size 8310 which is greater than the allowable maximum row size of 8060.

Important to note: the whole database is created by code first data migrations (EF Core 3.1 onwards). I did rebuild all tables with ALTER TABLE [dbo].[Foos] REBUILD just to be sure.

When I execute the query directly against the entity (so not projecting from the DTO class), the issue does not surface.

What could the projection configuration do to cause this?

public class Foo
{
    public List<Bar> Bars { get; set; }
    public List<Baz> Bazs { get; set; }
}

public class Bar
{
    public string Description { get; set; }
    //... Other properties and collections
}

public class Baz
{
    public int Quantity { get; set; }
    //... Other properties and collections
}

// Projection
IQueryable<FooDTO> foosQuery = dbContext.Foos
    .AsNoTracking()
    .ProjectTo<FooDTO>(mapperConfiguration);

Using below projection configuration will throw the exception on execution:

var mapperConfiguration = new MapperConfiguration(cfg =>
{
    cfg.CreateProjection<Foo, FooDTO>();
    cfg.CreateProjection<Bar, BarDTO>();
    cfg.CreateProjection<Baz, BazDTO>();
});

This configuration fails as well:

var mapperConfiguration = new MapperConfiguration(cfg =>
{
    cfg.CreateProjection<Foo, FooDTO>();
    cfg.CreateProjection<Bar, BarDTO>()
        // Ignore all properties individually like this:
        .ForMember(x => x.Description, opt => opt.Ignore());
    cfg.CreateProjection<Baz, BazDTO>();
});

This however does not fail:

var mapperConfiguration = new MapperConfiguration(cfg =>
{
    cfg.CreateProjection<Foo, FooDTO>()
        .ForMember(x => x.Bars, opt => opt.Ignore());
    cfg.CreateProjection<Bar, BarDTO>();
    cfg.CreateProjection<Baz, BazDTO>();
});

And the below query against the entity also does not fail:

IQueryable<Foo> foosQuery = dbContext.Foos
    .Include(x => x.Bars).Include(x => x.Bazs).AsNoTracking();

Answer 1

With the guidance of (the always helpful and knowledgeable!) Ivan Stoev, the issue was resolved by splitting the query using AsSplitQuery.

Single query mode (the default) will create query containing all selected columns from Foo + all selected from Bar + all selected from Baz. EF Core cannot track that, moreover this is a specific SqlServer limitation. Split query will execute 3 SQL queries containing only the selected columns from the corresponding tables.

// Projection
IQueryable<FooDTO> foosQuery = dbContext.Foos
    .AsNoTracking()
    .AsSplitQuery()
    .ProjectTo<FooDTO>(mapperConfiguration);