Create a Dropdown List for MVC3 using Entity Framework (.edmx Model) & Razor Views && Insert A Database Record to Multiple Tables

Question

After reading 100's of articles on here about how to create a DropDown List in MVC 3 with Razor Views, I could not find one that fit my case.

Situation: I am ultimately trying to create a View to Add an Employee to a Database.

Here is an image of the .EDMX Model that I am using (the tables that will be used by the create().):

Objectives:

1. Create an Employee (I have the Create.cshtml (strongly typed) made with a Partial View for the StaffNotify Checkboxes) {I am using a separate @model in the Notify Partial View from the Create View not sure if that is safe??? @model ShadowVenue.Models.Employee & @model ShadowVenue.Models.StaffNotify)

2. Create a Dropdown box for StaffTypeId (that will insert the [StaffTypeId] value from the Table "StaffType" (which has a 1 to many relationship), but will show the [Type] string value in the dropdown)

3. Create a Dropdown box for GenderId (that will insert the [GenderId] value from the Table "Genders" (which has a 1 to many relationship), but will show the [Gender] string value in the dropdown)

4. Insert the Record into the database (I have the Staff Notifications in a separate table with a 1 to 1 relationship on the StaffId Primary Key)

I seem to be having the trouble with the Controller code for this.

I am not sure if I should create Stored Procedure within the EDMX model, or come up with some query or method syntax, not sure which is the best way.

This my First Large MVC3 App using Entity Framework Model.

(if you need to know any of the Navigation Property Names in order to help with the solution just let me know, I will provide them to you)

Answer 1

Don't pass db models directly to your views. You're lucky enough to be using MVC, so encapsulate using view models.

Create a view model class like this:

public class EmployeeAddViewModel
{
    public Employee employee { get; set; }
    public Dictionary<int, string> staffTypes { get; set; }
    // really? a 1-to-many for genders
    public Dictionary<int, string> genderTypes { get; set; }

    public EmployeeAddViewModel() { }
    public EmployeeAddViewModel(int id)
    {
        employee = someEntityContext.Employees
            .Where(e => e.ID == id).SingleOrDefault();

        // instantiate your dictionaries

        foreach(var staffType in someEntityContext.StaffTypes)
        {
            staffTypes.Add(staffType.ID, staffType.Type);
        }

        // repeat similar loop for gender types
    }
}

Controller:

[HttpGet]
public ActionResult Add()
{
    return View(new EmployeeAddViewModel());
}

[HttpPost]
public ActionResult Add(EmployeeAddViewModel vm)
{
    if(ModelState.IsValid)
    {
        Employee.Add(vm.Employee);
        return View("Index"); // or wherever you go after successful add
    }

    return View(vm);
}

Then, finally in your view (which you can use Visual Studio to scaffold it first), change the inherited type to ShadowVenue.Models.EmployeeAddViewModel. Also, where the drop down lists go, use:

@Html.DropDownListFor(model => model.employee.staffTypeID,
    new SelectList(model.staffTypes, "ID", "Type"))

and similarly for the gender dropdown

@Html.DropDownListFor(model => model.employee.genderID,
    new SelectList(model.genderTypes, "ID", "Gender"))

Update per comments

For gender, you could also do this if you can be without the genderTypes in the above suggested view model (though, on second thought, maybe I'd generate this server side in the view model as IEnumerable). So, in place of new SelectList... below, you would use your IEnumerable.

@Html.DropDownListFor(model => model.employee.genderID,
    new SelectList(new SelectList()
    {
        new { ID = 1, Gender = "Male" },
        new { ID = 2, Gender = "Female" }
    }, "ID", "Gender"))

Finally, another option is a Lookup table. Basically, you keep key-value pairs associated with a Lookup type. One example of a type may be gender, while another may be State, etc. I like to structure mine like this:

ID | LookupType | LookupKey | LookupValue | LookupDescription | Active
1  | Gender     | 1         | Male        | male gender       | 1
2  | State      | 50        | Hawaii      | 50th state        | 1
3  | Gender     | 2         | Female      | female gender     | 1
4  | State      | 49        | Alaska      | 49th state        | 1
5  | OrderType  | 1         | Web         | online order      | 1

I like to use these tables when a set of data doesn't change very often, but still needs to be enumerated from time to time.

Hope this helps!