ComplexCollection Entity splitting support

[sebastianlausmann]

Bug description

When using a ComplexCollection (JSON) mapping on an entity that is split across multiple tables using SplitToTable, queries that include the complex collection fail with:

System.InvalidOperationException: Sequence contains more than one element

Additional issues observed:

• Despite using Table splitting, the framework attempts to write data for the "Tags" collection to both tables
• The [Column("tags")] attribute is ignored and the C# property name "Tags" is used instead for column mapping

Your code

#:package Microsoft.EntityFrameworkCore@10.0.5
#:package Microsoft.EntityFrameworkCore.Sqlite@10.0.5
#:property PublishAot=false

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.ComponentModel.DataAnnotations.Schema;

await using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlite(conn)
    .EnableDetailedErrors()
    .LogTo(Console.WriteLine, LogLevel.Information)
    .Options;

await using (var ctx = new AppDbContext(options))
{
    ctx.Database.EnsureCreated();

    var post = new Post
    {
        Title = "Hello",
        IsFeatured = true,
        Tags =
        [
            new Tag { Name = "a" },
            new Tag { Name = "b" }
        ]
    };
    ctx.Posts.Add(post);
    await ctx.SaveChangesAsync();
}

await using (var ctx = new AppDbContext(options))
{
    var data = await ctx.Posts
        .AsNoTracking()
        .Where(p => p.Tags.Any(t => t.Name.ToLower().Contains("a")))
        .ToListAsync();

    Console.WriteLine($"Posts count: {data.Count}");
}

[Table("posts")]
class Post
{
    [Column("id")]
    public int Id { get; set; }

    [Column("title")]
    public string? Title { get; set; }

    [Column("is_featured")]
    public bool IsFeatured { get; set; }

    [Column("tags")]
    public List<Tag> Tags { get; set; } = [];
}

class Tag
{
    public string Name { get; set; } = string.Empty;
}

class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>().HasKey(p => p.Id);
        modelBuilder.Entity<Post>().ComplexCollection(p => p.Tags, c => c.ToJson());

        modelBuilder.Entity<Post>().SplitToTable("posts_extra", tb =>
        {
            tb.Property(p => p.IsFeatured);
        });
    }
}

Stack traces

Unhandled exception. System.InvalidOperationException: Sequence contains more than one element
   at System.Linq.ThrowHelper.ThrowMoreThanOneElementException()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.GenerateJsonReader(Int32 jsonColumnIndex, ITypeBase structuralType)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessTopLevelComplexJsonProperties(StructuralTypeShaperExpression shaper, ParameterExpression instanceVariable, List`1 expressions)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AddStructuralTypeInitialization(StructuralTypeShaperExpression shaper, ParameterExpression instanceVariable, List`1 variables, List`1 expressions)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.StructuralTypeMaterializerInjector.MaterializeEntity(StructuralTypeShaperExpression shaper, ParameterExpression materializationContextVariable, ParameterExpression concreteEntityTypeVariable, ParameterExpression instanceVariable, ParameterExpression entryVariable)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.StructuralTypeMaterializerInjector.ProcessStructuralTypeShaper(StructuralTypeShaperExpression shaper)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.StructuralTypeMaterializerInjector.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.StructuralTypeMaterializerInjector.Inject(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.InjectStructuralTypeMaterializers(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, Expression& relationalCommandResolver, IReadOnlyList`1& readerColumns, LambdaExpression& relatedDataLoaders, Int32& collectionId)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args)
   at Program.<Main>$(String[] args)
   at Program.<Main>$(String[] args)
   at Program.<Main>(String[] args)

Verbose output

EF Core version

10.0.5

Database provider

Npgsql.EntityFrameworkCore.PostgreSQL, Microsoft.EntityFrameworkCore.Sqlite

Target framework

.NET 10.0

Operating system

No response

IDE

No response

[roji]

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Classification

Type: Bug
Areas: area-complex-types, area-model-building

Analysis

This issue reports three problems when combining ComplexCollection (JSON mapping) with SplitToTable. After investigation:

1. Query crash: InvalidOperationException: Sequence contains more than one element

This crash was caused by a SingleOrDefault() call in GenerateJsonReader which fails when the entity type has multiple table mappings (as happens with SplitToTable). This has already been fixed on the main branch by #37410, which changed the call to FirstOrDefault(). The fix will be available in the next preview release.

2. JSON column duplicated to both tables (still a bug)

When using ComplexCollection with SplitToTable, the JSON container column is incorrectly created on both the main table and the split table:

CREATE TABLE "posts" (
    "Id" INTEGER NOT NULL ..., "Title" TEXT NULL, "Tags" TEXT NOT NULL
);
CREATE TABLE "posts_extra" (
    "Id" INTEGER NOT NULL ..., "IsFeatured" INTEGER NOT NULL, "Tags" TEXT NOT NULL, ...
);

The Tags JSON column should only exist on the main table (since it was not explicitly assigned to the split fragment).

3. JSON data written to both tables (still a bug)

As a consequence of issue #2, INSERT statements write the Tags data to both tables:

INSERT INTO "posts" ("Tags", "Title") VALUES (@p0, @p1) RETURNING "Id";
INSERT INTO "posts_extra" ("Tags", "Id", "IsFeatured") VALUES (@p2, @p3, @p4);

4. [Column("tags")] attribute ignored

The [Column("tags")] data annotation on the complex collection property does not control the JSON container column name. This appears to be by design — the JSON container column name is set via ToJson("columnName"), not via [Column]. Using c.ToJson("tags") instead of c.ToJson() should set the desired column name.

Not a regression

Since ComplexCollection is a new feature introduced in .NET 10 previews, this is not a regression from a previous stable release.

Possible related issues

• #28442 — "Json: add support for table splitting on the entities that own json entities" (feature request for the same scenario with owned entities)
• #36171 — "EntitySplittingMissingPropertiesMainFragment does not take owned navigations/complex properties into account" (related model validation gap with entity splitting + complex properties)

Minimal repro

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.ComponentModel.DataAnnotations.Schema;

await using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlite(conn)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging()
    .Options;

await using (var ctx = new AppDbContext(options))
{
    ctx.Database.EnsureCreated();

    ctx.Posts.Add(new Post
    {
        Title = "Hello",
        IsFeatured = true,
        Tags = [new Tag { Name = "a" }, new Tag { Name = "b" }]
    });
    await ctx.SaveChangesAsync();
}

await using (var ctx = new AppDbContext(options))
{
    var data = await ctx.Posts.AsNoTracking().ToListAsync();
    Console.WriteLine($"Posts count: {data.Count}, Tags: {data[0].Tags.Count}");
}

[Table("posts")]
class Post
{
    public int Id { get; set; }
    public string? Title { get; set; }
    public bool IsFeatured { get; set; }
    public List<Tag> Tags { get; set; } = [];
}

class Tag
{
    public string Name { get; set; } = string.Empty;
}

class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>().ComplexCollection(p => p.Tags, c => c.ToJson());
        modelBuilder.Entity<Post>().SplitToTable("posts_extra", tb =>
        {
            tb.Property(p => p.IsFeatured);
        });
    }
}

Expected: Tags column only on posts table; data only inserted into posts table for Tags.

Actual: Tags column duplicated to both posts and posts_extra tables; data inserted into both tables.

Tested on EF Core 11.0.0-preview.2.26159.112 with SQLite. The query crash from the original report no longer reproduces (fixed by #37410), but the column duplication issues remain.

[roji]

@AndriySvyryd initially assigning to you for (2) and (3), which seem like they might be model building issues. Once that's done I can look at (1) afterwards (we can split to a separate issue, no pun intended).

(4) I think is a current known limitation (IIRC there's some issue somewhere already).

Note that we have #28442, but that's for owned (whereas this one's for complex).

[AndriySvyryd]

Blocked on #36172 and #36646