title: How to group by multiple columns with complex types in Linq
body: <p>I'm trying to preform a group by to get all of the students, by college, grouped by status. The problem is the database I'm working with has hundreds of fields in the StudentInfo table, which is why I think this is failing.</p>
  
  <p>I'm basically trying to get counts for the students by their statuses, and grouped by the college they are from.</p>
  
  <p>When I execute this query, I get the following exception.</p>
  
  <p><strong>The key selector type for the call to the 'GroupBy' method is not comparable in the underlying store provider.</strong></p>
  
  <p>Now I believe this is because there are way too many fields on the StudentInfo table, but I can't do anything about that in this case. There has to be a way to do what I want.</p>
  
  <p>I need the following fields returned, all of which are counts, except College.</p>
  
  <p><strong>College | Accepts | Webapps | Ays | Total</strong></p>
  
  <p>Can someone please point me in the right direction. </p>
  
  <p>Here is my model</p>
  
  <pre><code>public class EnrollmentCountsBySchool
  {
      // selected terms
      public int[] SelectedTerms { get; set; }
      // list of terms
      public IEnumerable&lt;SelectListItem&gt; Terms { get; set; }
  
      // selected programs
      public int[] SelectedPrograms { get; set; }
      // list of programs
      public IEnumerable&lt;SelectListItem&gt; Programs { get; set; }
  
      // selected statuses
      public string[] SelectedStatuses { get; set; }
      // list of statuses
      public IEnumerable&lt;SelectListItem&gt; Statuses { get; set; }
  
      // list of results
      public List&lt;EnrollmentCountsBySchoolResult&gt; Results { get; set; }
  </code></pre>
  
  <p>My Query (way to long, I know.)</p>
  
  <pre><code> var results =
              (from student in db.StudentInfo
               join college in db.Colleges on student.College.Id equals college.Id
               where model.SelectedTerms.Contains(student.TermOfInterestId.Value) &amp;&amp;
                     model.SelectedPrograms.Contains(student.ProgramId.Value) &amp;&amp;
                     model.SelectedStatuses.Contains(student.Status)
  
               group student by new
               {
                   College = college.Name,
                   Accepts = (
                           from s in db.StudentInfo
                           join c in db.Colleges on student.College.Id equals college.Id
                           where model.SelectedTerms.Contains(student.TermOfInterestId.Value) &amp;&amp;
                                 model.SelectedPrograms.Contains(student.ProgramId.Value) &amp;&amp;
                                 model.SelectedStatuses.Contains(student.Status) &amp;&amp;
                                 student.Status.ToLower().Contains("accepted") ||
                                 student.Status.ToLower().Contains("applicant")
                           group s by new
                           {
                               College = c.Name,
                           } into accepts
                           select new
                           {
                               accepts.Key.College,
                               Count = accepts.Count()
                           }
                   ),
                   Webapps =
                   (
                           from s in db.StudentInfo
                           join c in db.Colleges on student.College.Id equals college.Id
                           where model.SelectedTerms.Contains(student.TermOfInterestId.Value) &amp;&amp;
                                 model.SelectedPrograms.Contains(student.ProgramId.Value) &amp;&amp;
                                 model.SelectedStatuses.Contains(student.Status) &amp;&amp;
                                 student.Status.ToLower().Contains("webapp")
                           group s by new
                           {
                               College = c.Name,
                           } into webapps
                           select new
                           {
                               webapps.Key.College,
                               Count = webapps.Count()
                           }
                   ),
                   Ays = (
                           from s in db.StudentInfo
                           join c in db.Colleges on student.College.Id equals college.Id
                           where model.SelectedTerms.Contains(student.TermOfInterestId.Value) &amp;&amp;
                                 model.SelectedPrograms.Contains(student.ProgramId.Value) &amp;&amp;
                                 model.SelectedStatuses.Contains(student.Status) &amp;&amp;
                                 !student.Status.ToLower().Contains("accepted") &amp;&amp;
                                 !student.Status.ToLower().Contains("applicant") &amp;&amp;
                                 !student.Status.ToLower().Contains("webapp")
                           group s by new
                           {
                               College = c.Name,
                           } into ays
                           select new
                           {
                               ays.Key.College,
                               Count = ays.Count()
                           }
                   ),
                   Total = (
                           from s in db.StudentInfo
                           join c in db.Colleges on student.College.Id equals college.Id
                           where model.SelectedTerms.Contains(student.TermOfInterestId.Value) &amp;&amp;
                                 model.SelectedPrograms.Contains(student.ProgramId.Value) &amp;&amp;
                                 model.SelectedStatuses.Contains(student.Status)
                           group s by new
                           {
                               College = c.Name,
                           } into totals
                           select new
                           {
                               totals.Key.College,
                               Count = totals.Count()
                           }
                   )
  
               }
                   into grouping
                   select new EnrollmentCountsBySchoolResult
                   {
                       College = grouping.Key.College,
                       Accepts = grouping.Key.Accepts.Count(),
                       Webapps = grouping.Key.Webapps.Count(),
                       Ays = grouping.Key.Ays.Count(),
                       Total = grouping.Key.Total.Count()
                   }).ToList();
  </code></pre>