Efcore: Multiple Foreign Keys and Indices added for inherited items

Created on 10 Aug 2018 · 11Comments · Source: dotnet/efcore

I'm running into an issue where EF Core generates incorrect migrations. Consider the following code:

```C#
public class BasePage
{
[Key]
public Guid Id { get; protected set; }
}

public class ContentBlock
{
[Key]
public Guid Id { get; protected set; }
public string Title { get; set; }
}

// Derived pages:

public class ContentPage : BasePage
{
public ContentBlock ContentBlock { get; set; }

[Column(nameof(ContentBlockId))]
public Guid? ContentBlockId { get; set; }

}

public class HomePage : BasePage
{
public ContentBlock ContentBlock { get; set; }

[Column(nameof(ContentBlockId))]
public Guid? ContentBlockId { get; set; }

}

public class PageWithoutContentBlock : BasePage
{
public string Header { get; set; }
}

// Context file:

public class DatabaseContext : DbContext
{
public DatabaseContext(DbContextOptions options)
: base(options)
{ }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<HomePage>().HasBaseType<BasePage>();
    modelBuilder.Entity<ContentPage>().HasBaseType<BasePage>();
    modelBuilder.Entity<PageWithoutContentBlock>().HasBaseType<BasePage>();
}

public DbSet<BasePage> Pages { get; set; }

public DbSet<ContentBlock> ContentBlocks { get; set; }

}


This creates the following migration:

```C#
 public partial class initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "ContentBlocks",
                columns: table => new
                {
                    Id = table.Column<Guid>(nullable: false),
                    Title = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_ContentBlocks", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "Pages",
                columns: table => new
                {
                    Id = table.Column<Guid>(nullable: false),
                    Discriminator = table.Column<string>(nullable: false),
                    ContentBlockId = table.Column<Guid>(nullable: true),
                    Header = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Pages", x => x.Id);
                    table.ForeignKey(
                        name: "FK_Pages_ContentBlocks_ContentBlockId",
                        column: x => x.ContentBlockId,
                        principalTable: "ContentBlocks",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_Pages_ContentBlocks_ContentBlockId1",
                        column: x => x.ContentBlockId,
                        principalTable: "ContentBlocks",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_Pages_ContentBlockId",
                table: "Pages",
                column: "ContentBlockId");

            migrationBuilder.CreateIndex(
                name: "IX_Pages_ContentBlockId1",
                table: "Pages",
                column: "ContentBlockId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Pages");

            migrationBuilder.DropTable(
                name: "ContentBlocks");
        }
    }

This feels incorrect to me. It has the same foreign key and index added twice. Worse, if I have more items that inherit from BasePage (which I have in a real world example) I get the same number of indices and foreign keys added as the number of inherited pages (in my case 6).

I also tried explicitly adding the following line to the ContentBlock entity:

public List<BasePage> Pages { get; set; }

But funny enough this only adds another FK and index. I tried setting the FK explicitly using the Fluent API, but it seemed I failed, as it is expecting for example a List<HomePage> instead of a List<BasePage> in the WithMany method.

Am I missing something or is this a bug? I think that this issue appeared after upgrading to EF Core 2.1.1 but I am not 100% sure. I'm quite sure it worked correctly in 1.0 at least.

Further technical details

EF Core version: 2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.4

area-migrations area-model-building closed-fixed customer-reported type-bug

Source

razzemans

All 11 comments

This is a duplicate of #12702, but leaving open because it seems we could create only one constraint for this scenario, since the constraints are identical.

@razzemans The way to stop two constraints being created is to explicitly specify the constraint name to use:
```C#
modelBuilder.Entity()
.HasOne(e => e.ContentBlock)
.WithMany()
.HasConstraintName("FK_Pages_ContentBlocks_ContentBlockId");

modelBuilder.Entity()
.HasOne(e => e.ContentBlock)
.WithMany()
.HasConstraintName("FK_Pages_ContentBlocks_ContentBlockId");
```

ajcvickers on 11 Aug 2018

That indeed works. Do I understand correctly that this is something you consider fixing in EF itself in a future release?
The reason I ask is that this fix is a bit troublesome for us. We have created a CMS which allows users to create their own pages (in code) but must inherit from a base page. The situation outlined in my post is something that can easily happen. We don't want to burden users with having to specify FK constraint names themselves, so it would be great if this would work out of the box.

For now, we can continue with the above solution, thank you.

razzemans on 13 Aug 2018

@razzemans Are you seeing a functional issue with creating two constraints? As in, does it cause an exception/crash/incorrect behavior somewhere?

ajcvickers on 13 Aug 2018

@ajcvickers To be fair I haven't tested it, but I figured mutiple indices would definitely have some performance impacts for non-read operations? If that is not the case I'd gladly hear it!
Beside that, there is the esthetic argument which might not be as important but well, after running the latest migration we ended up having 7 duplicate FK's and indices. Looking at those tables it brings tears to my eyes, and not in a good way ;-)

If it is relatively easy to fix in EF Core I'd be happy if it gets implemented. It's not a showstopper as there are ways around it.

razzemans on 13 Aug 2018

@ajcvickers It does crash in my use case. I have a hierarchy with lots of children with many foreign keys each and for each FK EF Core adds an index. It creates more than 999 indexes, causing sql server exception.

Would appreciate a fix for this.