Self-referencing table in EF6

Question

I thought this was going to be easy... I have a situation where I have a table Module, which can contain "base" modules, and "compound" modules (that are made up from 1-n base modules).

So I have these two tables in SQL Server 2014:

CREATE TABLE Module
(
    ModuleId INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Module PRIMARY KEY CLUSTERED,
    ModuleName VARCHAR(100)
)

CREATE TABLE CompoundModule
(
    CompoundModuleId INT NOT NULL
        CONSTRAINT FK_CompoundModule_MainModule
        FOREIGN KEY REFERENCES dbo.Module(ModuleId),
    BaseModuleId INT NOT NULL
        CONSTRAINT FK_CompoundModule_BaseModules
        FOREIGN KEY REFERENCES dbo.Module(ModuleId),

    CONSTRAINT PK_CompoundModule
        PRIMARY KEY CLUSTERED(CompoundModuleId, BaseModuleId)
)

and I filled in a few base modules:

INSERT INTO dbo.Module (ModuleName)
VALUES ('Base Module #1'), ('Base Module #2'), ('Base Module #3')

Now I created an EF 6 "code-first, reverse-engineer from database" model and get this Module class:

[Table("Module")]
public partial class Module
{
    public Module()
    {
        Module1 = new HashSet<Module>();
        Module2 = new HashSet<Module>();
    }

    public int ModuleId { get; set; }
    public string ModuleName { get; set; }

    public virtual ICollection<Module> Module1 { get; set; }
    public virtual ICollection<Module> Module2 { get; set; }
}

and this context class:

public partial class ModuleCtx : DbContext
{
    public ModuleCtx() : base("name=ModuleCtx")
    { }

    public virtual DbSet<Module> Module { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Module>()
            .Property(e => e.ModuleName)
            .IsUnicode(false);

        modelBuilder.Entity<Module>()
            .HasMany(e => e.Module1)
            .WithMany(e => e.Module2)
            .Map(m => m.ToTable("CompoundModule").MapLeftKey("BaseModuleId").MapRightKey("CompoundModuleId"));
    }
}

When I'm now trying to create a new compound module with this code, it turns out things aren't quite as easy as I thought.....

using (ModuleCtx ctx = new ModuleCtx())
{
    Module newCompound = new Module();

    Module baseModule1 = ctx.Module.FirstOrDefault(m => m.ModuleId == 1);
    Module baseModule3 = ctx.Module.FirstOrDefault(m => m.ModuleId == 3);

    newCompound.BaseModules.Add(baseModule1);
    newCompound.BaseModules.Add(baseModule3);

    ctx.Module.Add(newCompound);
    ctx.SaveChanges();
}

This code causes an error (on the line trying to fetch the base module #1):

System.Data.Entity.Core.EntityCommandExecutionException was unhandled
HResult=-2146232004
Message=An error occurred while executing the command definition. See the inner exception for details.
Source=EntityFramework

InnerException: System.Data.SqlClient.SqlException
HResult=-2146232060
Message=Invalid column name 'Module_ModuleId'.

What am I missing here?? And why isn't the EF6 reverse-engineering code smart enough to create a model that works in this case??

I've been using EF4 with database-first approach so far, so all this fluent code-first configuration is still a bit of a mystery (and problem) to me...... does anyone see my (most likely very) obvious rookie mistake??

PS: this is the code that the "Code-first from existing database" reverse-engineering produces - not my own. So why does the reverse engineering output code that doesn't work in the end??

Answer 1

Try my generator EntityFramework Reverse POCO Generator and see if that does a better job for you.

It generated the following code (interesting stuff at the bottom):

public interface IMyDbContext : System.IDisposable
{
    System.Data.Entity.DbSet<Module> Modules { get; set; } // Module

    int SaveChanges();
    System.Threading.Tasks.Task<int> SaveChangesAsync();
    System.Threading.Tasks.Task<int> SaveChangesAsync(System.Threading.CancellationToken cancellationToken);
}

public class MyDbContext : System.Data.Entity.DbContext, IMyDbContext
{
    public System.Data.Entity.DbSet<Module> Modules { get; set; } // Module

    static MyDbContext()
    {
        System.Data.Entity.Database.SetInitializer<MyDbContext>(null);
    }

    public MyDbContext()
        : base("Name=MyDbContext")
    {
    }

    public MyDbContext(string connectionString)
        : base(connectionString)
    {
    }

    public MyDbContext(string connectionString, System.Data.Entity.Infrastructure.DbCompiledModel model)
        : base(connectionString, model)
    {
    }

    public MyDbContext(System.Data.Common.DbConnection existingConnection, bool contextOwnsConnection)
        : base(existingConnection, contextOwnsConnection)
    {
    }

    public MyDbContext(System.Data.Common.DbConnection existingConnection, System.Data.Entity.Infrastructure.DbCompiledModel model, bool contextOwnsConnection)
        : base(existingConnection, model, contextOwnsConnection)
    {
    }

    protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
    }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Configurations.Add(new ModuleConfiguration());
    }

    public static System.Data.Entity.DbModelBuilder CreateModel(System.Data.Entity.DbModelBuilder modelBuilder, string schema)
    {
        modelBuilder.Configurations.Add(new ModuleConfiguration(schema));
        return modelBuilder;
    }
}

public class Module
{
    public int ModuleId { get; set; } // ModuleId (Primary key)
    public string ModuleName { get; set; } // ModuleName (length: 100)

    // Reverse navigation
    public virtual System.Collections.Generic.ICollection<Module> BaseModule { get; set; } // Many to many mapping
    public virtual System.Collections.Generic.ICollection<Module> CompoundModule { get; set; } // Many to many mapping

    public Module()
    {
        BaseModule = new System.Collections.Generic.List<Module>();
        CompoundModule = new System.Collections.Generic.List<Module>();
    }
}

// Module
public class ModuleConfiguration : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Module>
{
    public ModuleConfiguration()
        : this("dbo")
    {
    }

    public ModuleConfiguration(string schema)
    {
        ToTable("Module", schema);
        HasKey(x => x.ModuleId);

        Property(x => x.ModuleId).HasColumnName(@"ModuleId").IsRequired().HasColumnType("int").HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
        Property(x => x.ModuleName).HasColumnName(@"ModuleName").IsOptional().IsUnicode(false).HasColumnType("varchar").HasMaxLength(100);
        HasMany(t => t.CompoundModule).WithMany(t => t.BaseModule).Map(m =>
        {
            m.ToTable("CompoundModule", "dbo");
            m.MapLeftKey("BaseModuleId");
            m.MapRightKey("CompoundModuleId");
        });
    }
}