Duplicate primary key name when running Update-Database

Question

I am using EF Core 7, with LIve Migrations enable in a code first project. I have a model using TPT strategy.

When I generate the model, it gives me the error:

Failed executing DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [azure_regional_zone] (
    [regional_zone_code] nvarchar(20) NOT NULL,
    [dr_region] nvarchar(200) NOT NULL,
    [main_region] nvarchar(200) NOT NULL,
    CONSTRAINT [PK_regional_zone] PRIMARY KEY ([regional_zone_code]),
    CONSTRAINT [FK_azure_regional_zone_regional_zone_regional_zone_code] FOREIGN KEY ([regional_zone_code]) REFERENCES [regional_zone] ([regional_zone_code]) ON DELETE CASCADE
);

I have a simple hierarchy and model, were the entity AzureRegionalZone extends the class RegionalZone for adding Azure specific parameters. I am also applying a custom naming startegy, applied on the OnModelCreating(ModelBuilder builder) function, after the call to base.OnModelCreating(builder);

These are my classes:

    /// <summary>
    /// Defines a region where the system is implemented.
    /// </summary>
    public class RegionalZone
    {
#nullable disable

        /// <summary>
        /// Cloud provider where the region is implemented.
        /// </summary>
        public virtual CloudProviders CloudProvider { get; set; } = CloudProviders.None;

        /// <summary>
        /// Unique code for the region.
        /// </summary>
        public string Code { get; set; }

        /// <summary>
        /// Name for the region.
        /// </summary>
        public string Name { get; set; }

#nullable enable

    }

    /// <summary>
    /// EF mapping for the entity <see cref="RegionalZone"/>.
    /// </summary>
    internal class RegionalZoneDbMap : IEntityTypeConfiguration<RegionalZone>
    {
        /// <inheritdoc/>
        public void Configure(EntityTypeBuilder<RegionalZone> builder)
        {
            builder.ToTable("regional_zone");

            builder.HasKey(k => k.Code);

            builder.Property(p => p.Code)
                .HasColumnName("regional_zone_code")
                .HasMaxLength(DbDefs.CodeSize)
                .IsRequired();

            builder.Property(p => p.Name)
                .HasColumnName("regional_zone_name")
                .HasMaxLength(DbDefs.NameSize)
                .IsRequired();

            builder.Property(p => p.CloudProvider)
                .HasColumnName("cloud_provider")
                .HasMaxLength(DbDefs.EnumNameSize)
                .HasConversion<EnumToStringConverter<CloudProviders>>()
                .IsRequired();
        }
    }

    /// <summary>
    /// Extension of the class <see cref="RegionalZone"/> for implementing a region in Azure.
    /// </summary>
    public class AzureRegionalZone : RegionalZone
    {
        /// <summary>
        /// Crete a new instance of class <see cref="AzureRegionalZone"/>.
        /// </summary>
        public AzureRegionalZone()
        {
            CloudProvider = CloudProviders.Azure;
        }

#nullable disable

        /// <summary>
        /// Name of the disaster recovery region implemented in Azure.
        /// </summary>
        public string DrRegion { get; set; }

        /// <summary>
        /// Name of the main region implemented in Azure.
        /// </summary>
        public string MainRegion { get; set; }

        /// <inheritdoc/>
        public override CloudProviders CloudProvider 
        { 
            get => base.CloudProvider; 
            set => base.CloudProvider = CloudProviders.Azure; 
        }

#nullable enable
    }

    /// <summary>
    /// EF mapping for the entity <see cref="AzureRegionalZone"/>.
    /// </summary>
    internal class AzureRegionalZoneDbMap : IEntityTypeConfiguration<AzureRegionalZone>
    {
        /// <inheritdoc/>
        public void Configure(EntityTypeBuilder<AzureRegionalZone> builder)
        {
            builder.ToTable("azure_regional_zone");

            builder.Property(p => p.MainRegion)
                .HasColumnName("main_region")
                .HasMaxLength(DbDefs.NameSize)
                .IsRequired();

            builder.Property(p => p.DrRegion)
                .HasColumnName("dr_region")
                .HasMaxLength(DbDefs.NameSize)
                .IsRequired();
        }
    }

I figured that, in the final model after building, both tables shares the same PK definition, so, no matter what name I apply to the key, it will generate a duplicate object name in the database scripts. If I do not apply any change in the PK name, it works properly, because the PK name is replaced elsewhere, but still have the same name in the model snapshot created in the Migrations folder.

But I must apply a custom name to be aligned with corporate naming standards, and when I define this custom name, the database cannot be updated due to multiple primary keys with the same name.

These are the generated definitions after Add-Migration:

            migrationBuilder.CreateTable(
                name: "regional_zone",
                columns: table => new
                {
                    regionalzonecode = table.Column<string>(name: "regional_zone_code", type: "nvarchar(20)", maxLength: 20, nullable: false),
                    cloudprovider = table.Column<string>(name: "cloud_provider", type: "nvarchar(50)", maxLength: 50, nullable: false),
                    regionalzonename = table.Column<string>(name: "regional_zone_name", type: "nvarchar(200)", maxLength: 200, nullable: false),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_regional_zone__code", x => x.regionalzonecode);
                });

            migrationBuilder.CreateTable(
                name: "azure_regional_zone",
                columns: table => new
                {
                    regionalzonecode = table.Column<string>(name: "regional_zone_code", type: "nvarchar(20)", maxLength: 20, nullable: false),
                    drregion = table.Column<string>(name: "dr_region", type: "nvarchar(200)", maxLength: 200, nullable: false),
                    mainregion = table.Column<string>(name: "main_region", type: "nvarchar(200)", maxLength: 200, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_regional_zone__code", x => x.regionalzonecode);
                    table.ForeignKey(
                        name: "FK_azure_regional_zone_regional_zone_regional_zone_code",
                        column: x => x.regionalzonecode,
                        principalTable: "regional_zone",
                        principalColumn: "regional_zone_code",
                        onDelete: ReferentialAction.Cascade);
                });

(please notice that both tables above have the same PK name)

Another side problem is that the FK definition for the one-to-one relation between azure_regional_zone and regional_zone is created implicitly, so it is not part of the model, and I cannot apply the custom namespace on it.

My current solution was to create a custom SQL migrations class, inheriting the class MigrationsSqlGenerator, and applying the custom naming again only to the PK, when generating the database scripts. This make the model work, but breaks the naming conventions for the PK, and does not solve the problem of naming the FK.

I really do not know how to solve this problem, and I am asking myself if it is not a bug in the library, or I am doing something wrong here.

Answer 1

I have a fully customized DbContext class, so, makes not much sense to share the full code. Basically, in the method OnModelCreating(), I have the following call:

        /// <summary>
        /// <see cref="DbContext.OnModelCreating(ModelBuilder)"/>.
        /// </summary>
        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.ApplyConfiguration(new GeographicRegionDbMap())
                .ApplyConfiguration(new RegionalZoneDbMap())
                .ApplyConfiguration(new AzureRegionalZoneDbMap()));

            base.OnModelCreating(builder);

            builder.UseRqaNamingConventions();
            builder.ApplyDescriptionsFromXml();
        }

Answer 2

In the DbContext class, I have the following call, in the method OnModelCreating()

        /// <summary>
        /// <see cref="DbContext.OnModelCreating(ModelBuilder)"/>.
        /// </summary>
        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.ApplyConfiguration(new GeographicRegionDbMap())
                .ApplyConfiguration(new RegionalZoneDbMap())
                .ApplyConfiguration(new AzureRegionalZoneDbMap()));

            base.OnModelCreating(builder);

            builder.UseRqaNamingConventions();
            builder.ApplyDescriptionsFromXml();
        }