我只想使用单个 LINQ 查询来填充 Entity(使用 EntityFramework) 中的嵌套列表。
我有 3 个表实体。头等Cities
包含List<Houses>
,Houses
包含List<Residents>
。
这些类:
class Cities
{
long CityId {get;set;}
string Name {get;set;}
List<House> Houses {get;set;}
}
class Houses
{
long CityId {get;set;}
string Address {get;set;}
List<Resident> Residents {get;set;}
}
class Residents
{
long HouseId {get;set;}
string FirstName {get;set;}
string LastName {get;set;}
}
我想要实现的是这样的:
var cities = ( from city in db.Cities
select new // Creating anonymous type to fill List of Houses
{
CityId = city.CityId,
Name = city.Name,
Houses = db.Houses.Where(h=>h.CityId == city.CityId)
.Select( new // Another anonymous type, but this time this isn't working
{
HouseId = h.HouseId,
Address = h.Address,
Residents = db.Residents.Where(r=>r.HouseId == h.HouseId).ToList()
}).ToList()
.Select( h => new Houses
{
HouseId = h.HouseId,
Address = h.Address,
Residents = h.Houses
}).ToList()
})
.ToList()
.Select( c=> new Cities
{
CityId = c.CityId
Name = c.Name,
Houses = c.Houses
}).ToList()
不幸的是,我收到错误The entity or complex type Houses cannot be constructed in a LINQ to Entities
.
它有效,仅适用于Houses = db.Houses.Where(h=>h.CityId ==city.CityId).ToList()
.但这样一来,我在Houses
中失去了Residents
.
甚至可以用一个 LINQ 查询来做吗?
您只需要将房屋和居民包含在城市查询中:
var cities = db.Cities.Include(c => c.Houses.Select(h => h.Residents)).ToList();
应使用导航属性,而不是单独的db
访问
var Cities = (from city in db.Cities
select new // Creating anonymous type to fill List of Houses
{
CityId = city.CityId,
Name = city.Name,
Houses = city.Houses.Select( new
{
HouseId = h.HouseId,
Address = h.Address,
Residents = h.Residents.ToList()
}).ToList()
.Select( h => new Houses
{
HouseId = h.HouseId,
Address = h.Address,
Residents = h.Houses
}).ToList()
})
.ToList()
.Select( c=> new Cities
{
CityId = c.CityId
Name = c.Name,
Houses = c.Houses
}).ToList()
没有检查整个语法,只用city.Houses
替换了db.Houses.Where(...)
(与Residents
相同),因此可能存在其他一些问题。