title: C# Linq - EF, select grouped data having the max value
body: <p>I have a many to many table where I store UserId, SectionId, Attempt, Qualification and timestamps. So, the user can have N Attempts by Section but when I evaluate every section only need to take where the Attempt is the max value.
  I tried make a join with the keys UserId and SectionId ordering desc by Attempt</p>
  <pre><code> var result = await (from exam in db.exams
                              join section in db.sections on exam.SectionId equals section.Id
                              join groupedTable in (from exam2 in db.exams
                                                    group exam2 by new { UserId = exam2.UserId, SectionId = exam2.SectionId, Attempt = exam2.Attempt } into grouped
                                                    select new { UserId = grouped.Key.UserId, SectionId = grouped.Key.SectionId, LastAttempt = grouped.Max(x =&gt; x.Attempt) })
                                                    .OrderByDescending(x =&gt; x.LastAttempt)
                                                    .Select(x =&gt; new
                                                    {
                                                       UserId = x.UserId,
                                                       SectionId = x.SectionId,
                                                       LastAttempt = x.LastAttempt
                                                    })
  
                              on new { UserId = exam.UserId, SectionId = section.Id }
                              equals new { UserId = groupedTable.UserId, SectionId = groupedTable.SectionId }
                              select exam)
                              .Distinct()
                              .ToListAsync();
  </code></pre>
  <p>also tried this</p>
  <pre><code>   var result = await (from exam in db.exams
                              join section in db.sections on exam.SectionId equals section.Id
                              select new
                              {
                                  UserId = exam.UserId,
                                  SectionId = exam.SectionId,
                                  Attempt = exam.Attempt
                              })
                              .GroupBy(x =&gt; new
                              {
                                  x.UserId,
                                  x.SectionId,
                                  x.Attempt
                              })
                              .Select(x =&gt; new
                              {
                                  UserId = x.Key.UserId,
                                  SectionId = x.Key.SectionId,
                                  Attempt = x.Max(x =&gt; x.Attempt)
                              })
                              .ToListAsync();
     
  </code></pre>
  <p>but the result is the same:</p>
  <pre><code>{ UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 8, Attempt = 1 }
  { UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 10, Attempt = 1 }
  { UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 9, Attempt = 1 }
  { UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 10, Attempt = 2 }
  </code></pre>
  <p>I this example I need to exclude where SectionId = 10 and Attempt = 1</p>