title: Reading multiple child objects from SQL Server
body: <p>I have the need to load in a large object with all its children from my SQL Server database.</p>
  
  <p>I plumped for using <code>FOR XML</code> in the queries and then using an <code>XmlReader</code> before deserialising the lot into the classes needed much like this:</p>
  
  <pre><code>SELECT  
      MyClass.* ,
      (SELECT    
           ChildClass1.[ID],
           ChildClass1.[Description]
       FROM      
           [dbo].[ChildClass1Table] ChildClass1
       JOIN 
           dbo.LinkyTable lt ON lt.ChildClass1ID = ChildClass1.ID
       WHERE     
           lt.CID = MyClass.ID
       FOR XML AUTO, ROOT('ChildClass1'), TYPE, ELEMENTS),
      (SELECT    
           ChildClass2.[ID], ChildClass2.[Description]
       FROM      
           [dbo].[ChildClass1Table] ChildClass2
       JOIN 
           dbo.LinkyTable2 lt2 ON lt.ChildClass2ID = ChildClass2.ID
       WHERE     
           lt2.CID = MyClass.ID
       FOR XML AUTO, ROOT('ChildClass2'), TYPE, ELEMENTS) 
  FROM 
      ...etc - for a fair few more
  </code></pre>
  
  <p>C# code:</p>
  
  <pre><code>using (System.Xml.XmlReader xmlr = cmd.ExecuteXmlReader())
  {
          if (xmlr.Read())
          {
              string xml = string.Empty;
  
              while (xmlr.ReadState != System.Xml.ReadState.EndOfFile)
              {
                  xml = xmlr.ReadOuterXml();
              }
  
              var serializer = new XmlSerializer(typeof(MyClass));
  
              using (var stream = new StringReader(xml))
              using (var reader = XmlReader.Create(stream))
              {
                  MyClass b = (MyClass)serializer.Deserialize(reader);
                  return b;
              }
          }
      }
  </code></pre>
  
  <p>This works well, and I really have no issue with it - other than I ideally would like to use the repository pattern to grab all my objects from the DB and would rather use the <code>IDbConnection</code> / <code>IDbCommand</code> rather than the concrete SQL Server classes which are necessary to use the <code>XmlReader</code>.</p>
  
  <p>My question is, is there another way that these child objects can be loaded in (without making multiple round trips to the DB for each child (and grandchild class)) using standard readers ?</p>
  
  <p>Thanks</p>