Transforming a one to many sql query into a List of nested classes

Question

What is the best way of taking a query and transforming it into a nested class list without doing a subselect for each row?

eg.

1   aaaa
1   bbbb
1   cccc
2   dddd
3   eeee

into

1   
    aaaa
    bbbb
    cccc
2   
    dddd
3   
    eeee

Answer 1

var result = myList
  .GroupBy(x => x.Id)
  .Select(g => new Parent()
  {
    Key = g.Key,
    Children = g.Select(x => x.SomeString).ToList()
  });

Answer 2

Just do a join between the parent and child tables, and iterate through the rows, creating a new outer class when the fields of interest change.

The data transfer overhead is negligible. What's more important is clear simple code other people can read easily.

If your entire peer group is adept at LINQ, then David's response is pretty elegant. I'm impressed. I can't tell you if it's correct, however.

Answer 3

See http://schotime.net/blog/index.php/2009/01/22/transforming-one-to-many-sql-into-nested-class/

for a full solution to the problem.

Thanks David B!!