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

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().):

enter image description here

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)

The answer is


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!


Well, actually I'll have to say David is right with his solution, but there are some topics disturbing me:

  1. You should never send your model to the view => This is correct
  2. If you create a ViewModel, and include the Model as member in the ViewModel, then you effectively sent your model to the View => this is BAD
  3. Using dictionaries to send the options to the view => this not good style

So how can you create a better coupling?

I would use a tool like AutoMapper or ValueInjecter to map between ViewModel and Model. AutoMapper does seem to have the better syntax and feel to it, but the current version lacks a very severe topic: It is not able to perform the mapping from ViewModel to Model (under certain circumstances like flattening, etc., but this is off topic) So at present I prefer to use ValueInjecter.

So you create a ViewModel with the fields you need in the view. You add the SelectList items you need as lookups. And you add them as SelectLists already. So you can query from a LINQ enabled sourc, select the ID and text field and store it as a selectlist: You gain that you do not have to create a new type (dictionary) as lookup and you just move the new SelectList from the view to the controller.

  // StaffTypes is an IEnumerable<StaffType> from dbContext
  // viewModel is the viewModel initialized to copy content of Model Employee  
  // viewModel.StaffTypes is of type SelectList

  viewModel.StaffTypes =
    new SelectList(
        StaffTypes.OrderBy( item => item.Name )
        "StaffTypeID",
        "Type",
        viewModel.StaffTypeID
    );

In the view you just have to call

@Html.DropDownListFor( model => mode.StaffTypeID, model.StaffTypes )

Back in the post element of your method in the controller you have to take a parameter of the type of your ViewModel. You then check for validation. If the validation fails, you have to remember to re-populate the viewModel.StaffTypes SelectList, because this item will be null on entering the post function. So I tend to have those population things separated into a function. You just call back return new View(viewModel) if anything is wrong. Validation errors found by MVC3 will automatically be shown in the view.

If you have your own validation code you can add validation errors by specifying which field they belong to. Check documentation on ModelState to get info on that.

If the viewModel is valid you have to perform the next step:

If it is a create of a new item, you have to populate a model from the viewModel (best suited is ValueInjecter). Then you can add it to the EF collection of that type and commit changes.

If you have an update, you get the current db item first into a model. Then you can copy the values from the viewModel back to the model (again using ValueInjecter gets you do that very quick). After that you can SaveChanges and are done.

Feel free to ask if anything is unclear.


Examples related to entity-framework

Entity Framework Core: A second operation started on this context before a previous operation completed EF Core add-migration Build Failed Entity Framework Core add unique constraint code-first 'No database provider has been configured for this DbContext' on SignInManager.PasswordSignInAsync The instance of entity type cannot be tracked because another instance of this type with the same key is already being tracked Auto-increment on partial primary key with Entity Framework Core Working with SQL views in Entity Framework Core How can I make my string property nullable? Lazy Loading vs Eager Loading How to add/update child entities when updating a parent entity in EF

Examples related to asp.net-mvc-3

Better solution without exluding fields from Binding IIs Error: Application Codebehind=“Global.asax.cs” Inherits=“nadeem.MvcApplication” Can we pass model as a parameter in RedirectToAction? return error message with actionResult Why is this error, 'Sequence contains no elements', happening? Failed to load resource: the server responded with a status of 500 (Internal Server Error) in Bind function 500.19 - Internal Server Error - The requested page cannot be accessed because the related configuration data for the page is invalid String MinLength and MaxLength validation don't work (asp.net mvc) How to set the value of a hidden field from a controller in mvc How to set a CheckBox by default Checked in ASP.Net MVC

Examples related to razor

Uncaught SyntaxError: Invalid or unexpected token How to pass a value to razor variable from javascript variable? error CS0103: The name ' ' does not exist in the current context how to set radio button checked in edit mode in MVC razor view @Html.DropDownListFor how to set default value Razor MVC Populating Javascript array with Model Array How to add "required" attribute to mvc razor viewmodel text input editor How to correctly use Html.ActionLink with ASP.NET MVC 4 Areas Multiple radio button groups in MVC 4 Razor How to hide a div element depending on Model value? MVC

Examples related to edmx

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

Examples related to html.dropdownlistfor

onchange event for html.dropdownlist Populating a razor dropdownlist from a List<object> in MVC Fill drop down list on selection of another drop down list twitter bootstrap autocomplete dropdown / combobox with Knockoutjs MVC3 DropDownListFor - a simple example? Create a Dropdown List for MVC3 using Entity Framework (.edmx Model) & Razor Views && Insert A Database Record to Multiple Tables How to write a simple Html.DropDownListFor()?