我有一个 LINQ 声明,我想弄清楚 - 我对此比较陌生,所以请原谅我的无知。我想返回一个人列表,每个人都有一个兴趣列表。
person(p) 表通过 p.id = pi.personid 连接到 personinterest(pi) 表
personinterest 表连接到 interest(i) 表,pi.interestid 到 i.id。
public class Persons
{
public int Id { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
...
public IList<Interest> PersonInterests { get; set; }
}
public class Interest
{
public string InterestName { get; set; }
}
我要返回的类是一个人,每个人都有其 PersonInterests 列表,其中填充了 0 到多个兴趣。我下面的 Linq 语句正在返回数据,但每个人的记录只获得一种兴趣,并且有不止一种兴趣的人正在复制他们的个人数据,如下 linq 语句所示
var interests = _db.Interests;
return (from p in _db.People
join i in _db.PersonInterests on p.Id equals i.PersonId
join s in _db.Interests on i.InterestId equals s.Id
select new Persons{
Id = p.Id,
FirstName = p.FirstName,
LastName = p.LastName,
Age = p.Age,
Address = p.Address,
City = p.City,
StateAbbrev = p.StateAbbrev,
ZipCode = p.ZipCode,
PersonInterests = (from r in interests where r.Id == i.InterestId select r).ToList()
}).ToList();
结果:
{"id":1,"lastName":"Alexander","firstName":"Carson","age":23,"address":"123 4th Street","city":"Jamestown","stateAbbrev":"NV","zipCode":"65465","personInterests":[{"id":1,"interestName":"Basketball"}],"photo":null}
{"id":1,"lastName":"Alexander","firstName":"Carson","age":23,"address":"123 4th Street","city":"Jamestown","stateAbbrev":"NV","zipCode":"65465","personInterests":[{"id":2,"interestName":"Camping"}],"photo":null},
而不是这样,我希望数据看起来像这样:
{"id":1,"lastName":"Alexander","firstName":"Carson","age":23,"address":"123 4th Street","city":"Jamestown","stateAbbrev":"NV","zipCode":"65465","personInterests":[{"id":1,"interestName":"Basketball"}, {"id":2,"interestName":"Camping"}],"photo":null}
我已经为此苦苦挣扎了一段时间,非常感谢任何帮助。
所以,在盯着这个看了几个小时之后,我意识到在一个查询中尝试这样做是愚蠢的。我把它分开并让它工作。首先检索人员数据,然后为每个人建立他们感兴趣的列表。
public async Task<IEnumerable<PersonResource>> GetPeople()
{
IEnumerable<PersonResource> people = await (from p in _db.People
select new PersonResource
{
Id = p.Id,
FirstName = p.FirstName,
LastName = p.LastName,
Age = p.Age,
Address = p.Address,
City = p.City,
StateAbbrev = p.StateAbbrev,
ZipCode = p.ZipCode,
Photo = p.Photo,
Interests = new List<string>()
}).ToListAsync();
foreach (PersonResource person in people)
{
person.Interests = (from iint in _db.Interests
join n in _db.PersonInterests on iint.Id equals n.InterestId
where n.PersonId == person.Id
select iint.InterestName).ToList();
}
return people;
// return Mapper.Map<List<Person>, List<PersonResource>>(people);
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句