Invalid name of Index using inheritance and many-to-many

[amyboose]

My code:

using Microsoft.EntityFrameworkCore;

namespace EfCoreTpc;
public class Program
{
    public static async Task Main(params string[] args)
    {
        IHost host = Host.CreateDefaultBuilder()
        .ConfigureServices(services =>
        {
            services.AddDbContext<MyContext>(builder =>
            {
                builder.UseNpgsql("Host=localhost;Port=7435;Database=testdb;Username=admin;Password=testpass")
                    .UseSnakeCaseNamingConvention();
            });
        })
        .Build();
    }
}

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

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

public abstract class CampaignProduct
{
    public int CampaignId { get; set; }
    public int ProductId { get; set; }
    public Campaign Campaign { get; set; } = null!;
    public Product Product { get; set; } = null!;
}

public class CampaignProductBinding : CampaignProduct
{

}

public class CampaignProductUnbinding : CampaignProduct
{

}

public class MyContext : DbContext
{
    public MyContext(DbContextOptions options) : base(options) { }
    public DbSet<Product> Products { get; set; }
    public DbSet<Campaign> Campaigns { get; set; }
    public DbSet<CampaignProduct> CampaignProducts { get; set; }
    public DbSet<CampaignProductBinding> CampaignProductBindings { get; set; }
    public DbSet<CampaignProductUnbinding> CampaignProductUnbindings { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CampaignProduct>(builder =>
        {
            builder.UseTpcMappingStrategy();

            builder
                .HasKey(p => new { p.CampaignId, p.ProductId });
        });
    }
}

I've got an successful migration without using SnakeCaseNamingConvention. But using convention I've got an exception:

Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42P07
    MessageText: relation "pk_campaign_products" already exists
    File: index.c
    Line: 869
    Routine: index_create
42P07: relation "pk_campaign_products" already exists
PM> 

A migration using SnakeCaseNamingConvention:

migrationBuilder.CreateTable(
name: "campaigns",
columns: table => new
{
    id = table.Column<int>(type: "integer", nullable: false)
        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
},
constraints: table =>
{
    table.PrimaryKey("pk_campaigns", x => x.id);
});

migrationBuilder.CreateTable(
name: "products",
columns: table => new
{
    id = table.Column<int>(type: "integer", nullable: false)
        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
},
constraints: table =>
{
    table.PrimaryKey("pk_products", x => x.id);
});

migrationBuilder.CreateTable(
name: "CampaignProductBindings",
columns: table => new
{
    campaignid = table.Column<int>(name: "campaign_id", type: "integer", nullable: false),
    productid = table.Column<int>(name: "product_id", type: "integer", nullable: false)
},
constraints: table =>
{
    table.PrimaryKey("pk_campaign_products", x => new { x.campaignid, x.productid });
    table.ForeignKey(
        name: "fk_campaign_products_campaigns_campaign_id",
        column: x => x.campaignid,
        principalTable: "campaigns",
        principalColumn: "id",
        onDelete: ReferentialAction.Cascade);
    table.ForeignKey(
        name: "fk_campaign_products_products_product_id",
        column: x => x.productid,
        principalTable: "products",
        principalColumn: "id",
        onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateTable(
name: "CampaignProductUnbindings",
columns: table => new
{
    campaignid = table.Column<int>(name: "campaign_id", type: "integer", nullable: false),
    productid = table.Column<int>(name: "product_id", type: "integer", nullable: false)
},
constraints: table =>
{
    table.PrimaryKey("pk_campaign_products", x => new { x.campaignid, x.productid });
    table.ForeignKey(
        name: "fk_campaign_products_campaigns_campaign_id",
        column: x => x.campaignid,
        principalTable: "campaigns",
        principalColumn: "id",
        onDelete: ReferentialAction.Cascade);
    table.ForeignKey(
        name: "fk_campaign_products_products_product_id",
        column: x => x.productid,
        principalTable: "products",
        principalColumn: "id",
        onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateIndex(
name: "ix_campaign_products_product_id",
table: "CampaignProductBindings",
column: "product_id");

migrationBuilder.CreateIndex(
name: "ix_campaign_products_product_id",
table: "CampaignProductUnbindings",
column: "product_id");

The migration creates 2 indexes with the same name: ix_campaign_products_product_id

For example, indexes without SnakeCaseNamingConvention:

migrationBuilder.CreateIndex(
    name: "IX_CampaignProductBindings_ProductId",
    table: "CampaignProductBindings",
    column: "ProductId");

migrationBuilder.CreateIndex(
    name: "IX_CampaignProductUnbindings_ProductId",
    table: "CampaignProductUnbindings",
    column: "ProductId");

The last example show that names contains full class name.

Microsoft documentation writes:

By convention, indexes created in a relational database are named IX_type name_property name.

Provider and version information

EF Core version: 7.0.2 Database provider: Npgsql Entity Framework Core provider for PostgreSQL 7.0.1 Target framework: NET 7.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.4

[x-xx-o]

I'm experiencing the same issue with version 8.0.0-rc.2.

[roji]

Finally got around to looking into this - the root cause seems to be https://github.com/dotnet/efcore/issues/27973. The index in question is defined on the TPC root in the model, and "applied" for each TPC child. Now, in regular mode (without this plugin), RelationalIndexExtensions.GetDatabaseName(StoreObjectIdentifier) knows how to calculate the correct index name - different for each TPC child; but in this plugin, we need to be able to set a different (rewritten) name for each TPC child, but that's not supported. /cc @AndriySvyryd

In the meantime, I'll make sure to at least clear the index name, which should remove the exception - but leave the index name without rewritten.

[roji]

Opened #245 to track actually rewriting the index names on the children - refraining from rewriting for now.