Entity Framework Core - Failed migration with Npgsql provider

Question

I am having an error when trying to create a database from its model using Entity Framework Core 2.0.1 and the provider npgsql 2.0.1.

The error description is:

"constraint «FK_PiezasStockExterno_ContenedoresDocumentos_IdContenedorDocume» for relation «PiezasStockExterno» already exists".

I have no call to Database.EnsureCreated() since I'm aware it causes troubles while migrating, and the database is being dropped before so I ensure it doesn't exist. It happens either using the below commands or calling that Database.EnsureCreated(). What could be the real issue?

Error script:

CREATE TABLE "public"."PiezasStockExterno" 
(    
"Id" serial NOT NULL,   
"IdContenedorDocumentosPieza" int4 NULL,    
"IdContenedorDocumentosVehiculo" int4 NULL,

CONSTRAINT "PK_PiezasStockExterno" PRIMARY KEY ("Id"),    
CONSTRAINT "FK_PiezasStockExterno_ContenedoresDocumentos_IdContenedorDocumentosPieza" 
    FOREIGN KEY ("IdContenedorDocumentosPieza") 
    REFERENCES "public"."ContenedoresDocumentos" ("Id") ON DELETE RESTRICT,    
CONSTRAINT "FK_PiezasStockExterno_ContenedoresDocumentos_IdContenedorDocumentosVehiculo" 
    FOREIGN KEY ("IdContenedorDocumentosVehiculo") 
    REFERENCES "public"."ContenedoresDocumentos" ("Id") ON DELETE RESTRICT
)

Models:

[Table("PiezasStockExterno", Schema = "public")]
public class PiezaStockExterno
{

    [Key]
    public int Id { get; set; }

    public int? IdContenedorDocumentosPieza { get; set; }

    [ForeignKey("IdContenedorDocumentosPieza")]
    public virtual ContenedorDocumentos ContenedorDocumentosPieza { get; set; }

    public int? IdContenedorDocumentosVehiculo { get; set; }

    [ForeignKey("IdContenedorDocumentosVehiculo")]
    public virtual ContenedorDocumentos ContenedorDocumentosVehiculo { get; set; }

}

[Table("ContenedoresDocumentos", Schema = "public")]
public class ContenedorDocumentos
{

    [Key]
    public int Id { get; set; }

    [InverseProperty("ContenedorDocumentos")]
    public IList<Imagen> Imagenes { get; set; }

    [InverseProperty("ContenedorDocumentos")]
    public IList<Foto> Fotos { get; set; }

    [InverseProperty("ContenedorDocumentos")]
    public IList<Documento> Documentos { get; set; }

    [InverseProperty("ContenedorDocumentos")]
    public IList<Video> Videos { get; set; }

}

Context:

public NContext(DbContextOptions<NContext> options) : base(options)
{

}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
    optionsBuilder.UseNpgsql(ConnectionString, b => b.MigrationsAssembly("WebAPI"));
    optionsBuilder.EnableSensitiveDataLogging();
    base.OnConfiguring(optionsBuilder);
}

Startup.cs in WebAPI project:

public void ConfigureServices(IServiceCollection services)
{
    services.AddEntityFrameworkNpgsql().AddDbContext<Infrastructure.Data.NContext>();       

    services.AddMvc()
        .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver()) 
        .AddJsonOptions(options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);

    AutoMapperConfig.Initialize();
}

Commands:

dotnet ef migrations add InitialMigration
dotnet ef database update

OR

Database.EnsureCreated()

InitialMigration.cs:

    migrationBuilder.CreateTable(
            name: "PiezasStockExterno",
            schema: "public",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),                    
                IdContenedorDocumentosPieza = table.Column<int>(nullable: true),
                IdContenedorDocumentosVehiculo = table.Column<int>(nullable: true)                    
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_PiezasStockExterno", x => x.Id);
                table.ForeignKey(
                    name: "FK_PiezasStockExterno_ContenedoresDocumentos_IdContenedorDocumentosPieza",
                    column: x => x.IdContenedorDocumentosPieza,
                    principalSchema: "public",
                    principalTable: "ContenedoresDocumentos",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
                table.ForeignKey(
                    name: "FK_PiezasStockExterno_ContenedoresDocumentos_IdContenedorDocumentosVehiculo",
                    column: x => x.IdContenedorDocumentosVehiculo,
                    principalSchema: "public",
                    principalTable: "ContenedoresDocumentos",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
            });

Ivan Stoev Ivan Stoev · Accepted Answer · 2018-01-19T19:02:18

You definitely are hitting the PostgreSQL Max Identifier Length of 63 bytes with the FK constraint names, which after truncation become one and the same, hence the confusing already exist error (although it can be seen that the name is truncated).

Since specifying FK constraint name currently is possible only with Fluent API, you need to override OnModelCreating and add the following code (use any names that make sense to you and don't exceed 63 characters):

modelBuilder.Entity<PiezaStockExterno>()
    .HasOne(e => e.ContenedorDocumentosPieza)
    .WithMany()
    .HasConstraintName("FK_PiezasStockExterno_IdContenedorDocumentosPieza");

modelBuilder.Entity<PiezaStockExterno>()
    .HasOne(e => e.ContenedorDocumentosVehiculo)
    .WithMany()
    .HasConstraintName("FK_PiezasStockExterno_IdContenedorDocumentosVehiculo");

And in case you add inverse collection navigation properties, don't forget to update the corresponding WithMany call.

Entity Framework Core - Failed migration with Npgsql provider

1 Answers