EF6 object relationships

Question

I have a database, which I cannot modify because it is from an external application. The database contains tables that are linked, but not via the key - foreign key structure. Simplified, the structure looks like this:

Table - Warehouse
ID (Key)
WarehouseCode
Description

Table - Location
ID (Key)
LocationCode
Description
WarehouseCode

As you can see, the two tables can be linked via the WarehouseCode, but I don't understand how I can do that in EF6.

Answer 1

Edit: Vol 2. It seems that you will have to wait until EF7

• Support for specifying a foreign key associations that on the principal end specify columns(s) that comprise a unique constraint but are not the primary key,

It Seems there no way entity framework can relate ONLY one warehouse to a Location since there is no guarantee that there will be only one warehouse with that Warehouse Code.

References 1 2

So a workaround might be to establish a many to many relationship using an extra table only for this.

public class Warehouse
{
    public Int32 Id { get; set; }
    public String WarehouseCode { get; set; }
    public String Description { get; set; }
    [InverseProperty("LocationWarehouses")]
    public virtual ICollection<Location> LocationsWithThisWarehouse{ get; set; }
}
public class Location
{
    public Int32 Id { get; set; }
    public String LocationCode { get; set; }
    public String Description { get; set; }
    public String WarehouseCode { get; set; }
    [InverseProperty("LocationsWithThisWarehouse")]
    public virtual Icollection<Warehouse> LocationWarehouses { get; set; }
}

public class YourContext: DbContext
{
    public DbSet<Location> Locations { get; set; }
    public DbSet<Warehouse> Warehouses { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer < YourContext > (null);
        modelBuilder.Entity<Warehouse>().ToTable("Warehouse", "SchemaName");
        modelBuilder.Entity<Location>().ToTable("Location", "SchemaName");
        modelBuilder.Entity<Warehouse>().HasMany(g => g.LocationsWithThisWarehouse).WithMany(t => t.LocationWarehouses).Map(m =>
        {
            m.MapLeftKey("WarehouseCodeOnWarehouse");
            m.MapRightKey("WarehouseCodeOnLocation");
            m.ToTable("WarehouseAtLocation");
        });
    }
}