Foreign Key Entity Framework & Razor

entity framework foreign key fluent api
entity framework retrieve data from table with foreign key
entity framework foreign key one-to-one
entity framework foreign key database first
entity framework foreign key code first one-to-many
ef core foreign key without navigation property
entity framework navigation properties
entity framework one-to zero or one

I am using Entity Framework 6, MVC 5 and the latest ASP.NET Razor.

I have the following code :

[ForeignKey("Country")]
[Display(Name = "CountryId", ResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
public int CountryId { get; set; }

[Required(ErrorMessageResourceName = "Country_ValidationError", ErrorMessageResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
[Display(Name = "Country", ResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
public virtual Country Country { get; set; }

[ForeignKey("Currency")]
[Display(Name = "CurrencyId", ResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
public int CurrencyId { get; set; }

[Required(ErrorMessageResourceName = "Currency_ValidationError", ErrorMessageResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
[Display(Name = "Currency", ResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
public virtual Currency Currency { get; set; }

Irrespective of where I place the foreign key, the tables are created correctly; but when I create the views:

<div class="form-group">
  @Html.LabelFor(model => model.CountryId, "CountryId", htmlAttributes: new { @class = "control-label col-md-2" })
  <div class="col-md-10">
    @Html.DropDownList("CountryId", null, htmlAttributes: new { @class = "form-control" })
    @Html.ValidationMessageFor(model => model.CountryId, "", new { @class = "text-danger" })
  </div>
</div>

<div class="form-group">
  @Html.LabelFor(model => model.CurrencyId, "CurrencyId", htmlAttributes: new { @class = "control-label col-md-2" })
  <div class="col-md-10">
    @Html.DropDownList("CurrencyId", null, htmlAttributes: new { @class = "form-control" })
    @Html.ValidationMessageFor(model => model.CurrencyId, "", new { @class = "text-danger" })
  </div>
</div>

I get the error :

The ViewData item that has the key 'CountryId' is of type 'System.Int32' but must be of type 'IEnumerable'.

What I am missing, surely this should work as I do not require IEnumerable for a one-to-one relationship?

Any assistance greatly appreciated.


The ViewData item that has the key 'CountryId' is of type 'System.Int32' but must be of type 'IEnumerable'.

    [ForeignKey("Currency")]
    [Display(Name = "CurrencyId", ResourceType = typeof(Internationalization.Resources.TenantFinanceConfiguration))]
    public int CurrencyId { get; set; }

Since you are defining int CurrencyID here, you will get int type. If you want Enumberable of int then you must declare something like

 public IEnumerable<int> CurrencyId { get; set; }

Code-First Conventions in Entity Framework 6, In Entity Framework, an entity can be related to other entities through an association or relationship. Each relationship contains two ends that  The ForeignKey attribute is used to configure a foreign key in the relationship between two entities in EF 6 and EF Core. It overrides the default conventions. As per the default convention, EF makes a property as foreign key property when its name matches with the primary key property of a related entity.


When you want a dropdown, common practice is to use another class for your View (something like a ViewModel). That class will also have all possible values for dropdown. (You can also use them in your Model class and mark property as [NotMapped])

public class YourViewModel : YourModel
{
    public IEnumerable<int> CurrencyIds { get; set; }
}

Then populate CurrencyIds in controller, and later use in your view:

@Html.DropDownListFor(model => model.CurrencyId, new SelectList(Model.CurrencyIds ))

Only this will display dropdown with IDs. User may actually be interested in currency name or another property. Therefore:

public class YourViewModel : YourModel
{
    public IEnumerable<Currency> Currencies { get; set; }
}

View:

@Html.DropDownListFor(x => x.CurrencyId, new SelectList(Model.Currencies, "CurrencyId", "DisplayName", Model.CurrencyId))

(notice, that I bound directly to @Model in SelectList)

Relationships, The ForeignKey attribute is used to specify which property is the foreign key in a relationship. In the following example, the AuthorFK property in the Book entity  A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. There are generally three types of relationships: one-to-one, one-to-many, and many-to-many.


Thank you all for your feedback. According to the Microsoft Documentation I need only specify a [ForeignKey] Attribute and virtual property. This is certainly how I have achieved this one-to-one relationship in the past with Entity Framework Code First. I have been reading a large number of articles on one-to-one relationship and even download sample code and they all work, it appears to be something in my solution but I cannot understand what. I downloaded a Contoso University example and they have specified there relationship as :

    public int DepartmentID { get; set; }

    public virtual Department Department { get; set; }

and there view has

<div class="form-group">
        <label class="control-label col-md-2" for="DepartmentID">Department</label>
        <div class="col-md-10">
            @Html.DropDownList("DepartmentID", null, htmlAttributes: new { @class =  "form-control" })
            @Html.ValidationMessageFor(model => model.DepartmentID, "", new { @class = "text-danger" })
        </div>
    </div>

And it all wires up correctly.

Relationships, navigation properties, and foreign keys, The required side of the 1..0 relationship MemberDataSet should not have a FK to DeferredData . Instead, DeferredData 's PK should also be a  Left as it is, Entity Framework Core will create an AuthorFK field and an AuthorId field which it will configure as a foreign key: The ForeignKey attribute can be used in the following ways to configure the relationship correctly, preventing the generation of an AuthorId column in the Books table.


This sort of thing can happen if you do not provide a ViewBag in your controller method or a List<SelectListItem> or SelectList in your model and/or if you do not specify it in your DropDownListFor, as it does not have there in your code. The error is telling you that you have this CountryId, which is an int, but it's expecting you to fill your dropdown list with a collection of items, not with an int. It will try to find a ViewBag with the name CountryId, as it will default to try to do when there is no collection specified in your View code, and it can't proceed if the collection is not there, because then it only sees that CountryId is referring to an integer value to which you are trying to set the dropdown's ID value.

The best way to avoid problems, I found, was to add:

    public SelectListItem[] CountriesList;
    public SelectListItem[] CurrenciesList;

to the ViewModel, then, in the controller, you add items accordingly:

public ActionResult Index()
{
    MyViewModel mvm = new MyViewModel();
    mvm = Initialize(mvm);
    return View(mvm);
}

public MyViewModel Initialize(MyViewModel mvm)
{
    if (_CurrenciesList == null)
        mvm.CurrenciesList = GetCurrencyList();
    else
        mvm.CurrenciesList = _CurrenciesList;

    if (_CountriesList == null)
        mvm.CountriesList = Get CountriesList();
    else
        mvm.CountriesList = _CountriesList;

    return mvm;
}

private static SelectListItem[] _CurrenciesList;
private static SelectListItem[] _CountriesList;

/// <summary>
/// Returns a static category list that is cached
/// </summary>
/// <returns></returns>
public SelectListItem[] GetCurrenciesList()
{
    if (_CurrenciesList == null)
    {
        var currencies = repository.GetAllCurrencies().Select(a => new SelectListItem()
        {
            Text = a.Name,
            Value = a.CurrencyId.ToString()
        }).ToList();
        currencies.Insert(0, new SelectListItem() { Value = "0", Text = "-- Please select your currency --" });

        _CurrenciesList = currencies.ToArray();
    }

    // Have to create new instances via projection
    // to avoid ModelBinding updates to affect this
    // globally
    return _CurrenciesList
        .Select(d => new SelectListItem()
    {
        Value = d.Value,
        Text = d.Text
    })
    .ToArray();
}

public SelectListItem[] GetCountriesList()
{
    if (_CountriesList == null)
    {
        var countries = repository.GetAllCountries().Select(a => new SelectListItem()
        {
            Text = a.Name,
            Value = a.CountryId.ToString()
        }).ToList();
        countries.Insert(0, new SelectListItem() { Value = "0", Text = "-- Please select your country --" });

        _CountriesList = countries.ToArray();
    }

    // Have to create new instances via projection
    // to avoid ModelBinding updates to affect this
    // globally
    return _CountriesList
        .Select(d => new SelectListItem()
    {
        Value = d.Value,
        Text = d.Text
    })
    .ToArray();
}

Note I use an "Initialize" function to fill the ViewModel, so that anytime the ViewModel needs to be filled with dropdown values, it can easily be done.

I have a separate "Repository" class, where I have functions for getting a List<T> of enumerables from the tables. In this case, it would look like this:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using YourSite.Models;
using YourSite.ViewModels;

namespace YourSite
{
    public class Repository
    {
        Model1 db = new Model1();  // this is your DB Context

        // Currencies
        public List<Currencies> GetAllCurrencies()
        {
            return db.Currencies.OrderBy(e => e.Name).ToList();
        }

        // Countries
        public List<Countries> GetAllCountries()
        {
            return db.Countries.OrderBy(e => e.Name).ToList();
        }
    }
}

It allows you to do any ordering, exclusion, manipulation - basically whatever you need to do on the table data before returning it for use.

In the model, to set up your Foreign Key, you should have it the opposite way than you do. The ForeignKey attribute maps the field you have in your current ViewModel to the primary key of the table you are accessing, i.e. the CountryId of your current ViewModel's table to the CountryId on the Country table, so the [ForeignKey("CountryId")] attribute goes over top the virtual instance of that Country table. The DisplayName attribute can be used in place of Display(Name="...", ...)]:

[DisplayName("Country")]
[Required(ErrorMessage = "Country is required")]
public int CountryId { get; set; }

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

[DisplayName("Currency")]
[Required(ErrorMessage = "Currency is required")]
public int CurrencyId { get; set; }

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

We use DisplayName because we want to, for example, have a LabelFor(model => model.CountryId) and have it show Country, not CountryId. We won't ever be displaying the virtual Countries table, so having a DisplayName over that isn't necessary. The validation errors should go above the Id fields, as well, and contain the actual errors to give (as shown above). As a side note: Model validation can be enacted with the following lines in a controller function if you didn't already have it:

ValidationContext vc = new ValidationContext(sta);
ICollection<ValidationResult> results = new List<ValidationResult>(); // results of the validation
bool isValid = Validator.TryValidateObject(mvm, vc, results, true); // Validates the object and its properties 

 int newId = 0;
 if (isValid)
 {
     // Save MVM
     newId = repository.SaveMVM(mvm); // function should save record and return ID
     return View("Edit", new { mvm.Id = newId });
  }
  else
      RedirectToAction("Edit", mvm);

On the view, the dropdown code would look like this:

<div class="form-group">
    @Html.LabelFor(model => model.CurrencyId, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        @Html.DropDownListFor(
            model => model.CurrencyId, // Specifies the selected CountryId
            //(IEnumerable<SelectListItem>)ViewData["CurrencyId"],
            Model.CurrenciesList, // supply the pre-created collection of SelectListItems
            htmlAttributes: new { @class = "form-control" }
        )
        @Html.ValidationMessageFor(model => model.CurrencyId, "", new { @class = "text-danger" })
     </div>
</div>

<div class="form-group">
    @Html.LabelFor(model => model.CountryId, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        @Html.DropDownListFor(
            model => model.CountryId, // Specifies the selected CountryId
            //(IEnumerable<SelectListItem>)ViewData["CountryId"],
            Model.CountriesList, // supply the pre-created collection of SelectListItems
            htmlAttributes: new { @class = "form-control" }
        )
        @Html.ValidationMessageFor(model => model.CountryId, "", new { @class = "text-danger" })
     </div>
</div>

If you were going to use the (IEnumerable)<SelectListItem>ViewData["CountryId"], for example, instead of using the Initialize() function, you use a ViewBag: adding ViewBag.CountryId = GetCountriesList(); in the Index() method before returning the View. You could also use ViewData["CountryId"] = GetCountriesList(); instead - these are interchangeable. I haven't had as great success getting values to bind to the DropDownListFor objects with either of those, though, so that's why I prefer using the model objects ( public SelectListItem[] CountriesList; public SelectListItem[] CurrenciesList; ), instead, but thought I would show the various ways of adding in the options to the dropdowns.

The ForeignKey Attribute, Learn Entity Framework foreign-key by example. Learn EF6 with The ForeignKey attribute is used to specify which property is the foreign key in a relationship. Entity framework one to zero or one relationship without navigation property. I had always thought that ForeignKeywas used to show which property in a class held the ForeignKey that determined the navigation property e.g. public class MemberDataSet{ [Key] [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public int?


Understanding ForeignKey attribute in entity framework code first , Entity framework Code First infers the Foreign Key by using the Navigational property. ForeignKey Attribute can be used to override the default  @MichaelMao Simply by convention. Name your child class foreign key string property to 'ApplicationUserId' this will be the foreign key by convention. EntityName + Id. Same is valid for primary key. 'Id' will always be the primarykey. – Pascal Aug 26 '16 at 19:12


Foreign Key, If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted  Entity Framework Model-First Nullable Foreign Key 0 Entity Framework Code First: The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical


ForeignKey Attribute in EF & EF Core, When a navigation property exist on a model, Entity Framework will automatically create a Foreign Key column. If a specific Foreign Key name is desired but is  Foreign key: The properties in the dependent entity that are used to store the principal key values for the related entity. Navigation property: A property defined on the principal and/or dependent entity that references the related entity.