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.

Lithoid

Is LevelId the PK? If so try adding the key annotation above the property like: [Key] public int LevelId {get;set;} – Absolution

Is this all code or did you leave out other properties for brevity? – Orchid

@dickrichie just tried adding the [Key] attribute, same thing happens. – Lithoid

@GertArnold I left out other stuff for brevity. I have some more string and DateTime properties, nothing complicated. – Lithoid

Possible duplicate of Map category parent id self referencing table structure to EF Core entity – Speiss

Answer 1

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");
        }
    }
}

Carper

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. – Lithoid

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. – Nephritic

@DavideDeSantis Added a Migration. Still no repro. – Carper

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! – Lithoid

@DavidBrowne-Microsoft and how can you retrieve all data in a hierarchical form? – Improvisator

The If you retrieve all the entities, EF will fix-up the relationships. – Carper

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 – Hypothermal

Post a repro in a new question. – Carper