EF Core 2.1: Self-referencing entity with one to many relationship generates additional column

Question

I have the following entity:

public class Level
{
    public int LevelId { get; set; }
    public int? ParentLevelId { get; set; }
    public string Name { get; set; }

    public virtual Level Parent { get; set; }
    public virtual HashSet<Level> Children { get; set; }   
}

What I am having trouble here, is the Children property, which is configured like this in Fluent API:

modelBuilder.Entity<Level>()
    .HasOne(x => x.Parent)
    .WithMany(x => x.Children)
    .HasForeignKey(x => x.ParentLevelId);

This results in some additional column being added by the migration:

migrationBuilder.AddColumn<int>(
    name: "LevelId1",
    table: "Level",
    nullable: true);

migrationBuilder.CreateIndex(
    name: "IX_Level_LevelId1",
    table: "Level",
    column: "LevelId1");

migrationBuilder.AddForeignKey(
    name: "FK_Level_Level_LevelId1",
    table: "Level",
    column: "LevelId1",
    principalTable: "Level",
    principalColumn: "LevelId",
    onDelete: ReferentialAction.Restrict);

What am I doing wrong here?

Edit: Question was marked as a possible duplicate of this question; however, in that case, the model generation works - the issue is loading the data. Whereas here, the issue is that an additional column is generated.

Is LevelId the PK? If so try adding the key annotation above the property like: [Key] public int LevelId {get;set;} — , Jul 12 '18 at 14:35
Is this all code or did you leave out other properties for brevity? — Gert Arnold, Jul 12 '18 at 14:35
@dickrichie just tried adding the [Key] attribute, same thing happens. — Davide De Santis, Jul 12 '18 at 14:40
@GertArnold I left out other stuff for brevity. I have some more string and DateTime properties, nothing complicated. — Davide De Santis, Jul 12 '18 at 14:41
Possible duplicate of [Map category parent id self referencing table structure to EF Core entity](https://stackoverflow.com/questions/46160780/map-category-parent-id-self-referencing-table-structure-to-ef-core-entity) — phuzi, Jul 12 '18 at 14:43

David Browne - Microsoft · Accepted Answer · 2018-07-12T15:38:55.710

Something's messed up in your migration. No repro when initializing that model:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace EfCoreTest
{

    public class Level
    {
        public int LevelId { get; set; }
        public int? ParentLevelId { get; set; }
        public string Name { get; set; }

        public virtual Level Parent { get; set; }
        public virtual HashSet<Level> Children { get; set; }
    }

    public class Db : DbContext
    {
        public DbSet<Level> levels { get; set; }


        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("server=.;database=EfCoreTest;Integrated Security=true");
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Level>()
                        .HasOne(x => x.Parent)
                        .WithMany(x => x.Children)
                        .HasForeignKey(x => x.ParentLevelId);

        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Db())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                Console.ReadKey();



            }

        }
    }
}

creates table:

CREATE TABLE [levels] (
    [LevelId] int NOT NULL IDENTITY,
    [ParentLevelId] int NULL,
    [Name] nvarchar(max) NULL,
    CONSTRAINT [PK_levels] PRIMARY KEY ([LevelId]),
    CONSTRAINT [FK_levels_levels_ParentLevelId] FOREIGN KEY ([ParentLevelId]) REFERENCES [levels] ([LevelId]) ON DELETE NO ACTION
);

Added a Migration,

PM> Add-Migration InitialCreate

and still no repro:

using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;

namespace EfCoreTest.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "levels",
                columns: table => new
                {
                    LevelId = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    ParentLevelId = table.Column<int>(nullable: true),
                    Name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_levels", x => x.LevelId);
                    table.ForeignKey(
                        name: "FK_levels_levels_ParentLevelId",
                        column: x => x.ParentLevelId,
                        principalTable: "levels",
                        principalColumn: "LevelId",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_levels_ParentLevelId",
                table: "levels",
                column: "ParentLevelId");
        }

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

I just dropped the db, deleted all migrations and created a new initial one. Same issue, it creates a LevelId property (key), ParentLevelId and that additional LevelId1. — Davide De Santis, Jul 12 '18 at 15:13
Try being explicit and decorating your `Parent` property with `[ForeignKey("ParentLevelId")]` to tell Entity specifically that you want to use that column as the FK for that Navigation Property. — Valuator, Jul 12 '18 at 15:21
I just retried after the weekend, and all of a sudden it works. I didn't change anything, did the same thing as last week (e.g. dropping the db, deleting all migrations, recreating an initial migration). What didn't work multiple times last week now works perfectly, no idea why. Thanks anyway for your help! — Davide De Santis, Jul 16 '18 at 06:23
@DavidBrowne-Microsoft and how can you retrieve all data in a hierarchical form? — pantonis, Sep 27 '19 at 11:56
The If you retrieve all the entities, EF will fix-up the relationships. — David Browne - Microsoft, Sep 27 '19 at 13:28
Hi @DavidBrowne-Microsoft, I have a similar configuration and it was, but my problem is that I can't insert an entity without setting the parentId `SqlException: The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint`. This is the error I am currently getting. Any suggestions will be welcomed — Cizaphil, Nov 24 '19 at 15:26

EF Core 2.1: Self-referencing entity with one to many relationship generates additional column

1 Answers1

Linked