c# - Joining tables together via junction table in LINQ -

c# - Joining tables together via junction table in LINQ -

i wondering how accomplish same navigation property, when using 3 tables in many-to-many relationship.

first have test code starting:

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 } };

"table1" contains titles of games instance, "table3" contains genres, , "table2" joins "table1" "table3" ids.

now question how elegantly select list of games (table1), each list of genres (table3) list of anonymous types?

my solution far this:

var query = t1 in table1 select new { game = t1, genres = ( t2 in table2 bring together t3 in table3 on t2.genreid equals t3.id t2.gameid == t1.id select t3 ).tolist() }; var result = query.tolist();

ideally avoid sub-queries, question whether can avoided in situation...

i thinking this:

var query = t1 in table1 bring together t2 in table2 on t1.id equals t2.gameid bring together t3 in table3 on t2.genreid equals t3.id select new { game = t1, genres = t3 }; var result = query.tolist();

which of course of study returns 6 items, not result want.

so sum up: possible in linq select list games (table1), each list of genres (table3) without using sub-queries?

maybe want combine sec query group by:

var query = t1 in table1 bring together t2 in table2 on t1.id equals t2.gameid bring together t3 in table3 on t2.genreid equals t3.id grouping t3 t1 g select new { game = g.key.title, genres = g.select (x => x.name) };

(note: should obvious, used select new { game = g.key.title, genres = g.select (x => x.name) } illustration purpose. select actual types, utilize select new { game = g.key, genres = g })

c# linq