How to bind dropdownlist in mvc from database using entity framework

This guide presents a couple of common ways to populate dropdown lists in ASP.NET MVC Razor views, with an emphasis on producing functional HTML forms with a minimum amount of code. It is intended to help developers who are working to improve their proficiency with some key technologies.

Also shown is how the contents of one dropdown list can be updated dynamically based on the selection made in another dropdown list.

It will be helpful to have an understanding of the following topics, but an extensive working knowledge isn't required.

This guide focuses on ASP.NET MVC following the model-view view-model (MVVM) design pattern. The solution includes a small amount of JavaScript utilizing the jQuery library, in addition to the required C# code. Entity Framework is used to handle the interface between the SQL Server database and the data entities in the model.

1. We'll start with the essential code for implementing a dropdown list populated when the view model is created.

2. Building on that functionality, we'll look at how to populate the State/Province/Region dropdown based on the country selected.

3. Next, we'll look at the entities underlying the case study and how the pieces fit together.

4. Finally, we'll touch on some considerations for building production solutions.

A complete Visual Studio solution with all the code shown in this guide is available from GitHub.

In each of the code segments below we'll see the file name from the associated sample project, like this:

We'll also see the using statements so you can better understand how the components reference each other.

Ellipsis (...) in the code segments indicate that in the complete file there is code above or below the segment that is unrelated to the topic at hand.

Implementing a dropdown list with data supplied in a view model involves four components:

We'll look at the essential code for each of these. If you need a better understanding of how these pieces of code fit into each component, the following section will provide a higher level picture of all the components.

Using a view model as the model for the form containing the dropdown elements separates presentation from structure. Keeping things normalized happens on the back end.

In our example, we're creating a new Customer record. Customers have Country and Region (state or province) properties.

1using System.Collections.Generic; 2using System.ComponentModel.DataAnnotations; 3using System.Web.Mvc; 4 5namespace BlipDrop.ViewModels 6{ 7 public class CustomerEditViewModel 8 { 9 [Display(Name = "Customer Number")] 10 public string CustomerID { get; set; } 11 12 [Required] 13 [Display(Name = "Customer Name")] 14 [StringLength(75)] 15 public string CustomerName { get; set; } 16 17 [Required] 18 [Display(Name = "Country")] 19 public string SelectedCountryIso3 { get; set; } 20 public IEnumerable<SelectListItem> Countries { get; set; } 21 22 [Required] 23 [Display(Name = "State / Region")] 24 public string SelectedRegionCode { get; set; } 25 public IEnumerable<SelectListItem> Regions { get; set; } 26 } 27}

Notice that for each property for which we're going to provide dropdown lists, we have two fields in the view model:

1. one for the selected item

The list is made up of a collection of SelectListItem type.

The field storing the selected item holds the unique key for each entity. The unique key of the selected value will be the same as one of the elements of the SelectListItem.

These are the form elements for the Country field on the Razor view showing the implementation of the DropDownListFor HtmlHelper.

1... 2<div class="form-group"> 3 @Html.LabelFor(x => Model.SelectedCountryIso3, htmlAttributes: new { @class = "control-label col-md-2" }) 4 <div class="col-md-5"> 5 @Html.DropDownListFor(x => Model.SelectedCountryIso3, new SelectList(Model.Countries, "Value", "Text"), htmlAttributes: new { @class = "form-control", id = "Country"}) 6 @Html.ValidationMessageFor(x => x.SelectedCountryIso3, "", new { @class = "text-danger" }) 7 </div> 8</div> 9...

Compare the @Html.DropDownListFor statement to the view model.

x => Model.SelectedCountryIso3 identifies the field in the view model where the selected value will be stored. We're storing the unique identifier for the Country.

new SelectList(Model.Countries, "Value", "Text") identifies the source of the list to use in populating the dropdown list, IEnumerable<SelectListItem> Countries from the data model. Note that it does this by creating a new SelectList with fields Value and Text, which correspond to the same fields in the Countries property of the data model.

You cannot use a field of type SelectList in your view model. The Razor engine needs to get the data as a collection of SelectListItems to build the HTML element properly. This is documented as opaquely as possible in the framework documentation on MSDN.

Note that the HTML element on the rendered page will have an id attribute value of Country. JavaScript code we'll look at later will use the id attributes of each of the dropdown elements.

The complete view looks like this when rendered by the browser:

Your controller can be simple: your controller just has to call the appropriate repository to get the view model, then you pass the view model to the view:

1using System.Collections.Generic; 2using System.Web.Mvc; 3using BlipDrop.Data; 4using BlipDrop.ViewModels; 5 6namespace BlipDrop.Controllers 7{ 8 public class CustomerController : Controller 9 { 10 // GET: Customer 11 public ActionResult Index() 12 { 13 var repo = new CustomersRepository(); 14 var customerList = repo.GetCustomers(); 15 return View(customerList); 16 } 17 ... 18 } 19}

Note in the using statements that the controller references the data context and the view models, but doesn't need to access the entities which map to database objects.

In our simple example, the customer repository creates an instance of the CustomerEditViewModel class and assigns a new GUID to the CustomerID field.

It also calls the countries and regions repositories to get the list of countries and regions so that the view model will have the data for the dropdown lists on the view.

1using System; 2using System.Collections.Generic; 3using System.Data.Entity; 4using System.Linq; 5using BlipDrop.Models; 6using BlipDrop.ViewModels; 7 8namespace BlipDrop.Data 9{ 10 public class CustomersRepository 11 { 12 ... 13 public CustomerEditViewModel CreateCustomer() 14 { 15 var cRepo = new CountriesRepository(); 16 var rRepo = new RegionsRepository(); 17 var customer = new CustomerEditViewModel() 18 { 19 CustomerID = Guid.NewGuid().ToString(), 20 Countries = cRepo.GetCountries(), 21 Regions = rRepo.GetRegions() 22 }; 23 return customer; 24 } 25 ... 26 } 27}

Note that in this scenario we don't need to use an instance of the data context, ApplicationDbContext, to create an instance of the CustomeEditViewModel because only the Countries and Regions fields require calls to the database to get values, and those calls are handled by the respective repositories for those objects.

1using System.Collections.Generic; 2using System.Linq; 3using System.Web.Mvc; 4 5namespace BlipDrop.Data 6{ 7 public class CountriesRepository 8 { 9 public IEnumerable<SelectListItem> GetCountries() 10 { 11 using (var context = new ApplicationDbContext()) 12 { 13 List<SelectListItem> countries = context.Countries.AsNoTracking() 14 .OrderBy(n => n.CountryNameEnglish) 15 .Select(n => 16 new SelectListItem 17 { 18 Value = n.Iso3.ToString(), 19 Text = n.CountryNameEnglish 20 }).ToList(); 21 var countrytip = new SelectListItem() 22 { 23 Value = null, 24 Text = "--- select country ---" 25 }; 26 countries.Insert(0, countrytip); 27 return new SelectList(countries, "Value", "Text"); 28 } 29 } 30 } 31}

Note that the return type for the CountryRepository is the same type as the Countries list in the view model, IEnumerable<SelectListItem>. This is the same collection as used by the @Html.DropDownListFor statement in the Razor view. The Value element of each SelectListItem is the Country's Iso3 code converted from a GUID to text.

RegionsRepository.GetRegions() returns an empty SelectListItem collection. This is so that the view model doesn't pass a null object reference to the Region dropdown list.

1using System; 2using System.Collections.Generic; 3using System.Linq; 4using System.Web.Mvc; 5 6namespace BlipDrop.Data 7{ 8 public class RegionsRepository 9 { 10 public IEnumerable<SelectListItem> GetRegions() 11 { 12 List<SelectListItem> regions = new List<SelectListItem>() 13 { 14 new SelectListItem 15 { 16 Value = null, 17 Text = " " 18 } 19 }; 20 return regions; 21 } 22 } 23}

Initializing a dependent dropdown in this way also gives you an opportunity to display an informational message (e.g., "select a country first") before populating the list values.

This technique adds two more pieces of code to the ones above:

• An additional HttpGet controller action

The Create view for the Razor includes a dropdown for Regions that works just like the one for Countries:

1... 2<div class="form-group"> 3 @Html.LabelFor(x => Model.SelectedRegionCode, htmlAttributes: new { @class = "control-label col-md-2" }) 4 <div class="col-md-5"> 5 @Html.DropDownListFor(x => Model.SelectedRegionCode, new SelectList(Model.Regions, "Value", "Text"), htmlAttributes: new { @class = "form-control", @id = "Region" }) 6 @Html.ValidationMessageFor(x => x.SelectedRegionCode, "", new { @class = "text-danger" }) 7 </div> 8</div> 9...

Remember that we've initialized the Regions field of the view model with an empty list. We'll need to go and get some values for the list when the user selects a country.

Populating the list values for a dropdown field on an HTML form requires an event on the client side. This technique uses the jQuery JavaScript library to bind an event handler to the Change event of the Country dropdown. Whenever the user selects a value in the Country dropdown, the event fires and the JavaScript code executes.

1@section Scripts { 2 @Scripts.Render("~/bundles/jqueryval") 3 <script type="text/javascript"> 4 $('#Country').change(function () { 5 var selectedCountry = $("#Country").val(); 6 var regionsSelect = $('#Region'); 7 regionsSelect.empty(); 8 if (selectedCountry != null && selectedCountry != '') { 9 $.getJSON('@Url.Action("GetRegions")', { iso3: selectedCountry }, function (regions) { 10 if (regions != null && !jQuery.isEmptyObject(regions)) 11 { 12 regionsSelect.append($('<option/>', { 13 value: null, 14 text: "" 15 })); 16 $.each(regions, function (index, region) { 17 regionsSelect.append($('<option/>', { 18 value: region.Value, 19 text: region.Text 20 })); 21 }); 22 }; 23 }); 24 } 25 }); 26 </script> 27}

This looks like a lot if you are relatively new to JavaScript, but it's easy to unpack:

1. When the change event of the Country HTML element (the dropdown list) fires:

   • Get the value of the element and store it in the selectedCountry variable.
   • Clear out anything that was previously in the Region dropdown list.

2. If the Country field contains a value (and it might not because the first element of the list of Countries is a blank item):

   • Call the GetRegions(string Iso3) controller action, passing it the value selected in Country, and storing the results in the object variable regions.
   • If regions is not null or empty (which it can be if there are no regions associated with a country):

     • Create a blank element as the first element of the list.
     • Iterate through the collection in regions and add each element to the dropdown's list of values.

The jQuery functions begins with $. For more information on the functions used see:

The JavaScript on the page (Create.cshtml) calls a controller action to get the data from the server asynchronously. The controller action's job is to call the repository to get the data, then convert it from an IEnumerable of SelectListItems to JSON format and pass it back to the page.

1using System.Collections.Generic; 2using System.Web.Mvc; 3using BlipDrop.Data; 4using BlipDrop.ViewModels; 5 6namespace BlipDrop.Controllers 7{ 8 public class CustomerController : Controller 9 { 10 ... 11 [HttpGet] 12 public ActionResult GetRegions(string iso3) 13 { 14 if (!string.IsNullOrWhiteSpace(iso3) && iso3.Length == 3) 15 { 16 var repo = new RegionsRepository(); 17 18 IEnumerable<SelectListItem> regions = repo.GetRegions(iso3); 19 return Json(regions, JsonRequestBehavior.AllowGet); 20 } 21 return null; 22 } 23 ... 24}

Note that while it works perfectly well to pass an IEnumerable to a view using ASP.NET model binding, that doesn't work for Ajax calls. Model binding takes care of converting the IEnumerable to HTML.

RegionsRepository.cs contains two signatures for GetRegion:

GetRegions() which returns an empty list of regions and GetRegions(string iso3) which returns the list of regions associated with the country code passed in the parameter.

We looked at the first signature when we looked at the Regions Repository] for the first time above.

This method works much like the GetCountries method, except that the ios3 parameter is used to select a subset of the records from the Regions table of the database.

1using System; 2using System.Collections.Generic; 3using System.Linq; 4using System.Web.Mvc; 5 6namespace BlipDrop.Data 7{ 8 public class RegionsRepository 9 { 10 ... 11 public IEnumerable<SelectListItem> GetRegions(string iso3) 12 { 13 if (!String.IsNullOrWhiteSpace(iso3)) 14 { 15 using (var context = new ApplicationDbContext()) 16 { 17 IEnumerable<SelectListItem> regions = context.Regions.AsNoTracking() 18 .OrderBy(n => n.RegionNameEnglish) 19 .Where(n => n.Iso3 == iso3) 20 .Select(n => 21 new SelectListItem 22 { 23 Value = n.RegionCode, 24 Text = n.RegionNameEnglish 25 }).ToList(); 26 return new SelectList(regions, "Value", "Text"); 27 } 28 } 29 return null; 30 } 31 } 32}

Note that we're setting the order of the elements to be the region name, rather than RegionCode. This is so the user sees the list of regions, like US states, in a predictable alphabetical order, rather than an order dictated by the index value.

The case study is a (very) simple list of customers. Each is assigned a GUID as a unique identifier. Their name, country, and region define each Customer object.

Countries can have zero or more regions.

Relational database schema diagram for multiple dropdowns example project

The classes for the data entities form the Object side of the Object Relational Mapper (ORM) that is Entity Framework. Using code-first entity migrations, as is done in this project, enables the database tables and their properties, including their relationships, to be defined in code. By defining the database in this fashion, it can be kept in synchronization with the entities for which it provides a persistent data store.

1using System; 2using System.ComponentModel.DataAnnotations; 3using System.ComponentModel.DataAnnotations.Schema; 4 5namespace BlipDrop.Models 6{ 7 public class Customer 8 { 9 [Key] 10 [Column(Order = 1)] 11 public Guid CustomerID { get; set; } 12 13 [Required] 14 [MaxLength(128)] 15 public string CustomerName { get; set; } 16 17 [Required] 18 [MaxLength(3)] 19 public string CountryIso3 { get; set; } 20 21 [MaxLength(3)] 22 public string RegionCode { get; set; } 23 24 public virtual Country Country { get; set; } 25 26 public virtual Region Region { get; set; } 27 } 28}

In the customer class, we can identify the required fields and set the maximum length of string fields (as well as other attributes). Entity Framework uses these attributes when creating and modifying the database.

The maximum length specified in the entity class defines the maximum size of the database field. In the view model, the StringLength attribute is used to identify the maximum size of the field input.

In the case study we've done a couple of seemingly inconsistent things to demonstrate how Entity Framework and the model-view view-model (MVVM) design pattern work together:

• In the view model the CustomerName field is [StringLength(75)] but the database field size is [MaxLength(128)], demonstrating that input requirements can be different than database requirements (making the input size larger than the database size can cause problems).
• In the view model the RegionCode is Required, but it's an optional field in the database. This is to demonstrate the error handling HtmlHelpers.

Database relationships are defined with navigation properties like the one for the Customer-Country relationship:

1public virtual Country Country { get; set; }

This defines the foreign key relationship the Country table.

See the More information section for learning resources if you need a better understanding of Entity Framework.

1using System.Collections.Generic; 2using System.ComponentModel.DataAnnotations; 3 4namespace BlipDrop.Models 5{ 6 public class Country 7 { 8 [Key] 9 [MaxLength(3)] 10 public string Iso3 { get; set; } 11 12 [Required] 13 [MaxLength(50)] 14 public string CountryNameEnglish { get; set; } 15 16 public virtual IEnumerable<Region> Regions { get; set; } 17 } 18}

This class has many of the same features as the Customer class, but in this case, the navigation property identifies a one-to-many relationship between Countries and Regions.

1using System.ComponentModel.DataAnnotations; 2 3namespace BlipDrop.Models 4{ 5 public class Region 6 { 7 [Key] 8 [MaxLength(3)] 9 public string RegionCode { get; set; } 10 11 [Required] 12 [MaxLength(3)] 13 public string Iso3 { get; set; } 14 15 [Required] 16 public string RegionNameEnglish { get; set; } 17 18 public virtual Country Country { get; set; } 19 } 20}

In the sample project, each region has a unique RegionCode. In real life, the unique identifier for a region is more likely to be a compound code consisting of the country identifier and the region code. For the sake of simplicity, we've simplified the structure.