How do I get this ViewModel correct?

Question

Utilizing MVC 5 and Entity Framework I am trying to set up my a ViewModel for my Index view for a show of Employees and their goals. I have an employee table, an employeeMap table (join table with payload) and a goal table. There is a one-to-many relationship between Employee and EmployeeMap and between Goal and EmployeeMap. I am a total newbie and getting stuck with an index view, which initially should display employees, and when one employee is selected should display the goals of the employees.

I can't get my index action right:

var viewModel = new EmployeeGoals();

        viewModel.Employees = db.Employees
            .Include(d => d.Department)
            .Include(e => e.Position)
            .Include(m => m.EmployeeMaps)
            .Where(d => d.OrganizationID == oid && d.Department.ManagerID == currentUser.EmployeeID)
            .OrderBy(d => d.HireDate);


        if (id != null)
        {
            ViewBag.EmployeeID = id.Value;
            viewModel.EmployeeMaps = viewModel.Employees.Where(e => e.ID == id.Value).Single().EmployeeMaps;
            viewModel.Goals = viewModel.EmployeeMaps.Where(e => e.EmployeeID == ViewBag.EmployeeID).Select(e => e.Goals);



            }

            if (goalID != null)
            {
                ViewBag.GoalID = goalID.Value;
                viewModel.Activities = viewModel.Goals.Where(
                    x => x.ID == goalID).Single().Activities;
            }

            return View(viewModel);

I do get the viewmodel.employees populated correctly, but not the viewmodel.goals

oh, and the viewmodel is:

public class EmployeeGoals
    {
        public IEnumerable<EmployeeMap> EmployeeMaps { get; set; }
        public IEnumerable<Employee> Employees { get; set; }
        public IEnumerable<Goal> Goals { get; set; }
    }

A hint to help me past this would be great. Thanks

Got it working using a different approach:

viewModel.Employees = db.Employees
                .Include(d => d.Department)
                .Include(e => e.Position)
                .Include(m => m.EmployeeMaps)
                .Where(d => d.OrganizationID == oid && d.Department.ManagerID == currentUser.EmployeeID)
                .OrderBy(d => d.HireDate);


            if (id != null)
            {
                ViewBag.EmployeeID = id.Value;
                viewModel.EmployeeMaps = viewModel.Employees.Where(e => e.ID == id.Value).Single().EmployeeMaps;
                viewModel.Goals = from g in db.Goals
                                  join m in db.EmployeeMaps on g.ID equals m.GoalID
                                  join e in db.Employees on m.EmployeeID equals e.ID 
                                  where m.EmployeeID == id.Value
                                  select g;
            }

couldn't figure out how to get a result using navigation properties, so ended up with above solution. It does the job, but am I hitting the database too often?? Also, ended up with a mix of LINQ syntax'es - should go with just one of them, I know :-/

Answer 1

You should use SelectMany:

viewModel.Goals = viewModel.EmployeeMaps
                           .Where(e => e.EmployeeID == ViewBag.EmployeeID)
                           .SelectMany(e => e.Goals);

because EmployeeMaps.Where() is an IEnumerable, so a Select produces IEnumerable<IEnumerable<Goal>>. SelectMany flattens this into IEnumerable<Goal>.