TPC: generated index use parent name

[benedict-odonovan]

Was pointed here from the dotnet/efcore repository.

The preview versions of EF7 support Table per Concrete Type inheritance which is currently incompatible with .UseSnakeCaseNamingConvention. Generated indexes use the name of the parent type rather than that of the child type, causing migrations to fail because of duplicate index names.

Entity Models:

public abstract class BaseEntityTrackable<TKey>
{
    public TKey Id { get; set; }
    public DateTime CreatedOn { get; set; }
    public int CreatedById { get; set; }
    public Consultant CreatedBy { get; set; }

    public DateTime EditedOn { get; set; }
    public int EditedById { get; set; }
    public Consultant EditedBy { get; set; }
}

[Table("candidates")]
public class Candidate : BaseEntityTrackable<int>
{
    public string FirstName { get; set; }
}

[Table("addresses")]
public class Address : BaseEntityTrackable<int>
{
    public string? PostCode { get; set; }
}

[Table("consultants")]
public class Consultant
{
    public int Id { get; set; }
}

In EntityContext.cs:

public DbSet<BaseEntityTrackable<int>> BaseEntityTrackables { get; set; }
public DbSet<Candidate> Candidates { get; set; }
public DbSet<Address> Addresses { get; set; }
public DbSet<Consultant> Consultants { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BaseEntityTrackable<int>>().UseTpcMappingStrategy();

    modelBuilder.Entity<BaseEntityTrackable<int>>().Property(e => e.CreatedOn).HasDefaultValueSql("current_timestamp").ValueGeneratedOnAdd();
    modelBuilder.Entity<BaseEntityTrackable<int>>().Property(e => e.EditedOn).HasDefaultValueSql("current_timestamp").ValueGeneratedOnAddOrUpdate();
}

In Startup.cs:

builder.Services.AddDbContext<EntityContext>(options =>
    options.UseSnakeCaseNamingConvention()
    );

In the generated migration:

migrationBuilder.CreateIndex(
                name: "ix_base_entity_trackables_created_by_id",
                table: "addresses",
                column: "created_by_id");

            migrationBuilder.CreateIndex(
                name: "ix_base_entity_trackables_edited_by_id",
                table: "addresses",
                column: "edited_by_id");

            migrationBuilder.CreateIndex(
                name: "ix_base_entity_trackables_created_by_id",
                table: "candidates",
                column: "created_by_id");

            migrationBuilder.CreateIndex(
                name: "ix_base_entity_trackables_edited_by_id",
                table: "candidates",
                column: "edited_by_id");

When update-database is run the following error is returned:
42P07: relation "ix_base_entity_trackables_created_by_id" already exists

System Info:
EF Core version: 7.0.0-preview.7.22376.2
EFCore.NamingConventions version: 6.0.0
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL:7.0.0-preview.7
Target framework: (e.g. .NET 5.0) .NET 6.0
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.3) Visual Studio 2022 17.3

[AndriySvyryd]

Fixing this properly won't be possible until dotnet/efcore#27973, dotnet/efcore#27972 and dotnet/efcore#27971 are fixed. The best that can be done for EF 7 is skipping objects on entity types using TPC or entity splitting

[roji]

Note that this was also filed as #185. Until EF-side support is available, I've at least removing the rewriting for 8.0.0 - index names in this case aren't rewritten, but at least they don't cause duplicate name errors. Keeping this issue to track properly rewriting.