Entity Framework Foreign Key attribute usage problems

Question

I am writing an ASP.NET MVC 5 application with Entity Framework 6 Code First and am having a bit of a problem with foreign keys. To exemplify the problem I show two tables, Country and Currency.

The Country model represents a table with countries and their attributes:

[Table("dbo.Country")]
public class Country {
    [Key]
    [StringLength(2)]
    [Display(Name = "L_Country_ISO2", ResourceType = typeof(ResxGlobal))]
    public string iso { get; set; }

    [Required]
    [StringLength(80)]
    [Display(Name = "L_Country", ResourceType = typeof(ResxGlobal))]
    public string name { get; set; }

    [Required]
    [StringLength(80)]
    public string nicename { get; set; }

    [StringLength(3)]
    [Display(Name = "L_Country_ISO3", ResourceType = typeof(ResxGlobal))]
    public string iso3 { get; set; }

    public short? numcode { get; set; }

    [Display(Name = "L_DialCode", ResourceType = typeof(ResxGlobal))]
    public int phonecode { get; set; }
}

And I have a separate table named Currency that lists the country's currency attributes with its PK being a two-letter ISO country code which is at the same time an FK to the Country table:

[Table("dbo.Currency")]
public class Currency {
    [Key]
    [StringLength(2)]
    [Display(Name = "L_Country_ISO2", ResourceType = typeof(ResxGlobal))]
    public string CountryCode { get; set; }

    [Required, StringLength(100)]
    [Display(Name="Currency name")]
    public string CurrencyName { get; set; }

    [Required, StringLength(3)]
    [Display(Name="Currency code")]
    public string CurrencyCode { get; set; }

    [StringLength(5)]
    public string Symbol { get; set; }

    //[ForeignKey("CountryCode")]
    public virtual Country Country {get; set; }
}

So far so good so now look at the 2nd model (Currency). IF I enable the ForeignKey attribute specifying that the CountryCode PK is also a FK to be used by the navigation property Country and do the migration, then the SQL Server diagram shows a relationship between Country and Currencya with a PK (they small key) terminator at both ends of the relationship, whereas normally the small key icon is shown on the PK table and the small infinity icon shown at the FK end of the relationship. That I found weird.

I reverted the migration, removed (commented out) the Foreign Key attribute and updated the database again. This time the relationship showed as I expected PK with key and other end with infinity icon. However, the table showed an extra column that I did not specify in my model. I manually edited the migration to omit the extra column named "Country_iso" I had not specified in my model.

Having removed (from the migration code) the unwanted Country_iso column prior to Update-Database I find that when I attempt to use the database context to retrieve the currency I get an "Invalid column name Country_iso" error. Where on earth is it insisting on getting a column I don't have? I don't see a reference to that anywhere, is it hidden somewhere in some metadata file?

So what would be the right or proper way to have a FK relationship (one to one and one to many) in a model?

Answer 1

Putting a Foreign Key constraint on your primary key doesn't seem like a good idea.

Consider the scenario where you delete a Country record, the database cannot enforce the FK constraint in the Currency table because it cannot NULL the FK field (It's also the primary Key). Your Currency record will continue to reference something that doesn't exist.

When you didn't provide a Foreign Key and one couldn't be resolved from the name of your properties EF generated the column Country_iso for you.

You have a CurrencyCode property in your Currency table you could use as the primary key.

[Table("dbo.Currency")] 
public class Currency {

    [StringLength(2)]
    [Display(Name = "L_Country_ISO2", ResourceType = typeof(ResxGlobal))]
    public string CountryCode { get; set; }

    [Required, StringLength(100)]
    [Display(Name="Currency name")]
    public string CurrencyName { get; set; }

    [Key]
    [Required, StringLength(3)]
    [Display(Name="Currency code")]
    public string CurrencyCode { get; set; }

    [StringLength(5)]
    public string Symbol { get; set; }

    [ForeignKey("CountryCode")]
    public virtual Country Country {get; set; } 
}

This will work for each Country only having one Currency and vice versa (1:1). If you want to support multiple countries with the same currency (M:1) you want each country to reference a Currency record instead.

[Table("dbo.Country")]
public class Country{
    [Key]
    [StringLength(2)]
    [Display(Name = "L_Country_ISO2", ResourceType = typeof(ResxGlobal))]
    public string iso { get; set; }

    [Required]
    [StringLength(80)]
    [Display(Name = "L_Country", ResourceType = typeof(ResxGlobal))]
    public string name { get; set; }

    [Required]
    [StringLength(80)]
    public string nicename { get; set; }

    [StringLength(3)]
    [Display(Name = "L_Country_ISO3", ResourceType = typeof(ResxGlobal))]
    public string iso3 { get; set; }

    public short? numcode { get; set; }

    [Display(Name = "L_DialCode", ResourceType = typeof(ResxGlobal))]
    public int phonecode { get; set; }

    public string CurrencyCode { get; set; }

    [ForeignKey("CurrencyCode")]
    public virtual Currency Currency { get; set; }
}

[Table("dbo.Currency")]
public class Currency{
    [Key]
    [Required, StringLength(3)]
    [Display(Name = "Currency code")]
    public string CurrencyCode { get; set; }

    [Required, StringLength(100)]
    [Display(Name = "Currency name")]
    public string CurrencyName { get; set; }

    [StringLength(5)]
    public string Symbol { get; set; }

    public virtual ICollection<Country> Countries { get; set; }
}

Answer 2

Some weeks ago I found conflicting information regarding the use of the ForeignKey attribute so I stuck with the one that seemed most logical.

However, I did another experiment and then moved the ForeignKey attribute to the CountryCode property of the Currency table/model as follows:

 [Key]
 [ForeignKey("Country")]
 [StringLength(2)]
 [Display(Name = "L_Country_ISO2", ResourceType = typeof(ResxGlobal))]
 public string CountryCode { get; set; }
        :
 public virtual Country Country {get; set; }

so it is now on the PK field (that is a FK as well) and points to the corresponding navigation property (Country).

As a result the migration "script" does not introduce any phantom column (the former Country_iso column) and retrieval of the information does not produce any errors either.