Thursday, March 24, 2011

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

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
From stackoverflow
  • var result = myList
      .GroupBy(x => x.Id)
      .Select(g => new Parent()
      {
        Key = g.Key,
        Children = g.Select(x => x.SomeString).ToList()
      });
    
  • 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.

    Schotime : Seems to work. Will post a full example soon.
  • 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!!

    David B : Glad it works for you. I checked out your blog post and that is quality code.

0 comments:

Post a Comment