Exception thrown with split tables: System.InvalidOperationException: 'Sequence contains more than one element'

[cobrul]

With EF Core 8.0.7 and 9.0.0, if I have an entity split to more than one table I get an error when trying to check for null. The error appears in RelationalSqlTranslatingExpressionVisitor method TryRewriteEntityEquality, due to a call to SingleOrDefault().

Code to reproduce (I am using SqlServer but it appears also with Sqlite):

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace EfCoreBug1Demo;

class Program
{
    static void Main()
    {
        var connection = new SqliteConnection("Data Source=:memory:");
        connection.Open();

        var options = new DbContextOptionsBuilder<TestContext>()
            .UseSqlite(connection)
            .Options;

        using (var dbContext = new TestContext(options))
        {
            dbContext.Database.EnsureDeleted();
            dbContext.Database.EnsureCreated();
        }

        using (var dbContext = new TestContext(options))
        {
            var query = dbContext.Set<InvoiceLine>().AsNoTracking()
                        .Select(x => new
                        {
                            x.Id,
                           // throws exception
                           Name = x.Invoice.Creator != null ? x.Invoice.Creator!.FullName : "",
                           // works
                           //Name = x.Invoice.CreatorId != null ? x.Invoice.Creator!.FullName : ""
                        });

            var result = query.ToArray();
        }
    }
}

internal class TestContext : DbContext
{
    public TestContext(DbContextOptions options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var assembly = typeof(Program).Assembly;
        modelBuilder.ApplyConfigurationsFromAssembly(assembly);
    }
}

internal class Person
{
    public int Id { get; set; }
    public string FullName { get; set; } = "";
    public byte[] Photo { get; set; } = [];
    public int PhotoVersion { get; set; } = 1;
}

internal class Invoice
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public required Person Customer { get; set; }
    public int? CreatorId { get; set; }
    public Person? Creator { get; set; }
}

internal class InvoiceLine
{
    public int Id { get; set; }
    public int InvoiceId { get; set; }
    public required Invoice Invoice { get; set; }
}


internal class InvoiceConfig: IEntityTypeConfiguration<Invoice>
{
    public void Configure(EntityTypeBuilder<Invoice> builder)
    {
        builder.HasOne(e => e.Customer).WithMany().HasForeignKey(e => e.CustomerId).OnDelete(DeleteBehavior.Restrict);
        builder.HasOne(e => e.Creator).WithMany().HasForeignKey(e => e.CreatorId).OnDelete(DeleteBehavior.SetNull);
        builder.HasKey(e => e.Id);
        builder.ToTable("Invoices");
    }
}

internal class InvoiceLineConfig : IEntityTypeConfiguration<InvoiceLine>
{
    public void Configure(EntityTypeBuilder<InvoiceLine> builder)
    {
        builder.HasOne(e => e.Invoice).WithMany().HasForeignKey(e => e.InvoiceId).OnDelete(DeleteBehavior.Cascade);
        builder.HasKey(e => e.Id);
        builder.ToTable("InvoiceLines");
    }
}

internal class PersonConfig : IEntityTypeConfiguration<Person>
{
    public void Configure(EntityTypeBuilder<Person> builder)
    {     
        builder.Property(e => e.FullName).IsRequired().HasMaxLength(132);

        builder.ToTable("Persons")
            .SplitToTable("PersonsPhotos", t =>
            {
                t.Property(e => e.Id).HasColumnName("PersonId");
                t.Property(e => e.Photo);
                t.Property(e => e.PhotoVersion);
            });

        builder.HasKey(e => e.Id);
        builder.ToTable("Persons");  
    }    
}

Stack trace:

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__DisplayClass57_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.Sqlite.Query.Internal.SqliteSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitConditional(ConditionalExpression conditionalExpression)
   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.VisitNew(NewExpression newExpression)
   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.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.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at EfCoreBug1Demo.Program.Main() in d:\EfCoreBug1Demo\Program.cs:line 33

EF Core version: 8.0.7 / 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer / Microsoft.EntityFrameworkCore.Sqlite
Target framework: (e.g. .NET 8.0)
Operating system: Windows 10
IDE: Visual Studio 2022 17.11.4