ASP.NET MVC Cascading Dropdown List

I have been receiving lots of emails from devs requesting me to send them materials to help learn ASP.NET MVC. I usually point them to http://www.asp.net/mvc which is a good starting point. However often when you get started with a project, things are not exactly as in tutorials and you start hitting snags, little things that are different from tutorials and require you to do multiple searches to get their answers to. In essence small issues, already solved, you just need the semantics and gotchas if any.

Starting today, we (Sumit Maitra and Suprotim Agarwal) are starting a MVC 101 series for ASP.NET MVC in which we hope to capture some of these small issues that we see devs who are new into MVC, get stuck with. Today’s example is very simple. We want to save an Address, the State field should get populated after the Country has been selected and it should do it without a full page post back. We use Enterprise Framework DB first with EF Db Context generator (entities will still be POCOs).

Getting Started

I actually looked up some online databases to see if I could get a list of all countries with their states but the database was upto 250+ MBs. So I settled down with two countries and their states (India and USA).


An excel sheet is committed to the repository that contains this data.

Step 1: The Database

The Database Diagram is as follows:

database-diagram

We have the ‘master’ tables ‘country’ and ‘state’ and we have the ‘address’ table that has two fields that refer to master data. A VERY common scenario in a LoB app.

The script for creating the tables is in the ‘CreateTable.sql’.

Step 2: Entity Framework DB First

Now I chose DB first because recently I’ve received requests for DB first and come to think of enterprise application development it’s often designed DB First. Anyway, nothing would change if it were code first, except that you would write the POCOs first and let the DB get generated by EF.

Step 3: Adding an EDMX and Generating Model

- Right click on the Models folder and select Add New Item. In the Models folder, VS 2012 also gives you option to add ADO.NET Entity Model directly, it will as for a name, only in that case.

add-new-edmx

- Connect to the database by either creating a new Connection or Using an existing one.

connect-to-database

- Next pick connection name in Web.Config

cascading-drop-dwon

- Select the Tables required (note we are leaving out the sysdiagrams table) that’s used by SQL Management Studio to store DB Diagrams. At this point, the EDMX creation can complete and you’ll have the generated entities for yourself.

Step 4: Using EF Code First

Let’s add EF 5.x DbContext Generator so that the code generated by EDMX is Code First compliant. To do this, open the EDMX Designer, right click on it and select add Code Generation item. It will bring up the following Dialog. Name the context file appropriately because it’s what is going to be used as the base for your Context files.

select-database-objects

NOTE: Once you do this, your Entity files are regenerated. At this point you will get a build error saying your entity class file already exist. Delete these older files (highlighted).

cleanup

This completes EF Mapping and code generation.

Step 5: Generating the Controller

Right click on the Models folder and select Add Controller from the Menu. This brings up the following dialog and selects the Data Context automatically

controller

- Provide a Controller name and select the entity name.

- Click add for the tooling to generate the controller and the Views.

- Build and Run the Application

default-dropdown-data

Thanks to the relationships in the database, MVC Tooling will generate a Create Address page that pulls up all the Countries and all the States in dropdowns. However as you will notice above, there is no ‘cascading’ or ‘filtering’ effect applied and states from both the countries are shown.

Implementing Cascading with a little bit of jQuery

To implement the cascading effect, we do the following:

Step 1: Stop binding the State dropdown

removed-state-binding

In the Create action method, MVC generates code to bind state_id to a SelectList that has all the states from the database. We comment this line out because we need a filtered list to be bound.

Step 2: Adding a Controller method to get filtered data

Next we add a Method in the controller that will be called via AJAX with the appropriate country ID and return a JSON result of the states for that country only.

public JsonResult SelectStates(int id)
{
IEnumerable<state> states = db.states.Where(stat => stat.country_id == id);
return Json(states);
}


Step 3: Add client side scripting

Finally we add the required JavaScript to invoke the action method when the Country dropdown changes

<script>
$(document).ready(function ()
{
//Dropdownlist Selectedchange event
$("#country_id").change(function ()
{
  $("#state_id").empty();
  $.ajax({
   type:'POST',
   url: '@Url.Action("SelectStates")',
   dataType: 'json',
   data: { id: $("#country_id").val() },
   success: function (states)
   {
    // states contains the JSON formatted list
    // of states passed from the controller
    $.each(states, function (i, state)
    {
     $("#state_id").append('<option value="'
      + state.state_id + '">'
      + state.state_name + '</option>');
    });
   },
   error: function (ex)
   {
    alert('Failed to retrieve states.' + ex);
   }
  });
  return false;
})
});
</script>


As we can see, we assign a ‘change’ event handler to the “country_id” dropdown. Thereafter we do an AJAX POST to the SelectStates Action method with the data ‘id’ having the value of the selected country_id.

On a successful post, we get back a JSON Formatted list of states for the selected country and use this to add <option… /> items to the state_id dropdown box.

So far so good.

Step 4: Running into a Gotcha and fixing it

Run the app and SPLAT! Instead of getting an updated list, you get a screen full of HTML Gibberish! Part of it is shown below

errormessage

Well, a quick search reveals that we’ve to disable Dynamic Proxy creation when querying EF for data that we are converting to JSON.

NOTE: You are not going to hit this error if you use Domain Entities instead of the direct EF entities that we use here (See! best practices have no exceptions ;-)…)

Well the solution is simple. Go back to the Action method and add a line to disable Dynamic Proxies on you EF DBContext.

public JsonResult SelectStates(int id)
{
db.Configuration.ProxyCreationEnabled = false;
IEnumerable<state> states = db.states.Where(stat => stat.country_id == id);
return Json(states);
}


Now you are good to go. Run the application and select country as United States or India and see the states change dynamically.

Conclusion

We saw how to do a very small but often used functionality in ASP.NET MVC – Cascading Drop Down. We also saw a weird ‘A circular reference was detected while serializing an object of type…’ error, that we quickly nailed.

Code for this article can be downloaded from here https://github.com/devcurry/mvc101-cascading-dropdowns




11 comments:

Anonymous said...

Hi Karan,
I am assuming you are loosing the selected 'state'. You can do two things.
1. Don't comment out the ViewBag.state_id. This way the first population will be from the view bag and when Razor renders the view it will setup the correct state based on the state_id in your entity.
2. Instead of converting the IEnumberable to Json, create a SelectList and pass the selected ID to it. Then serialize to Json. Use the 'selected' property in jQuery function to mark the correct option as 'selected'.

These are from the top of my head. Let me know if you are still stuck.

-Sumit

Anonymous said...

Actually ignore the first point. That might create more confusion than it solves.
-Sumit.

Oleg Kolpashchikov said...

An ideal version could also work the other way around:
if the user selects the state right away (before the country), the country dropdown list should automatically choose the correct country

LE said...

You have lots of code, which is great. But, in a few cases, you don't really specify where the changes go.

Can you specify what file the changes go in?

LE said...

This is completely horrible, but to fix the issue about, instead of commenting out the ViewBag.state_id, I put this in:

ViewBag.state_id = new SelectList(db.states.Where(e => e.state_id == 0));

Anonymous said...

Hello LE,
The code in 'Implementing Cascading with a little bit of jQuery' section refers to the 'addressController'. The JavaScript is in the create.cshtml file.

Regarding your second comment you are simply filling the viewbag with an empty select list that's never used because the data-bind for the state dropdown is replaced with the Ajax Call, on change of the Country drop down. The State dropdown is populated on the client side now.

Hope this helps.
-Sumit

Anonymous said...

Hello. This tutorial is good but the same effect that we get in create view, we don't get it in edit view. In edit view if you change the country still the state ddl remains unchanged and in the same state if we click save then the record is saved with wrong data. Eg. If while creating the record I have selected India as country and Maharastra as State then in edit view if I change country to USA keeping Maharastra intact saves the record. Plz correct it in edit view or give some perfect solution. Thanks

brace said...

Geat tutorial! It worked for me. I just was wondering on how to make it work for a third dropdown level. I've tried it, repeating this process, but the third dropdown isn't populating.
Any suggestions...?Thanks in advance

Unknown said...

Hi just wanted to know how to implement the same when javascript is disabled in asp.net MVC. Can we get the similar functionality without javascript as we can get in asp.net web forms post back events?

Sandeep Raturi said...

Thanks nice article but at the time of edit dropdown select index changed is not working. All country and states value is showing there.

Sandeep Raturi said...

Hi Anybody help me out?? Iam facing problem at the time of Edit mode, All country and states showing there at the time of edit any records.