dbcontext loading related entities using select(), include() and where() not working

Question

I have the following relationship between the entities. Company 1 ---* Appointments *---1 Employee

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.

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.

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.

i tried

var companies = db.Companies.Include(c => c.Appointments.Select(a=>a.Employee).Where(e=>e.Username.ToLower() == this.User.Identity.Name.ToLower())).ToList();

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"

Here are the source code,

CompanyController

[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 => c.Username.ToLower().Equals(this.User.Identity.Name.ToLower())).ToList() };
        }
        else if (Roles.IsUserInRole("employees")) {
            var companies = db.Companies.Include(c => c.Appointments.Select(a=>a.Employee).Where(e=>e.Username.ToLower() == this.User.Identity.Name.ToLower())).ToList();
            viewModel = new CompanyIndexViewModel { Companies = companies.ToList() };
        }

        return View(viewModel);
    }

...

Models

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<Contact> Contacts { get; set; }

        public virtual ICollection<Appointment> 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<AppointmentLine> AppointmentLines { get; set; }

        public Appointment() {
            //this.AppointmentLines = new List<AppointmentLine>();
            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<Appointment> Appointments { get; set; }

        public Employee() {
            this.DateHired = DateTime.Now;        
        }
    }
}

Solution

If you want to get companies which have appointment with selected employee you don't need to use Include. Include is for instructing EF to load all appointments related to the company (and it doesn't support filtering). Try this:

string userName = this.User.Identity.Name.ToLower();
var companies = db.Companies.Where(c => c.Appointments.Any(a => 
                   a.Employee.Username.ToLower() == userName)).ToList();

Comments

[Thupten]

It did not work. It throws ArgumentException.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

[Boolean Operation]

Interesting. The parentheses was for sure in the wrong place. Have you tried to just use the old string include method? Something like: db.Companies.Include("Appointments.Employee").Where(e=>e.Use‌​rname.ToLower() == this.User.Identity.Name.ToLower()).ToList(); See my edited answer.

[Thupten]

thanks @boolean operation ...that statement might work but i am limited by my liniq knowledge. db.Companies.Include("Appointments.Employee") gives me Company Objects so the Where method doesn't have employee. e=>e.Username... becomes an instance of Company. c=>c.Appointments.Employee???? I think I am back to where I started. ie Company object.

[Thupten]

The relationship between the entities are like so.. Company to Appointment: One to Many Employee to Appointment: One to Many I need to get Company or Companies which is related to Appointments which in turn is/are related to the Employee with certain Username.

[Ram]

alternative fix possible at stackoverflow.com/questions/16798796/…