Q:

Including related data in controller

I have a Details page that displays a report, I now need an index page that displays more than one report. I have set this up with some sucess (I can select checkboxes on a page of users and display some of the information on a 'Print page' but I can't figure out how to display related info.
My details takes in a userid like this - 
 public ViewResult Details(string id)
        {
User user = db.Users
                .Include(i => i.UserDetails)
                .Include(i => i.TRS)
                .Include(i => i.Pension)
                .Include(i => i.LifeAssurance)
                .Include(i => i.IncomeProtection)
                .Where(i => i.UserName == id)
                .Single();

string pensionProviderName = providerRepository.GetProviderByID(user.Pension.PensionProviderID).ProviderName;

TRSViewModel viewModel = new TRSViewModel
            {
                UserName = user.UserName,
                FirstName = user.UserDetails.FirstName,
                LastName = user.UserDetails.LastName,
                Salary = user.UserDetails.Salary,
                PensionSchemeName = pensionSchemeName

but in my index I have
public ViewResult Index(FormCollection form)
        { 
          string[] UserIDs = form["TRSIDs"].Split(',');

var trs = db.Users
                .Include(t => t.UserDetails)
                .Include(t => t.TRS)
                .Include(t => t.Pension)
                .Where(g => UserIDs.Contains(g.UserName))
                .Select(user => new TRSViewModel
            {
                UserName = user.UserName,
                FirstName = user.UserDetails.FirstName,
                LastName = user.UserDetails.LastName,
                Salary = user.UserDetails.Salary

but I can't figure out how to include the 'PensionSchemeName' details in teh view model. I tried mapping directly using - 
PensionSchemeName = db.SchemeNames.Find(user.Pension.PensionSchemeNameID).Name,

but i get an error with that. Any ideas how I can include this?

A:

You can try to join Users and SchemeNames  manually:
public ViewResult Index(FormCollection form)
{
    string[] UserIDs = form["TRSIDs"].Split(',');

    var trs = db.Users
        .Join(db.SchemeNames,
            user => user.Pension.PensionSchemeNameID,
            schemeName => schemeName.SchemeNameID,
            (user, schemeName) => new { User = user, SchemeName = schemeName })
        .Where(a => UserIDs.Contains(a.User.UserName))
        .Select(a => new TRSViewModel
        {
            UserName = a.User.UserName,
            FirstName = a.User.UserDetails.FirstName,
            LastName = a.User.UserDetails.LastName,
            Salary = a.User.UserDetails.Salary,
            PensionSchemeName = a.SchemeName.Name
        }
    //...
}

I assume that Pension doesn't have a PensionSchemeName navigation property, otherwise it would be more straight-forward:
    var trs = db.Users
        .Where(u => UserIDs.Contains(u.UserName))
        .Select(u => new TRSViewModel
        {
            UserName = u.UserName,
            FirstName = u.UserDetails.FirstName,
            LastName = u.UserDetails.LastName,
            Salary = u.UserDetails.Salary,
            PensionSchemeName = u.Pension.PensionSchemeName.Name
        }

(I have removed the Includes. They are ignored anyway when you use a projection (Select).)

