title: Using LINQ to group on ID, EmailListID, PhoneListID, etc
body: <ul>
  <li>I'm using Dapper to map my database content to <code>EmployeeModel</code> objects.</li>
  <li>The mapping of properties is working, but the grouping at the end is still giving me trouble:
  <ul>
  <li><code>EmployeeModel</code> takes <code>List&lt;PhoneModel&gt;</code> and <code>List&lt;EmployeeModel&gt;</code> as properties.</li>
  <li>Items are grouped according to <code>EmployeeID</code>, however multiple email and phone results are returned as I haven't been able to find the syntax for doing so.</li>
  <li>I've tried looping through the <code>EmployeeIDs</code> in the <code>employeeList</code> after it's been grouped by <code>ID</code>, before it's been grouped by <code>ID</code>, and while it's being grouped by <code>ID</code>.</li>
  </ul>
  </li>
  </ul>
  <pre class="lang-cs prettyprint-override"><code>var sql = @&quot;
  
  SELECT
      e.id,
      e.FirstName, e.LastName, e.Nickname, 
      em.id as ID, em.Address, em.Type, 
      jt.id as ID, jt.Name, 
      e.id as ID, p.Number, p.Type,
      d.id as ID, d.Name,
      es.id as ID, es.Name 
  
  FROM
      dbo.Employees e 
      LEFT JOIN dbo.Emails         em ON em.EmployeeID  = e.id
      LEFT JOIN dbo.JobTitles      jt ON e.JobTitleID   = jt.id
      LEFT JOIN Phones             p  ON p.EmployeeID   = e.id
      LEFT JOIN dbo.Departments    d  ON e.DepartmentID = d.id
      LEFT JOIN dbo.EmployeeStatus es ON e.StatusID     = es.id
  &quot;;
  
  IEnumerable&lt;EmailModel&gt; emailsGrouped = new List&lt;EmailModel&gt;();
  
  var employees = await connection
      .QueryAsync&lt;
          EmployeeModel,EmailModel,TitleModel,
          PhoneModel,DepartmentModel,StatusModel,
          EmployeeModel&gt;
      (
          sql,
          ( e, em, t, p, d, s ) =&gt;
          {
              e.EmailList.Add(em);
              e.JobTitle = t;
              e.PhoneList.Add(p);
              e.Department = d;
              e.Status = s;
              return e;
         },
          splitOn: &quot;ID, ID, ID, ID, ID&quot;
      );
  
  
  foreach (EmployeeModel emod in employees)
  {
      emod.EmailList.GroupBy(em =&gt; em.ID);
  }
              
  var result = employees
      .GroupBy(e =&gt; e.ID)
      .Select(g =&gt;
      {
          var groupedEmployee = g.First();
          groupedEmployee.EmailList = g.Select(e =&gt; e.EmailList.Single()).ToList();
          groupedEmployee.PhoneList = g.Select(e =&gt; e.PhoneList.Single()).ToList();
      
          return groupedEmployee;
      });
  
  return result.ToList();
  </code></pre>
  <p>Here is my Email definition, as requested.  It's inside my EmployeeClass, so I've posted the whole thing.</p>
  <pre><code>public class EmployeeModel
      {
          public int ID { get; set; }
          public string LastName { get; set; }
          public string FirstName { get; set; }
          public string Nickname { get; set; }
          public DepartmentModel Department { get; set; }
          public TitleModel JobTitle { get; set; }
          public DateTime HireDate { get; set; }
          public StatusModel Status { get; set; }
          public List&lt;EmailModel&gt; EmailList { get; set; } = new List&lt;EmailModel&gt;();
          public List&lt;PhoneModel&gt; PhoneList { get; set; } = new List&lt;PhoneModel&gt;();
          public List&lt;RestrictionModel&gt; RestrictionsList { get; set; } = new List&lt;RestrictionModel&gt;();
          public List&lt;CitationModel&gt; CitationsList { get; set; } = new List&lt;CitationModel&gt;();
          public List&lt;CertificationModel&gt; CertificationList { get; set; } = new List&lt;CertificationModel&gt;();
  
          public string ListView
          {
              get
              {
                  return $&quot;{LastName}, {FirstName}&quot;;
              }
          }
          public string ToEmailString()
          {
              IEnumerable&lt;string&gt; employeeEmailStrings = EmailList.Select(emmod =&gt; emmod.ToString());
              string employeeEmailString = string.Join($&quot;{Environment.NewLine}&quot;, employeeEmailStrings);
              return $&quot;{FirstName}, {LastName}: {Environment.NewLine} -{JobTitle.Name}- {Environment.NewLine}&quot;;
          }
  
          //IEnumerable&lt;string&gt; phoneStrings = PhoneList.Select(plistmod =&gt; plistmod.ToString());
          //string phoneString = string.Join($&quot;{Environment.NewLine}&quot;, phoneStrings);
  
  
  
          public string ToCertificationString()
          {
              IEnumerable&lt;string&gt; certificationStrings = CertificationList.Select(clistmod =&gt; clistmod.ToString());
              string certificationString = string.Join($&quot;{Environment.NewLine}&quot;, certificationStrings);
              return certificationString;
          }
          public class EmailModel
          {
              public int ID { get; set; }
              public string Address { get; set; }
              public string Type { get; set; }
  
              public override string ToString()
              {
                  return $&quot;{Address} ({Type})&quot;;
              }
          }
          public class PhoneModel
          {
              public int ID { get; set; }
              public string Number { get; set; }
              public string Type { get; set; }
              public override string ToString()
              {
                  return $&quot;{Number} ({Type})&quot;;
              }
          }
      }
  }
  </code></pre>
  <p>What I'm trying now is to loop through the Emails in the <code>EmployeeModel</code> to create a new list of emails, and then set that new list as the <code>EmployeeModel.List&lt;EmailModel&gt;</code>.</p>