Entity Framework Foreign Key attribute usage problems

ef-code-first ef-migrations entity-framework-6

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?

1
0
10/7/2014 5:06:23 PM

Popular Answer

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.

1
10/7/2014 5:37:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow