使用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屋!