使用select（），include（）和where（）不起作用的dbcontext加载相关实体 [英] dbcontext loading related entities using select(), include() and where() not working
问题描述
我在实体之间有如下关系。
公司1 --- *约会* --- 1员工
I have the following relationship between the entities. Company 1 ---* Appointments *---1 Employee
我有一个单独的数据库中的.net asp成员资格。无论何时创建用户，都可以将其分配给公司，员工或管理员角色。
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.
在我的公司控制器的索引操作中，我检查登录的用户角色。基于角色，我做出不同的linq查询。例如，管理员可以获得所有公司的列表，公司可以获得具有与User.Identity.Name相同的用户名属性（字符串）的公司列表。对于管理员和公司角色，它都可以正常工作。
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.
对于员工角色，我想加载与当前员工相关的所有公司。我很难写出一个这样做的linq查询。
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.
我试过
var companies = db.Companies.Include(c => c.Appointments.Select(a=>a.Employee).Where(e=>e.Username.ToLower() == this.User.Identity.Name.ToLower())).ToList();
我收到此错误
Include路径表达式必须引用导航属性
参数名称：路径
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"
这里是源代码，
CompanyController
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);
}
...
模型
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;
}
}
}
推荐答案
如果您想获得有选定职员的预约公司，则不需要使用 Include 。包括指示EF加载与公司相关的所有约会（和它不支持过滤）。尝试这样：
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();
这篇关于使用select（），include（）和where（）不起作用的dbcontext加载相关实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
