title: Joining multiple tables to return a list using LINQ
body: <p>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.</p>
  
  <p>The person(p) table joins to the personinterest(pi) table by p.id = pi.personid </p>
  
  <p>The personinterest table joins to the interest(i) table, pi.interestid to i.id.</p>
  
  <pre><code>    public class Persons
      {
          public int Id { get; set; }
          public string LastName { get; set; }
          public string FirstName { get; set; }
  ...
          public IList&lt;Interest&gt; PersonInterests { get; set; }
      }
  
      public class Interest
      {
          public string InterestName { get; set; }
      }
  
  </code></pre>
  
  <p>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</p>
  
  <pre><code>            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();
  
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>{"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},
  </code></pre>
  
  <p>Instead of this, I would like the data to look like this:</p>
  
  <pre><code>{"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}
  </code></pre>
  
  <p>I have struggled with this for a while, any help is greatly appreciated.</p>