EF code first from database 0..1 to many relationship

Question

I am trying to generated an entity framework code first model from an existing database (without changing the database schema). This database has been used in the past to generate edmx models and I am trying to achieve the equivalent model using Fluent Api or data annotations.

The relationship I have been unable to reproduce is 0..1 to many using a join table (not a nullable foreign key).

So it would look something like this:

TableA
{
   ID (PrimaryKey)
   TableB (0 or 1)
}

JoinTable
{
   TableA_FK (PrimaryKey, ForeignKey),
   TableB_FK (ForeignKey)
}

TableB
{
   ID (PrimaryKey)
   TableAs (Many)
}

Is this achievable in the code first style or will I have to generate an edmx model in order to use this database in EF without changing its schema?

Many thanks, Phil

Answer 1

Here is an example without using a JoinTable class. The join table is configured through the fluent api.

class DataContext : DbContext
    {
        public DataContext(string connectionString)
            : base(connectionString)
        { }

        public DbSet<TableA> TableA { get; set; }
        public DbSet<TableB> TableB { get; set; }

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

            modelBuilder.Entity<TableA>().ToTable("TableA");
            modelBuilder.Entity<TableB>().ToTable("TableB");

            modelBuilder.Entity<TableB>()
                .HasMany(x => x.TableAs)
                .WithMany()
                .Map(m =>
                {
                    m.ToTable("JoinTable");
                    m.MapLeftKey("TableA_FK");
                    m.MapRightKey("TableB_FK");
                });
        }
    }

    class TableA
    {
        public int ID { get; set; }
        public TableB TableB { get; set; }
    }

    class TableB
    {
        public int ID { get; set; }
        public ICollection<TableA> TableAs { get; set; }
    }

This will generate the following migration script, which looks like the schema you have.

public override void Up()
{
    CreateTable(
        "dbo.TableA",
        c => new
            {
                ID = c.Int(nullable: false, identity: true),
                TableB_ID = c.Int(),
            })
        .PrimaryKey(t => t.ID)
        .ForeignKey("dbo.TableB", t => t.TableB_ID)
        .Index(t => t.TableB_ID);

    CreateTable(
        "dbo.TableB",
        c => new
            {
                ID = c.Int(nullable: false, identity: true),
            })
        .PrimaryKey(t => t.ID);

    CreateTable(
        "dbo.JoinTable",
        c => new
            {
                TableA_FK = c.Int(nullable: false),
                TableB_FK = c.Int(nullable: false),
            })
        .PrimaryKey(t => new { t.TableA_FK, t.TableB_FK })
        .ForeignKey("dbo.TableB", t => t.TableA_FK, cascadeDelete: true)
        .ForeignKey("dbo.TableA", t => t.TableB_FK, cascadeDelete: true)
        .Index(t => t.TableA_FK)
        .Index(t => t.TableB_FK);

}