title: ADO.NET build complex objects using DataReader
body: <p>I'm trying to create a way to make an unique search into the database and build the right object for my needs. I mean, I use a SQL query that returns me a lot of rows and then I build the collections based on that database rows. E.g.:</p>
  
  <p>We have a table called <code>People</code> and another table called <code>Phones</code>.</p>
  
  <p>Let's suppose that this is my SQL query and will return the following below:</p>
  
  <pre><code>SELECT 
     P.[Id], P.[Name], PH.[PhoneNumber]
  FROM
     [dbo].[People] P
  INNER JOIN 
     [dbo].[Phones] PH ON PH.[Person] = P.[Id]
  </code></pre>
  
  <p>And that's the results returned:</p>
  
  <pre><code>1   NICOLAS    (123)123-1234
  1   NICOLAS    (235)235-2356
  </code></pre>
  
  <p>So, my class will be:</p>
  
  <pre><code>public interface IModel {
      void CastFromReader(IDataReader reader);
  }
  
  public class PhoneModel : IModel {
      public string PhoneNumber { get; set; }
  
      public PhoneModel() {  }
      public PhoneModel(IDataReader reader) : this() {
           CastFromReader(reader);
      }
  
      public void CastFromReader(IDataReader reader) {
          PhoneNumber = (string) reader["PhoneNumber"];
      }
  }
  
  public class PersonModel : IModel {
      public int Id { get; set; }
      public string Name { get; set; }
      public IList&lt;PhoneModel&gt; Phones { get; set; }
  
      public PersonModel() {  
          Phones = new List&lt;PhoneModel&gt;();
      }
  
      public PersonModel(IDataReader reader) : this() {
           CastFromReader(reader);
      }
  
      public void CastFromReader(IDataReader reader) {
          Id = Convert.ToInt32(reader["Id"]);
          Name = (string) reader["Name"];
  
          var phone = new PhoneModel();
          phone.CastFromReader(reader);
          Phones.Add(phone);
  
          // or
          Phones.Add(new PhoneModel {
              PhoneNumber = (string) reader["PhomeNumber"]
          });
      }
  }
  </code></pre>
  
  <p>This code will generate a <code>PersonModel</code> object with two phone numbers. That's good so far. </p>
  
  <p>However, I'm struggling to make some good way to deal when I want to manage more tables with this process.</p>
  
  <p>Let's suppose, then, I have a new table called <code>Appointments</code>. It stores the user's appointments to the schedule.</p>
  
  <p>So, adding this table to the query, the result will be:</p>
  
  <pre><code>1   NICOLAS   (123)123-1234    17/09/2014
  1   NICOLAS   (123)123-1234    19/09/2014
  1   NICOLAS   (123)123-1234    27/09/2014
  1   NICOLAS   (235)235-2356    17/09/2014
  1   NICOLAS   (235)235-2356    19/09/2014
  1   NICOLAS   (235)235-2356    17/09/2014
  </code></pre>
  
  <p>As you guys can see, the problem is to manage the phones and the appointments this way. Do you can think in anything that could solve this issue? </p>
  
  <p>Thank you all for the opinions!</p>