I have a LINQ statement I am trying to figure out - I am relatively new to this so please excuse my ignorance. I want to return a person list, each person having a list of interests.
The person(p) table joins to the personinterest(pi) table by p.id = pi.personid
The personinterest table joins to the interest(i) table, pi.interestid to 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; }
}
The class I am returning is a person, each with its PersonInterests list populated with 0 to many interests. The Linq statement I have below is returning data, but each person record is only getting one interest, and persons with more than one interest are having their person data duplicated as shown below the linq statement
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();
Results:
{"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},
Instead of this, I would like the data to look like this:
{"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}
I have struggled with this for a while, any help is greatly appreciated.
So, after staring at this for hours I realized it was stupid to try and do this in one query. I split it up and got it working. First retrieved the person data, then for each person, built their list if interests.
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);
}