Why is EF Code First generating a column for my navigation property?

Question

I am trying to figure out how to configure EF Code First, to generate a table. The problem I am running into is I am not sure how to correctly configure the table. The table has a parent-child relationship to itself. I would prefer a solution that uses FluentAPI configuration rather than attributes.

EF is generating the following table:

Specification
-------------
SpecificationId
Name
ParentSpecificationId
ParentSpecification_SpecificationId

Here is the class:

public class Specification
{
    public Specification()
    {
        Children = new Collection<Specification>();
    }

    public int SpecificationId { get; set; }
    public string Name { get; set; }
    public int? ParentSpecificationId { get; set;}
    public virtual Specification ParentSpecification { get; set;}
    public virtual ICollection<Specification> Children { get; set;}
}

I tried the configuration below based on this question

public class SpecificationConfiguration : EntityTypeConfiguration<Specification>
{
    public SpecificationConfiguration()
    {
        ToTable("Specification");
        HasKey(k => k.SpecificationId);
        Property(p => p.SpecificationId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        HasRequired(t => t.ParentSpecification)
        .WithMany(t => t.Children)
        .HasForeignKey(t => t.ParentSpecificationId)
        .WillCascadeOnDelete(false);

        HasOptional(t => t.Children)
        .WithMany()
        .HasForeignKey(t => t.SpecificationId)
        .WillCascadeOnDelete(false);
    }
}

What is wrong in my configuration and/or how do I get EF to stop generating the ParentSpecification_SpecificationId and put the value in the ParentSpecificationId?

Note: EF is putting the correct value in the column it is generating.

Answer 1

Like this (EF 6.1.3):

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{



    public class Specification
    {
        public int SpecificationId { get; set; }

        public string Name { get; set; }

        public int? ParentSpecificationId { get; set; }

        public virtual Specification ParentSpecification { get; set; }

        public virtual ICollection<Specification> Children { get; } = new HashSet<Specification>();
    }

    public class Db : DbContext
    {
        public DbSet<Specification> Specifications { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Specification>()
                        .HasMany(s => s.Children)
                        .WithOptional(s => s.ParentSpecification)
                        .HasForeignKey(s => s.ParentSpecificationId)
                        .WillCascadeOnDelete(false);

            base.OnModelCreating(modelBuilder);
        }
    }


    class Program
    {
        static void Main(string[] args)
        {


            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);

                db.Database.Initialize(false);


                Console.ReadKey();
            }
        }
    }
}

outputs

    CREATE TABLE [dbo].[Specifications] (
        [SpecificationId] [int] NOT NULL IDENTITY,
        [Name] [nvarchar](max),
        [ParentSpecificationId] [int],
        CONSTRAINT [PK_dbo.Specifications] PRIMARY KEY ([SpecificationId])
    )


    -- Executing at 6/30/2017 12:54:33 PM -05:00

    -- Completed in 24 ms with result: -1



    CREATE INDEX [IX_ParentSpecificationId] ON [dbo].[Specifications]([ParentSpecificationId])


    -- Executing at 6/30/2017 12:54:33 PM -05:00

    -- Completed in 8 ms with result: -1



    ALTER TABLE [dbo].[Specifications] 
    ADD CONSTRAINT [FK_dbo.Specifications_dbo.Specifications_ParentSpecificationId] 
    FOREIGN KEY ([ParentSpecificationId]) 
    REFERENCES [dbo].[Specifications] ([SpecificationId])