Error in combination with EF and SplitToTable

[Quietscheente]

Assemblies affected

ASP.NET Core OData 8.2.5 Microsoft.EntityFrameworkCore 8.0.6 [EDIT] SqlServer 8.0.6

Describe the bug

In combination with EF and SplitToTable I get an error "System.InvalidOperationException: Sequence contains more than one element". The error is in EF, so I'm not 100% sure if it is an oData or EF problem. But I tried to reproduce the problem with a hand made linq EF query that returns equivalent results (like dbContext.Tests.Include(t => t.ParentNavigation)) that oData returns and everything works fine. I can't reproduce the bug without oData. So I hope I'm in the right place.

error is:

dbug: 07.08.2024 10:45:55.928 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query)
      Compiling query expression:
      'DbSet<Test>()
          .AsNoTracking()
          .Select($it => new SelectAllAndExpand<Test>{
              Model = __TypedProperty_0,
              Instance = $it,
              UseInstanceForProperties = True,
              Container = new SingleExpandedProperty<SelectAll<Test>>{
                  Name = "parentNavigation",
                  Value = new SelectAll<Test>{
                      Model = __TypedProperty_1,
                      Instance = $it.ParentNavigation,
                      UseInstanceForProperties = True
                  }
                  ,
                  IsNull = $it.ParentNavigation == null
              }

          }
          )'
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      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.RelationalSqlTranslatingExpressionVisitor.<>c__DisplayClass62_0.<TryRewriteStructuralTypeEquality>g__TryRewriteEntityEquality|0(Expression& result)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TryRewriteStructuralTypeEquality(ExpressionType nodeType, Expression left, Expression right, Boolean equalsMethod, Expression& result)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
         at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
         at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         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__DisplayClass9_0`1.<Execute>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.IEnumerable.GetEnumerator()
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()

Reproduce steps

• Use the "Test" and "TestDescription" models
• Configure the modelBuilder in EF
• Create a oData controller that returns db.Set<Test>().AsNoTracking(); as ActionResult<IQueryable<Test>> for GET
• Create the objects like in attached CreateTables.txt (inserts at the bottom)
• Call Url:/test?$expand=ParentNavigation

Data Model

[Table("Test")]
[Index("Parent", Name = "index1")]
public partial class Test
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    [StringLength(50)]
    [Unicode(false)]
    public string? Name { get; set; }

    [Column("parent")]
    public int? Parent { get; set; }

    public string? Description { get; set; }

    [InverseProperty("ParentNavigation")]
    public virtual ICollection<Test> InverseParentNavigation { get; set; } = new List<Test>();

    [ForeignKey("Parent")]
    [InverseProperty("InverseParentNavigation")]
    public virtual Test? ParentNavigation { get; set; }

    [InverseProperty("IdNavigation")]
    public virtual TestDescription? TestDescription { get; set; }
}

[Table("TestDescription")]
public partial class TestDescription
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("description")]
    public string Description { get; set; } = null!;

    [ForeignKey("Id")]
    [InverseProperty("TestDescription")]
    public virtual Test IdNavigation { get; set; } = null!;
}

modelBuilder.Entity<Models.DB.Test>(entity =>
{
    entity.HasOne(d => d.ParentNavigation).WithMany(p => p.InverseParentNavigation).HasConstraintName("FK_Test_Test");

    entity
        .SplitToTable(
            "TestDescription",
            tableBuilder =>
            {
                tableBuilder.Property(test => test.Id);
                tableBuilder.Property(test => test.Description);
            });
});

modelBuilder.Entity<Models.DB.TestDescription>(entity =>
{
    entity.Property(e => e.Id).ValueGeneratedNever();

    entity.HasOne(d => d.IdNavigation).WithOne(p => p.TestDescription)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_TestDescription_id");
});

EDM (CSDL) Model

<EntityType Name="Test">
    <Key>
        <PropertyRef Name="id"/>
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false"/>
    <Property Name="name" Type="Edm.String"/>
    <Property Name="parent" Type="Edm.Int32"/>
    <Property Name="description" Type="Edm.String"/>
    <NavigationProperty Name="inverseParentNavigation" Type="Collection(BisDB.Models.DB.Test)"/>
    <NavigationProperty Name="parentNavigation" Type="BisDB.Models.DB.Test">
        <ReferentialConstraint Property="parent" ReferencedProperty="id"/>
    </NavigationProperty>
    <NavigationProperty Name="testDescription" Type="BisDB.Models.DB.TestDescription"/>
</EntityType>

<EntityType Name="TestDescription">
    <Key>
        <PropertyRef Name="id"/>
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false"/>
    <Property Name="description" Type="Edm.String" Nullable="false"/>
    <NavigationProperty Name="idNavigation" Type="BisDB.Models.DB.Test" Nullable="false">
        <ReferentialConstraint Property="id" ReferencedProperty="id"/>
    </NavigationProperty>
</EntityType>

Additional context

I also tried /test?$expand=InverseParentNavigation and everything works fine

CreateTables.txt

[julealgon]

What EF provider are you using (the stacktrace seems to point to SqlServer?) and what version?

Does the problem go away if you just switch the EF configuration to a single-table mapping?

[Quietscheente]

SqlServer 8.0.6

If I remove public string? Description { get; set; } from class Test and the .SplitToTable it works fine.

[EDIT] Even with expands like: /test?$expand=ParentNavigation($expand=TestDescription),TestDescription