title: dbcontext loading related entities using select(), include() and where() not working
body: <p>I have the following relationship between the entities.
  Company 1 ---* Appointments *---1 Employee</p>
  
  <p>I have the .net asp membership in a separate database. Whenever a user is created it can be assigned to companies, employees, or administrators roles.</p>
  
  <p>in the Index action of my Company Controller, I check the logged in user's role. Based on the role, I make different linq query. For example, administrators can get list of all companies, companies can get list of company which has a username property (string) same as the User.Identity.Name. For both of administrators and companies role, it is working fine.</p>
  
  <p>For the employees role, I want to load all the companies that are related to the current employee. I am having hard time to compose a linq query that does this job.</p>
  
  <p>i tried</p>
  
  <pre><code>var companies = db.Companies.Include(c =&gt; c.Appointments.Select(a=&gt;a.Employee).Where(e=&gt;e.Username.ToLower() == this.User.Identity.Name.ToLower())).ToList();
  </code></pre>
  
  <p>to which i get this error
  "The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.
  Parameter name: path"</p>
  
  <p>Here are the source code,</p>
  
  <p>CompanyController</p>
  
  <pre><code>[Authorize]
  public class CompanyController : Controller
  {
      private MyDBContext db = new MyDBContext();
  
      //
      // GET: /Company/
  
      public ViewResult Index()
      {
          var viewModel = new CompanyIndexViewModel();
          if (Roles.IsUserInRole("administrators")) {
              viewModel = new CompanyIndexViewModel { Companies = db.Companies.ToList() };
          }
          else if (Roles.IsUserInRole("companies")) {
              viewModel = new CompanyIndexViewModel { Companies = db.Companies.Where(c =&gt; c.Username.ToLower().Equals(this.User.Identity.Name.ToLower())).ToList() };
          }
          else if (Roles.IsUserInRole("employees")) {
              var companies = db.Companies.Include(c =&gt; c.Appointments.Select(a=&gt;a.Employee).Where(e=&gt;e.Username.ToLower() == this.User.Identity.Name.ToLower())).ToList();
              viewModel = new CompanyIndexViewModel { Companies = companies.ToList() };
          }
  
          return View(viewModel);
      }
  </code></pre>
  
  <p>...</p>
  
  <p>Models</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.ComponentModel.DataAnnotations;
  namespace TorontoWorkforce.Models
  {
      public class Company
      {
          public int CompanyId { get; set; }
  
          [Required]
          public string Username { get; set; }
  
          [Display(Name="Company Name")]
          [Required]
          public string Name { get; set; }
  
  
          [UIHint("PhoneNumber")]
          public string Phone { get; set; }
  
          [DataType(DataType.Url)]
          public string Website { get; set; }
  
          [DataType(DataType.EmailAddress)]
          public string Email { get; set; }
  
          public AddressInfo AddressInfo { get; set; }
  
          public virtual ICollection&lt;Contact&gt; Contacts { get; set; }
  
          public virtual ICollection&lt;Appointment&gt; Appointments { get; set; }
  
          public Company(){
              this.AddressInfo = new AddressInfo();
          }
      }
  }
  
  
  
  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.ComponentModel.DataAnnotations;
  
  namespace TorontoWorkforce.Models
  {
      public class Appointment
      {
  
          public int AppointmentId { get; set; }
  
  
          [Required]
          [UIHint("DateTime")]
          [Display(Name="Appointment Date")]
          public DateTime? DateOfAppointment { get; set; }
  
          [Required]
          public int CompanyId { get; set; }
  
          [Required]
          public int EmployeeId { get; set; }
  
          [Required]
          [UIHint("MultilineText")]
          [Display(Name = "Appointment Summary")]
          public string Description { get; set; }
  
          [Display(Name="Allocated No of Hours")]
          public decimal NoOfHoursWorked { get; set; }
  
          public virtual Company Company { get; set; }
  
          public virtual Employee Employee { get; set; }
  
          public virtual ICollection&lt;AppointmentLine&gt; AppointmentLines { get; set; }
  
          public Appointment() {
              //this.AppointmentLines = new List&lt;AppointmentLine&gt;();
              this.DateOfAppointment = DateTime.Now;
          }
  
          [NotMapped]
          [Display(Name="Actual No of Hours")]
          public decimal ActualHoursWorked {
              get
              {
                  decimal total = 0;
                  foreach (var jobline in this.AppointmentLines)
                  {
                      total = total + jobline.TimeSpent;
                  }
                  return total;
              } 
          }
  
  
      }
  
      public class AppointmentLine
      {
          public int AppointmentLineId { get; set; }
  
          [UIHint("MultilineText")]
          [Required]
          public string Description { get; set; }
  
          [Display(Name="Time Spent")]
          [DataType(DataType.Duration)]
          public decimal TimeSpent { get; set; }
  
          public int AppointmentId { get; set; }
  
          public virtual Appointment Appointment { get; set; }
      }
  }
  
  
  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.ComponentModel.DataAnnotations;
  
  namespace TorontoWorkforce.Models
  {
      public class Employee: TorontoWorkforce.Models.Person
      {
          public int EmployeeId { get; set; }
  
          [Required]
          public string Username { get; set; }
  
          [Display(Name="Date Hired")]
          public DateTime? DateHired { get; set; }
  
          [Required]
          public string Position { get; set; }
  
          public virtual ICollection&lt;Appointment&gt; Appointments { get; set; }
  
          public Employee() {
              this.DateHired = DateTime.Now;        
          }
      }
  }
  </code></pre>