title: Joining tables together via junction table in LINQ
body: <p>I was wondering how to accomplish the same as a navigation property, when using three tables in a many-to-many relationship.</p>
  
  <p>First I have some test code to get us starting:</p>
  
  <pre><code>var table1 = new[]
  {
      new { Title = "Title1", Id = 1 },
      new { Title = "Title2", Id = 2 },
      new { Title = "Title3", Id = 3 }
  };
  var table2 = new[]
  {
      new { GameId = 1, GenreId = 1 },
      new { GameId = 1, GenreId = 2 },
      new { GameId = 1, GenreId = 3 },
      new { GameId = 2, GenreId = 1 },
      new { GameId = 2, GenreId = 2 },
      new { GameId = 3, GenreId = 1 }
  };
  var table3 = new[]
  {
      new { Name = "Genre1", Id = 1 },
      new { Name = "Genre2", Id = 2 },
      new { Name = "Genre3", Id = 3 }
  };
  </code></pre>
  
  <p>"table1" contains some titles of say games for instance, "table3" contains some genres, and "table2" joins "table1" with "table3" by their ids.</p>
  
  <p>Now my question is how do you most elegantly select a list of games (table1), each with a list of their genres (table3) into a list of anonymous types?</p>
  
  <p>My solution so far is this:</p>
  
  <pre><code>var query =
      from t1 in table1
      select new
      {
          Game = t1,
          Genres =
          (
              from t2 in table2
              join t3 in table3 on t2.GenreId equals t3.Id
              where t2.GameId == t1.Id
              select t3
          ).ToList()
      };
  var result = query.ToList();
  </code></pre>
  
  <p>Ideally I would like to avoid sub-queries, but the question is whether it can be avoided in this situation...</p>
  
  <p>I was thinking something like this:</p>
  
  <pre><code>var query =
      from t1 in table1
      join t2 in table2 on t1.Id equals t2.GameId
      join t3 in table3 on t2.GenreId equals t3.Id
      select new { Game = t1, Genres = t3 };
  var result = query.ToList();
  </code></pre>
  
  <p>Which of course returns six items, which is not the result I want.</p>
  
  <p>So to sum up: Is it possible in LINQ to select a list games (table1), each with a list of their genres (table3) without using sub-queries?</p>