Entity framework mapping when foreign key column name is different

Question

I have a legacy table like this:

Country
- countryCode (PK, varchar(10), not null)

Now I have a new table:

Store
- store_id
- country_code

My models:

public class Country
{
   [Key]
   [Column("countryCode")
   public int CountryCode {get;set;}
}


public class Store
{
   [Key]
   [Column("store_id")
   public int Id {get;set;}

   [Column("country_code")]
   public int CountryCode {get;set;}
}

Now I want to be able to do this:

var store = // get store

store.Country.CountryCode

How can I create this mapping? Notice that the column names are not the same (I can't change this).

I believe I have to add this to my Store model, but how do I specificy the foreign key's seeing as they have different names?

public virtual CountryCode {get;set;}

Answer 1

If your database column has a type of varchar(10) you cannot use an int property in your model but you must use a string, no matter if the property name matches the column name or not. Also in order to be able to access the Country from the Store you need a navigation property Store.Country:

public class Country
{
    [Key]
    [Column("countryCode", TypeName = "varchar")]
    [MaxLength(10)]
    public string CountryCode { get; set; }
}

public class Store
{
    [Key]
    [Column("store_id")
    public int Id { get; set; }

    [Column("country_code", TypeName = "varchar")]
    [MaxLength(10)]
    [Required]
    [ForeignKey("Country")]
    public string CountryCode { get; set; }

    public virtual Country Country { get; set; }
}

(It's possible that the ForeignKey attribute is not necessary. You can try it without it. Also remove the [Required] attribute if the country_code column in table Store allows NULL values.)

You should be able now to access the CountryCode with for example:

var store = context.Stores.Find(1);
string countryCode = store.Country.CountryCode;

The store.Country navigation property will be loaded automatically via lazy loading (hence the virtual modifier) when you access the property.