How to model lookup tables using EF DbContext

Question

I am using Entity Framwork DbContexts with an outdated database. I have two different properties of an object that should reference the same lookup table, for example:

public class Address { public virtual AddressType AnAddressType {get; set;} public virtual AddressType AnotherAddressType {get; set;} } // now here a LINQ query that just flat won't work: from a in Addresses select a; 

The exception indicates that we tried to include a completely fictitious field in the selection list - the field does not appear in my POCO or table - it looks like it was expected by convention, it was called AnAddressType_AddressType or something close to that

The AddressType object does not have a corresponding navigation property. I can't seem to get this to work. When I try to select data with my LINQ query, I get runtime errors.

Edit

I have other relationships that work (this code is generated from the DbContext "stock" generator). The fact is that this other relationship is different from the fact that the lookup table does not have the navigation property back to the main table (the lookup table is used everywhere, so I really don't want to add nav properties from it to everything that uses it). EF has a problem with this. This is probably a configuration and agreement option, and I inadvertently tripped over some kind of agreement problem.

Answer 1

You have a foreign key column name in your old database that does not comply with EF conventions, for example: the foreign key column in the Addresses table in the AddressTypes table for the AnAddressType relationship has the name MyCrazyAnAddressTypeNumberCodeKeyId .

But EF, by convention, suppose the column name is FK: [Nav.property]_[KeyColumn] . For example: If an AddressType has a PK named AddressTypeId EF, it is assumed that the FK column is named AnAddressType_AddressTypeId . Since this does not match you get the exception you are describing. You must provide the FK column name to resolve this issue:

 modelBuilder.Entity<Address>() .HasRequired(a => a.AnAddressType) .WithMany() .Map(c => c.MapKey("MyCrazyAnAddressTypeNumberCodeKeyId")) .WillCascadeOnDelete(false); 

(code snippet partially stolen from Ladislav's answer for convenience)

This is my hypothesis.

Edit

Alternatively, you can enter a foreign key property in your model and inform EF with data annotations that this property is FK for the corresponding navigation property:

 public class Address { [ForeignKey("AnAddressType")] public int MyCrazyAnAddressTypeNumberCodeKeyId {get; set;} public virtual AddressType AnAddressType {get; set;} } 

Answer 2

If you do not need navigation properties on either side of the relationship, you should help EF with a smooth display so that the model is correctly represented:

 public class YourContext : DbContext { public DbSet<Address> Addresses { get; set; } public DbSet<AddressType> AddresTypes { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<Address>() .HasRequired(a => a.AnAddressType) .WithMany() .WillCascadeOnDelete(false); modelBuilder.Entity<Address>() .HasRequired(a => a.AnotherAddressType) .WithMany() .WillCascadeOnDelete(false); } }