List<string> property causes inclusion of additional columns that are not used in the projection.

[Thijs153]

List property causes inclusion of addition columns that are not used in the projection.

Hi there, I have a strange issue going on when I include a List property in the Select when querying the DB. It includes all the additional columns even if they are not used in the projection.

---

I'm applying DDD, so I have my "Datamodel" Entity with a lot of Owned Entity relations defined:

public sealed class Datamodel : AggregateRoot
{
    private Datamodel() : base(default!) { }
    private Datamodel(Guid id) : base(id) { }

    private readonly List<string> _supportedLocales = [];
    private readonly List<Classification> _classifications = [];
    private readonly List<Entity> _entities = [];
    private readonly List<FieldGroup> _fieldGroups = [];
    private readonly List<Picklist> _picklists = [];
    private readonly List<Field> _fields = [];

    public string Version { get; private set; } = default!;
    public ModelStatus Status { get; private set; }
    public string DefaultLanguage { get; private set; } = default!;
    public IReadOnlyList<string> SupportedLocales => _supportedLocales.AsReadOnly();
    public IReadOnlyList<Classification> Classifications => _classifications.AsReadOnly();
    public IReadOnlyList<Entity> Entities => _entities.AsReadOnly();
    public IReadOnlyList<FieldGroup> FieldGroups => _fieldGroups.AsReadOnly();
    public IReadOnlyList<Picklist> Picklists => _picklists.AsReadOnly();
    public IReadOnlyList<Field> Fields => _fields.AsReadOnly();
}

Note the SupportedLocales property.

This Datamodel has the following ef configuration:

private static void ConfigureDatamodels(EntityTypeBuilder<Datamodel> builder)
{
    builder.IsMultiTenant();
    
    builder.HasKey(model => model.Id);
    
    builder.Property(model => model.Id)
        .ValueGeneratedNever();
    
    builder.Property(model => model.Status)
        .HasConversion<EnumToStringConverter<ModelStatus>>();

    builder.Property<List<string>>("_supportedLocales")
        .HasColumnName("SupportedLocales");
}

and then each of the Owned Entities is configured like this:

private static void ConfigureClassifications(EntityTypeBuilder<Datamodel> builder)
{
    builder.OwnsMany(m => m.Classifications, cb =>
    {
        cb.WithOwner().HasForeignKey("DatamodelId");

        cb.HasKey(classification => classification.Id);
        
        cb.Property(classification => classification.Id)
            .ValueGeneratedNever();

        cb.OwnsOne<TranslatableProperty>(classification => classification.Name, nb =>
            nb.Property<Dictionary<string, string>>("_translations")
                .HasColumnName("NameTranslations")
                .HasValueJsonConverter());
    });
}

Now I understand that Owned Entities are auto-included, now when querying this isn't always needed, and with the Select statement, you can select the specific data that you need, like this:

// Works - only selects the [Id] from the table.
var models = await _readRepository.Queryable().Select(model => new
{
    model.Id,
}).ToListAsync(cancellationToken); 

(Queryable() returns a DbSet with .AsNoTracking())

this call results in the following SQL:

SELECT [d].[Id]
FROM [Datamodel] AS [d]
WHERE [d].[TenantId] = @__ef_filter__Id_0

Including one of the owned entities in the select, also works fine:

var models = await _readRepository.Queryable().Select(model => new
{
    model.Id,
    model.Classifications, // IReadOnlyList<Classification> [Owned Entity]
}).ToListAsync(cancellationToken);

results into:

SELECT [d].[Id], [c].[Id], [c].[CreatedOn], [c].[DatamodelId], [c].[ExternalId], [c].[ExternalReference], [c].[Index], [c].[TechnicalName], [c].[UpdatedOn], [c].[NameTranslations]
FROM [Datamodel] AS [d]
LEFT JOIN [Classification] AS [c] ON [d].[Id] = [c].[DatamodelId]
WHERE [d].[TenantId] = @__ef_filter__Id_0
ORDER BY [d].[Id] 

But the moment I include that SupportedLocales property, all of the other additional columns and Owned Entities are included in the SQL, even when they're not needed.

// Doesn't work - Also includes all the owned entities.
var models = await _readRepository.Queryable().Select(model => new
{
    model.Id,
    model.SupportedLocales, // List<string>
}).ToListAsync(cancellationToken);

Is this a bug or is this intended behavior? Is there a way to query the SupportedLocales without including everything, this is causing performance issues.

I've tried .AsNoTracking() and .IgnoreAutoIncludes(), but nothing seems to work.

Include provider and version information

EF Core version:
"Microsoft.EntityFrameworkCore" Version="8.0.2"
"Microsoft.EntityFrameworkCore.Design" Version="8.0.0"
"Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.2"
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows 11
IDE: JetBrains Rider 2024.2

[maumar]

@Thijs153 Read-only primitive collections are not supported on EF8. SupportedLocales is considered not mapped so EF performs a client evaluation on the projection, which requires bringing entire entity from the database. In the upcoming EF9 we improved this and now you are able to use read-only primitive collections. See: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew#read-only-primitive-collections

On EF9, for read-only collections with a backing field you can configure it by doing:

modelBuilder.Entity<Datamodel>().PrimitiveCollection(x => x.SupportedLocales);

On EF8 you will have to convert the collection to read-write, sorry.

[maumar]

dupe of #31722