title: Linq query and group by data from table which has one to many relationship?
body: <p>i have 3 tables,
   1 is user table which store all user data, another one is application table, the last is user education table,
  the relationship between these tables :</p>
  
  <p><code>an user can have many applications, and also can have many education background</code></p>
  
  <p>the problem
  here is i want to select data for monthly report from application table and i want to group by my data by each education level, based on each user highest education, like this :</p>
  
  <pre><code>user A - high school
  user A - bachelor degree
  user A - master degree
  user B - high school
  user C - bachelor degree
  user D - high school
  </code></pre>
  
  <p>will result : </p>
  
  <pre><code>master degree : total 1
  bachelor degree : total 1
  high school : total 2
  </code></pre>
  
  <p>i have tried this :</p>
  
  <pre><code>var edu = CTX.user_applications.Where(a =&gt; a.applied_date &gt;= new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)
                                                          &amp;&amp; a.applied_date &lt;= DateTime.Now
                                                        ).GroupBy(g =&gt; (g.user_list.user_edus.OrderByDescending(o =&gt; o.edu_lvl_id).FirstOrDefault()))
                                                             .Select(x =&gt; new ReportObject
                                                             {
                                                                 Key = x.Key.ToString(),
                                                                 Count = x.Count().ToString()
                                                             }).ToList();
  </code></pre>
  
  <p>error :</p>
  
  <pre><code>A group by expression can only contain non-constant scalars that are comparable by the server. The expression with type 'System.Data.Linq.EntitySet`1[OTOKarir.Models.user_edu]' is not comparable
  </code></pre>
  
  <p>it does not work, it give me an error, there's must some trick to do it correctly, btw i am not really sure that it will give me the desired result, i am stug with this, need some guidance, any idea will be very great? thx.</p>
  
  <p><img src="https://i.stack.imgur.com/7DHyi.jpg" alt="application table and education table both have foreign key from table user list"></p>