Unnecessary IS NOT NULL filter on unique indexes for TPT tables on SQL Server

[zbarnett]

It appears there is an unnecessary [ColumnName] IS NOT NULL filter added to unique indexes on TPT tables when using SQL Server.

Minimum reproducible example:

public class BaseType
{
    public int Id { get; set; }
}

public class DerivedType : BaseType
{
    public string Name { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlServer();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BaseType>().ToTable(nameof(BaseType));
        
        modelBuilder.Entity<DerivedType>(entity =>
        {
            entity.ToTable(nameof(DerivedType));

            entity.HasIndex(e => e.Name).IsUnique();
        });
    }
}

The resulting migration contains:

migrationBuilder.CreateTable(
    name: "DerivedType",
    columns: table => new
    {
        Id = table.Column<int>(type: "int", nullable: false),
        Name = table.Column<string>(type: "nvarchar(450)", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_DerivedType", x => x.Id);
        table.ForeignKey(
            name: "FK_DerivedType_BaseType_Id",
            column: x => x.Id,
            principalTable: "BaseType",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

migrationBuilder.CreateIndex(
    name: "IX_DerivedType_Name",
    table: "DerivedType",
    column: "Name",
    unique: true,
    filter: "[Name] IS NOT NULL");

Since the Name column is already not null, I would expect the index in the migration to look like:

migrationBuilder.CreateIndex(
    name: "IX_DerivedType_Name",
    table: "DerivedType",
    column: "Name",
    unique: true);

Include provider and version information

EF Core version: 8.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: macOS 13.6.3
IDE: N/A

[ajcvickers]

/cc @maumar

[maumar]

We add filter by convention to TableIndex. This happens in SqlServerIndexConvention.SetIndexFilter. We try to set indexes on nullable columns only. However, at the time we are setting the filter, entity is still considered TPH, so all the properties on derived types are nullable by definition.

[maumar]

related to #15898

[ajcvickers]

Note for triage: not a regression.