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

I may be wrong, but I believe you're missing some concepts here...

Why you have a JoinTable if it's doesn't have any column besides its foreign keys? It doesn't make sense... IHMO a nullable foreign key in TableA would be correct way.

When you work with Code-First it means that everything in your database will be represented by CODE. There's no reason to have a table in your database but not in your code...

EDMX handles that relationship because it uses "Associations" https://msdn.microsoft.com/en-us/data/jj713299#Overview

...backing to the code-first, you can represent your database like this:

 public class JoinTable
 {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int TableA_FK { get; set; }

        public int TableB_FK { get; set; }

        //a future property here

        public virtual TableA TableA { get; set; }

        public virtual TableB TableB { get; set; }
 }

 public partial class TableA
 {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int TableAId { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        public virtual JoinTable JoinTable { get; set; }
}


public partial class TableB
{
        public TableB()
        {
            JoinTable = new HashSet<JoinTable>();
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int TableBId { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        public virtual ICollection<JoinTable> JoinTable { get; set; }
    }
}

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

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

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<TableA>()
            .HasOptional(e => e.JoinTable)
            .WithRequired(e => e.TableA);

        modelBuilder.Entity<TableB>()
            .HasMany(e => e.JoinTable)
            .WithRequired(e => e.TableB)
            .HasForeignKey(e => e.TableB_FK)
            .WillCascadeOnDelete(false);
    }
}

Answer 2

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

}

Answer 3

If I've understood correctly, the following code using only data annotations should create your model.

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

    public JoinTable JoinTable { get; set; }
}

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

    public List<JoinTable> JoinTables{ get; set; }
}

public class JoinTable
{
    [Key, ForeignKey("TableA")]
    public int TableA_FK { get; set; }

    [ForeignKey("TableB")]
    public int TableB_FK { get; set; }

    public TableA TableA { get; set; }

    public TableB TableB { get; set; }
}     

Interestingly, EF does not perform a round trip back to the original, if you generate the code-first models from the database model that this code creates then EF simplifies the model and removes the join table and creates a nullable foreign key.

Let me know if this works.