Adding additional query to return(View)

Question

I have a ViewModel and I currently return a query where if the movie has employees and they have a role then that movie, actor(s), and role(s) are all returned.

Now I want to add an additional query to this that makes a string (or whatever works best) of all the employees within the same movie. So if three people were in the same movie, I want a list of them so that I can call it in my View.

My current query/Controller is

        var parameter = Int32.Parse(Filter);
        var queryString =
        from m in db.Movies

        join me in db.MovieEmployees
        on m.ID equals me.movieID

        join e in db.Employees
        on me.employeeID equals e.ID

        join r in db.Roles
        on me.roleID equals r.ID

        where (parameter == 1 && m.Name.Contains(searchString)) || 
              (parameter == 2 && e.Name.Contains(searchString)) || 
              (parameter == 3 && r.RoleType.Contains(searchString))

        select new StarringViewModel { employeeID = e.ID, movieID = m.ID, roleID = r.ID,
               movieName = m.Name, movieDescription = m.Description, 
               movieReleaseDate = m.ReleaseDate, employeeBirthdate = e.Birthday, 
               employeeName = e.Name, Role = r.RoleType };

            return View(queryString.Distinct().ToList().OrderBy(x => x.movieName));

And it looks like this

See how I have two employees in "The Greatest Movie Ever", I want to return that list of employees (employeeList) within the "Starring" field I have below the movies

For reference, here is my Model, ViewModel, and DB layout.

Model

public class StarringViewModel
    {
        public int movieID { get; set; }
        public int roleID { get; set; }
        public int employeeID { get; set; }
        public string movieName { get; set; }
        public string movieDescription { get; set; }
        public DateTime? movieReleaseDate { get; set; }
        public string Role { get; set; }
        public string employeeName { get; set; }
        public DateTime employeeBirthdate { get; set; }
        public string employeeList { get; set; } // <-- I want this to be the list of actors in same movies
    }

View

<table class="table table-striped table-hover table-responsive table-condensed">
        <tr>
            <th>
                <h3 style="font-size: x-large; font-weight: bolder">Movie Name</h3>
            </th>
            <th>
                <h3 style="font-size: x-large; font-weight: bolder">Release Date</h3>
            </th>
            <th>
                <h3 style="font-size: x-large; font-weight: bolder">Employee</h3>
            </th>
            <th>
                <h3 style="font-size: x-large; font-weight: bolder">@Html.DisplayNameFor(model => model.Role)</h3>
            </th>
            <th>
                @using (Html.BeginForm("Index", "Starring"))
                {
                    <div class="dropdown">
                        <select class="btn btn-group-lg btn-default col-md-4" style="margin-top: 15px; height: 36px; opacity: 1" data-toggle="dropdown" name="Filter">
                            <option value="0" disabled selected>Filter By...</option>
                            <option value="1">Movie Name</option>
                            <option value="2">Actor Name</option>
                            <option value="3">Role</option>
                        </select>
                    </div>

    <input type="text" name="searchString" class="col-md-6" style="margin-top: 16px; text-align:center; height:35px; font-size:20px" placeholder="enter text" />
    <button type="submit" class="btn btn-group-lg btn-primary col-md-2 glyphicon glyphicon-arrow-right" style="margin-top: 15px; height:36px; opacity:1" value="" />
                }
            </th>
            @foreach (var item in Model)
            {

            <tr>
                <td class="col-md-2">
                    <span style="font-size: 17px;">@Html.DisplayFor(modelItem => item.movieName)</span>
                </td>
                <td class="col-md-2">
                    <span style="font-size: 17px;">@Html.DisplayFor(modelItem => item.movieReleaseDate)</span>
                </td>
                <td class="col-md-1">
                    <span style="font-size: 17px;">@Html.DisplayFor(modelItem => item.employeeName)</span>
                </td>
                <td class="col-md-1">
                    <span style="font-size: 17px;">@Html.DisplayFor(modelItem => item.Role)</span>
                </td>
                <td class="col-md-3 col-md-offset-3">
                </td>

            </tr>
                <tr>
                    <td colspan="12">
                        <p style="font-size: 17px; font-style: italic; font-family: 'Roboto', sans-serif">
                            Movie ID: @Html.DisplayFor(modelItem => item.movieID)
                            <br />
                            Description: @Html.DisplayFor(modelItem => item.movieDescription)
                            <br />
                            Starring: // ADD LIST OF EMPLOYEES HERE (item.employeeList)

                        </p>
                    </td>
                </tr>
            }
        </table>

Diagram

If any additional information is needed please let me know.

Thank you!

Answer 1

I think you should add a property IEnumerable<string> employeeNames to the view model.

var query =
from m in db.Movies
from me in m.MovieEmployees
where (parameter == 1 && m.Name.Contains(searchString)) || 
      (parameter == 2 && me.Employee.Name.Contains(searchString)) || 
      (parameter == 3 && me.Role.RoleType.Contains(searchString))
select new StarringViewModel
{ 
    employeeID = e.ID,
    movieID = m.ID, roleID = r.ID,
    movieName = m.Name, 
    movieDescription = m.Description, 
    movieReleaseDate = m.ReleaseDate, 
    employeeBirthdate = me.Employee.Birthday, 
    employeeName = me.Employee.Name, Role = me.Role.RoleType,
    employeeNames = m.MovieEmployees
                    .Selext(x => x.Employee.Name)
};

(Notice, by the way, that I use navigation properties rather than joins).

You can show the employees by concatenating them:

string.Join(", ", employeeNames)

either in the view, or in a property like EmployeeNamesString in the view model.

An example of how to do it in the view model:

public string EmployeeNamesString
{
    get { return string.Join(", ", this.employeeNames); }
}

and display EmployeeNamesString in the view.