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)

Intisar

Check similar question. – Rival

see my tutorial Working with the DropDownList Box and jQuery (asp.net/mvc/tutorials/javascript/… ) and My blog Cascading DropDownList in ASP.Net MVC ( blogs.msdn.com/b/rickandy/archive/2012/01/09/… ) – Adlare

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!

Aldos

Just noticed the //comment about the Genders 1 to many. What would you have recomended. – Intisar

Am I supposed to add this into the model? ClientEntities context = new ClientEntities(); I am not able to access the Classes and Properties with out it. I always have added that to the controllers, never a model. Just wanted to check with you. – Intisar

@Timothy: I guess I was in a sarcastic mood ;) Typically, I just keep an enumeration for genders in my apps. If it's required to be in the db, maybe consolidate it into a Look-up table that doesn't just keep values for Male/Female, but also for other attributes with limited values. I'll edit my answer with examples of both. – Aldos

@Timothy: To answer your second question, the view model is just another model (read: Class) but with a more specific purpose--to encapsulate display data--making your Views easier to deal with in terms of data. Views should be for layout only. Just create another class in your Models folder for the EmployeeAddViewModel. Depending on the complexity of the app, I like to create separate folders (and subsequently namespaces) for different entities. For example, in your case I might add Models/Employees and put the new view model in there. Then, reference the Entities namespace from that model. – Aldos

If you are doing an add rather than edit, would you need to create a new instance of the Employee entity in the no parameter constructor? – Beriosova

@Beriosova I'm not sure if you meant "edit rather than add" since I describe the add above. Ideally though, you'd use a different class altogether for editing these entities. I would call mine EmployeeEditViewModel where there would probably still be a parameterless constructor where you might want (need?) to create an empty instance if you plan on using the to-be-edited employee in multiple steps. – Aldos

Could you please answer #9328697 ? – Precedential

I'm a little confused here, if we call the Get method on your controller (i.e we want the form to add a new employee) we get returned View(new EmployeeAddViewModel()); but this is using the empty constructor so the Dictionaries will be null so the drop down menus will not be populated? – Colleencollege

As far as I can remember, you're right about the constructor--I guess the Dictionary(s) should be created in the empty constructor. I have no idea why I added the constructor with parameters. – Aldos

I'm a student and want to implement this is my project. I'm working with the ViewModel and using MVC5 the only thing not working is the .Add property. Anyone who knows how to fix this? – Landowska

You should probably open another question with your code sample so we can see what's going on. There are two .Add methods in my answer, both of which are straight forward collections/dictionaries. – Aldos

The .Add i'm referring to is Employee.Add(vm.Employee); It says (Does not contain a definition of .Add. I will create a a new question too. #26957757 – Landowska

Answer 2

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.

Mons

+1 for AutoMapper. It makes things like model to view-model much easier. – Swee

Thank you for all your suggestions, I am going to try this right now, I will let you know if I get stuck anywhere. Thanks again. – Intisar

AutoMapper is good thing, but why you write that including the Model as member in the ViewModel is bad? Is it security issue only? I would like not to repeat myself whenever it's possible. Also why passing dictionary to a view is a bad style? – Cowgirl

One reason not to include your Model in your ViewModel is validation. The Model may have different constraints than your ViewModel. For example, I have a DB field with a no nulls constraint, but I want to allow the user to leave that field blank on a particular form, in a particular situation. If they leave the field blank, I'll give it a certain value in my Business Layer, so it won't be null when it's entered into the DB, but it was allowed to be null on user entry. – Tabshey

Why is it a bad thing to pass your model to the view? – Eryn

Its generally accepted as bad practice to make your presentation layer aware of how your data layer works with data. If you had a change to your database, you must look at your UI because its probably broken. – Bawl

If you change the database, the mapping between the view and the data is going to have to be changed somewhere. Makes sense to me to not add an additional layer (complication) to the architecture. Just because it's generally accepted doesn't make it right; bandwagon fallacy. – Nodababus