Table-splitting: Entity does not load when the only nullable, shared column/property is null.

[cailenmusselman]

I believe the issue I'm experiencing is similar to these: #23564, #21488, #23488. Although I'm not sure they're exactly the same (and/or would be resolved by them) so I've decided to create a new issue.

I'm also experiencing an unnecessary self-join, which is sort of covered by #10067, but for my example the self-join occurs regardless of whether or not I'm using .Include() because of how my models are defined.

EF Core version: 5.0.2
Database provider: Microsoft.EntityFrameworkCore.Sqlite, Npgsql.EntityFrameworkCore.PostreSQL, likely Microsoft.EntityFrameworkCore.SqlServer as well.
Target framework: .NET Core 3.1
Operating system: Windows
IDE: Visual Studio 2019 16.6.5

Description:

When using table-splitting to share the Id and nullable Name of a User with Author, I'm unable to materialize Author results when the Name shared w/ User is null.
The sample doesn't exactly model my real-life scenario, but gets the point across. The gist of it being that User and Author are two different domain models that I'd like to map to the same table.

Expected: When querying for Author, results should be returned regardless of whether or not the Author/User has name, since both entity configurations specify it as optional.

Actual: When querying for Author, no results are returned because a WHERE Name IS NOT NULL condition is included in the generated SQL.

Work around: If I create a shadow-property on Author mapped to the same column as a non-nullable property/column on User I'm able to get the results I expect.

Code (also attached): HasOneTableSplitting.zip

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Linq;

namespace HasOneTableSplitting
{
    public class Program
    {
        /***************
         * 
         * With this set to "false" the User's name is set up as non-nullable and the query to get 
         * an Author does not include a "WHERE Name IS NOT NULL" clause.
         * However, with this set to "true" (the User's name is optional) the query to get 
         * an Author includes a "WHERE Name IS NOT NULL" clause, even though my query makes no such specification
         * and an Author/User without a name is perfectly valid (in this example, anyways).
         * 
         * NOTE: If I add the additional required/non-null property "AnotherProperty" to Author the query succeeds because
         * the generated query contains "WHERE Name IS NOT NULL OR AnotherProperty IS NOT NULL" and AnotherProperty
         * will always be non-null.
         * 
         ***************/ 
        public const bool UseNullableName = true;


        static void Main(string[] args)
        {
            int id = 0;

            using (var context = new SqliteContext())
            {
                context.Database.EnsureCreated();

                context.Users.Add(new User(Program.UseNullableName ? null : "Me"));
                context.SaveChanges();

                id = context.Users.Single().Id;
            }

            using (var context = new SqliteContext())
            {
                var users = context.Users.ToList();

                /**
                 Scenario 1: With a nullable "Name" shared by User/Author I get no results because of the undesired where condition:
                        SELECT "u"."Id", "u"."Name"
                        FROM "Users" AS "u"
                        INNER JOIN "Users" AS "u0" ON "u"."Id" = "u0"."Id"
                        WHERE "u"."Name" IS NOT NULL
                 
                 Scenario 2: With a non-nullable "Name" shared by User/Author I get expected results, but an unnessesary self-join:
                        SELECT "u"."Id", "u"."Name"
                        FROM "Users" AS "u"
                        INNER JOIN "Users" AS "u0" ON "u"."Id" = "u0"."Id"

                 Scenario 3: With a nullable "Name" but non-nullable "AnotherProperty" also included in Author I get expected results, but an unnessesary self-join:
                        SELECT "u"."Id", "u"."AnotherProperty", "u"."Name"
                        FROM "Users" AS "u"
                        INNER JOIN "Users" AS "u0" ON "u"."Id" = "u0"."Id"
                **/

                Console.Clear();
                var authors = context.Authors.ToList();

                if(authors.Count != 1)
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("COULD NOT FIND AUTHOR");
                    Console.ResetColor();
                    return;
                }
            }

            using (var context = new SqliteContext())
            {
                var author = context.Authors.Include(author => author.Books).Where(author => author.Id == id).Single();
                author.PublishedNewBook("My New Book");
                context.SaveChanges();
            }

            using (var context = new SqliteContext())
            {
                // This part works, assuming an Author is returned.
                var author = context.Authors.Include(author => author.Books).Where(author => author.Id == id).Single();
                if (author == null || author.Books.Count() != 1)
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("PUBLISHED BOOK DID NOT SAVE");
                    Console.ResetColor();
                    return;
                }
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("SUCCESS");
            Console.ResetColor();
        }
    }

    public class Context : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Author> Authors { get; set; }

        public Context(DbContextOptions options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.ApplyConfigurationsFromAssembly(typeof(Program).Assembly);
        }
    }

    public class User
    { 
        public int Id { get; private set; }
        public string Name { get; private set; }
        public string AnotherProperty { get; private set; }
        public DateTime SignedUpOn { get; set; }

        private User() { }

        public User (string name)
        {
            this.Name = name;
            this.AnotherProperty = "asdf";
            this.SignedUpOn = DateTime.UtcNow;
        }
    }

    public class Author
    { 
        public int Id { get; private set; }
        public string Name { get; private set; }
        //public string AnotherProperty { get; private set; }

        public IEnumerable<PublishedBook> Books => _books?.ToList();
        private List<PublishedBook> _books;

        private Author() { }

        public void PublishedNewBook(string title)
        {
            if(_books == null)
            {
                throw new Exception("Forgot to load Author's books.");
            }
            _books.Add(new PublishedBook(this.Id, title, DateTime.UtcNow));
        }
    }

    public class PublishedBook
    { 
        public int Id { get; private set; }
        public string Title { get; private set; }
        public DateTime PublishedOn { get; private set; }

        private int _authorId;

        private PublishedBook() { }

        public PublishedBook(int authorId, string title, DateTime publishedOn)
        {
            this.Title = title;
            this.PublishedOn = publishedOn;

            _authorId = authorId;
        }
    }

    public class UserDbConfiguration : IEntityTypeConfiguration<User>
    {
        public void Configure(EntityTypeBuilder<User> builder)
        {
            builder.ToTable("Users");
            builder.HasKey(b => b.Id);
            builder.Property(b => b.Id).HasColumnName("Id");
            builder.Property(b => b.Name).HasColumnName("Name").IsRequired(!Program.UseNullableName);
            builder.Property(b => b.AnotherProperty).HasColumnName("AnotherProperty").IsRequired(true);
        }
    }

    public class AuthorDbConfiguration : IEntityTypeConfiguration<Author>
    {
        public void Configure(EntityTypeBuilder<Author> builder)
        {
            builder.ToTable("Users");
            builder.HasOne<User>().WithOne().IsRequired(true).HasForeignKey<Author>(author => author.Id);
            builder.HasKey(author => author.Id);
            builder.Property(author => author.Id).HasColumnName("Id");
            builder.Property(author => author.Name).HasColumnName("Name").IsRequired(!Program.UseNullableName);

            // With this uncommented and AnotherProperty added to Author, even with a nullable Name the Author is returned correctly.
            // builder.Property(b => b.AnotherProperty).HasColumnName("AnotherProperty").IsRequired(true);
            // Or, we could add a shadow property on a non-nullable property I guess
            // builder.Property<string>("AnotherProperty").HasColumnName("AnotherProperty").IsRequired(true);

            builder.HasMany(author => author.Books).WithOne().HasForeignKey("_authorId");
        }
    }

    public class BookDbConfiguration : IEntityTypeConfiguration<PublishedBook>
    {
        public void Configure(EntityTypeBuilder<PublishedBook> builder)
        {
            builder.Property<int>("_authorId").HasColumnName("AuthorId"); // backing field to use for Author relationship
            builder.HasKey("_authorId", nameof(PublishedBook.Id));
        }
    }

    public class SqliteContext : Context
    {
        private static SqliteConnection _connection;

        static SqliteContext()
        {
            _connection = new SqliteConnection("Datasource=:memory:");
            _connection.Open();
        }

        public SqliteContext()
            : base(new DbContextOptionsBuilder<Context>().UseSqlite(_connection).LogTo(Console.WriteLine).Options)
        {
        }
    }

    public class NpgsqlContext : Context
    {
        public NpgsqlContext()
            : base(new DbContextOptionsBuilder<Context>().UseNpgsql("User ID=postgres;Password=xxx;Host=localhost;Port=5432;Database=EF_502;Pooling=true").LogTo(Console.WriteLine).Options)
        {
        }
    }

    public class DesignTimeControlContextFactory : IDesignTimeDbContextFactory<Context>
    {
        public Context CreateDbContext(string[] args)
        {
            return new NpgsqlContext();
        }
    }

}

[cailenmusselman]

If my interpretation is correct, looks like #23635 maybe actually fix my issue - "If there are no non-shared properties then it is always present and act like a required dependent"

[AndriySvyryd]

This is by-design. By default Author is considered optional and Name is the only column that can be used to determine wether it should be materialized.

To configure Author to always materialize change this line:

 builder.HasOne<User>().WithOne().IsRequired(true).HasForeignKey<Author>(author => author.Id);

to

 builder.HasOne<User>().WithOne().IsRequired(true).HasForeignKey<Author>(author => author.Id)
    .Metadata.IsRequiredDependent();