Presenting data from multiple tables to a single view

Question

I realise that this type of question has been posed before, but, as a novice, I'm really have a tough time getting to grips with this problem. Any help is appreciated.

I have two tables - a Staff table, and a Salutation table - for which I have separate models and views. The view's records are placed in a form for user editing, and are populated via a LINQ query. Each staff record contains a Salutation ID value. I want a single view, which would list all the staff records, with the appropriate Salutation attached.

I believe what I need is a ViewModel, which I also believe is a distinct thing from the models I used to create the actual database tables. How I can use such a viewmodel to pull the data I need, however, escapes me. My current code is as follows:

Context:

public class LibraryContext : DbContext
{
    public DbSet<PrmTbl_Staff> PrmTbl_Staffs { get; set; }
    public DbSet<PrmTbl_Salutation> PrmTbl_Salutations { get; set; }
}

Models

public class PrmTbl_Staff
{
    public int ID { get; set; }
    public int SalutationID { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }

    public PrmTbl_Staff(int id, int salID, string name, Boolean active)
    {
        ID = id;
        SalutationID = salID;
        Name = name;
        Active = active;
    }
}

public class PrmTbl_Salutation
{
    public int ID { get; set; }
    public string Desc { get; set; }
    public bool Active { get; set; }

    public PrmTbl_Salutation(int id, string desc, Boolean active)
    {
        ID = id;
        Desc = desc;
        Active = active;
    }
}

Controllers

    private LibraryContext staffDB = new LibraryContext();

    public ActionResult Staff()
    {
        ViewBag.Title = "Parameters - Staff";
        var StaffQuery = from st in staffDB.PrmTbl_Staffs
                         join sal in staffDB.PrmTbl_Salutations on st.SalutationID equals sal.ID
                         where st.Active == true
                         orderby st.ID descending
                         select st;
        var count = StaffQuery.Count();
        if (count == 0)
        {
            ViewBag.NullRes = "There are no Results to View!";
        }
        return View(StaffQuery.ToList());
    }

    private LibraryContext saltDB = new LibraryContext();

    public ActionResult Salutation()
    {
        ViewBag.Title = "Parameters - Salutations";
        var SaltQuery =
            (from a in saltDB.PrmTbl_Salutations
             where a.Active == true
             orderby a.ID descending
             select a);
        var count = SaltQuery.Count();
        if (count == 0)
        {
            ViewBag.NullRes = "There are no Results to View!";
        }
        return View(SaltQuery.ToList());
    }

View:

if (ViewBag.NullRes == null) {

    using (Html.BeginForm("UpdateStaff", "Parameter", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        @Html.AntiForgeryToken()
        @Html.ValidationSummary(true)

        <fieldset>
            <legend>Update Form</legend>
            <table>
                <thead>
                    <tr>
                        <th>@Html.DisplayNameFor(model => model.ID)</th>
                        <th>@Html.DisplayNameFor(model => model.SalutationID)</th>
                        <th>@Html.DisplayNameFor(model => model.Name)</th>
                        <th>@Html.DisplayNameFor(model => model.Active)</th>
                    </tr>
                </thead>

                @foreach (var item in Model)
                {
                    var thisID = item.ID;
                    var thisSalID = item.SalutationID;
                    var thisName = item.Name;
                    var thisActive = item.Active;
                    // note that intellisense does not pick up any values 
                    // from the Salutations table, so I assume my 
                    // LINQ query is incomplete. For now, I'll just echo the ID

                    <tr>
                        <td class="tdsm centered fade"> @thisID </td>
                        <td> <input type="text" name="@(thisID + ".salID")" value="@thisSalID" /> </td>
                        <td> <input type="text" name="@(thisID + ".name")" value="@thisName" /> </td>
                        <td class="tdsm centered">
                            @{
                                if (@thisActive)
                                {
                                    <text><input type="checkbox" name="@(thisID + ".active")" value="true" checked="checked" /></text>
                                }
                                else
                                {
                                    <text><input type="checkbox" name="@(thisID + ".active")" value="true" /></text>
                                }
                            }
                        </td>
                    </tr>
                }
            </table>
            <p> <input type="submit" value="Update" /> </p>
        </fieldset>
    } 
}
else {
    <p>@ViewBag.NullRes</p>
}

So, in summary, I want the Desc value from the Salutations table to display in the view alongside the Staff person's name. Ideally I would like all salutations to display as a dropdown, with the appropriate one on display, but if I can just break the initial problem I'd be just as happy.

Note that I did try my hand at a ViewModel (created just like the other models, and the View would pick it up, but I don't know how to draw any values from it, or how they pair up with each other Salt => Name-wise...) I include it here anyway.

public class StaffSalutation
{
    public List<PrmTbl_Salutation> Salutations { get; set; }
    public List<PrmTbl_Staff> Staffs { get; set; }
}

As I said, I'm really grateful for any assistance - and the more explanation-heavy, the better! Some of the concepts of MVC are slipping by me I fear.

EDIT - Current Controller

    private LibraryContext staffDB = new LibraryContext();

    public ActionResult Staff()
    {
        ViewBag.Title = "Parameters - Staff";
        IEnumerable<PrmTbl_Staff> StaffQuery;

        StaffQuery = from st in staffDB.PrmTbl_Staffs
                        join sal in staffDB.PrmTbl_Salutations on st.SalutationID equals sal.ID
                        where st.Active == true
                        orderby st.ID descending
                        select st;

        if (StaffQuery.Count() == 0)
        {
            ViewBag.NullRes = "There are no Results to View!";
        }

        var viewModel = StaffQuery.Select(staff =>
            new StaffSalutation
            {
                StaffID = staff.ID,
                Name = staff.Name,
                Salutation = staff.Salutation.Desc,
                Active = staff.Active
            }
        );

        return View(viewModel);

    }

Answer 1

The idea of ViewModel is to have a simple, logic-free, bag for data which can be passed to a view. The ViewModel might contain data coming from different models, in your case Staff and Salutation, but it should only contain data used in the view.

So to display list of staff with salutation, you would use the following ViewModel:

public class StaffSalutation
{
    public int StaffID { get; set; }
    public string Name { get; set; }
    public string Salutation {get; set; }
}

To get the your ViewModel you can use something like:

IEnumerable<PrmTbl_Staff> StaffQuery;
//get your data here
var viewModel = StaffQuery.Select(staff => 
    new StaffSalutation 
    { 
          StaffID = staff.ID, 
          Name = staff.Name, 
          Salutation = staff.Salutation.Desc 
    });
return View(viewModel);

Note that I used missing reference in your object model between Staff and Salutation, so your class would be looking more like:

public class PrmTbl_Staff
{
    public int ID { get; set; }
    public int SalutationID { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }

    //reference to Salutation
    public PrmTbl_Salutation Salutation {get; set;}

    //...
}