我正试图在LINQ中获取特定状态下的Employees计数。
我有这样的东西：
States
|
Cities
|
Posts
|
Employees
如何通过手中选定的State获得Employees计数？
我的实体是：
public class Province : EntityBase
{
public String ProvinceName { get; set; }
public virtual IList<City> Cities { get; set; }
}
public class City : EntityBase
{
public String CityName { get; set; }
public virtual Province Province { get; set; }
public virtual IList<Post> ElectricPosts { get; set; }
}
public class Post : EntityBase
{
public String PostName { get; set; }
public virtual City City { get; set; }
public virtual IList<Employee> Employees { get; set; }
}
public class Employee : Person
{
public virtual String FirstName { get; set; }
public virtual String SureName { get; set; }
public virtual Post ElectricPost { get; set; }
}
编辑：有趣的是，我可以毫无问题地获得Posts的计数，但当我想在@HamletHakobyan帖子中尝试这种方法时，我得到了NullReferenceException，我不知道为什么？
我不确定，但试试这个：
var state = ((IObjectContextAdapter)context)
.ObjectContext
.CreateObjectSet<Province>("States") as ObjectQuery<Province>;
// States is entitySetName
int count = state.Cities.Include("ElectricPosts.Employees")
.SelectMany(c => c.Posts)
.SelectMany(p => p.Employees)
.Count();
您可以使用LINQ to Entities Sum和Count方法进行以下操作：
int count = db.States
.Where( state => state.StateId == X )
.Sum( state => state.Cities
.Sum( city => city.Posts
.Sum( post.Employees.Count() )
)
)
;
var q = from emp in dc.Employees
from post in dc.Posts
from city in dc.Cities
from state in dc.States
where emp.PostID == post.ID
&& post.CityID == city.ID
&& city.StateID == state.ID
&& state.Name == "Tehran"
select emp;
或简称
var q = from emp in dc.Employees
where emp.Post.City.State.Name == "Tehran"
q是该州的员工列表，现在您可以轻松地统计他们(我相信您知道如何统计他们(。
如果我理解正确，这将是我解决您问题的方法因为我没能找到一种方法把4林克粘在一起，但也许你能做到这一点
顺便说一下，如果你需要可选的，在哪里看这个链接
var list = new List<Province>();
string sProvinceName = "";
string sCityName = "";
string sPostName = "";
string sEmployeeFirstName = "";
string sEmployeeSureName = "";
var iListListcitys = from province in list
where province != null
where province.Cities != null
where province.ProvinceName == sProvinceName
select province.Cities;
var iListListposts = from icitys in iListListcitys
from city in icitys
where city != null
where city.ElectricPosts != null
where city.CityName == sCityName
select city.ElectricPosts;
var iListListEmployees = from posts in iListListposts
from post in posts
where post != null
where post.Employees != null
where post.PostName == sPostName
select post.Employees;
var listEmployees = from employees in iListListEmployees
from employee in employees
where employee != null
where employee.FirstName == sEmployeeFirstName || employee.SureName == sEmployeeSureName
select employee;
var count = listEmployees.Count();
我也遇到了同样的问题，但不幸的是，其他人在互联网上找到的解决方案是错误的！所以我试图解决这个问题。所以，假设我们有一些国家，每个国家都有一些州，每个州都有一些城市，我们想得到一些国家视图模型，这些模型有每个国家的Id和名称，以及所有州和所有城市属于每个国家的计数。解决方案如下：
var varResult =
OurDatabaseContext.Countries
.Select(current => new {
Id = current.Id,
Name = current.Name,
StateCount = current.States.Count,
CityCount = current.States.Count == 0 ? 0 :
current.States.Select(state =>
new { CityCount = state.Cities.Count }).Sum(q => q.CityCount)
};
db.States.Where(p => p.yourcondition == whatever).Cities.Posts.Employees.Count()
试试这个：
Province province = ...;
int count = province.Cities.Include(c => c.Posts.Select(p => p.Employees))
.Where(c => c != null)
.SelectMany(c => c.Posts)
.Where(p => p != null)
.SelectMany(p => p.Employees)
.Where(e => e != null)
.Count();
然后逐一删除.Where子句，找出问题所在。
您也可以使用join s:
int count = (from c in province.Cities
join p in db.Posts on c equals p.City
join e in db.Employees on p equals e.ElectricPost
select e).Count();
var cites = (from s in _db.Province where s.ProvinceName == 'name' select s.Cities);
int count = (from p in _db.Posts
where city.Contains(p.City)
select p.Employees).Count();