title: How to transform grouping with MAX from sql to linq query
body: <p>I am stuck trying to convert below sql query to linq:</p>
  <pre><code>SELECT
      Table1.Id1,
      Table2.Id2,
      MAX(Table3.DateField) AS MaxDateField
  FROM
      Table1 
  INNER JOIN
      Table2 ON Table1.Id1 = Table2.FK1 
  LEFT OUTER JOIN
      Table3 ON Table2.Id2 = Table3.FK2
  GROUP BY 
      Table1.Id1, Table2.Id2
  </code></pre>
  <p>Table1/Table2 relationship is 1 to many, and Table2/Table3 is also 1 to many.</p>
  <p>The SQL works properly and delivers only one record from Table3 for each Table1 and Table2 record.</p>
  <p>For linq I tried:</p>
  <pre><code>var query = from table1 in tables1
              join table2 in tables2 on table1.Id1 equals table2.FK1
              join table3 in tables3 on table2.Id2 equals table3.FK2 into JoinedTable3Table2
              from table3 in JoinedTable3Table2.DefaultIfEmpty()
  
              group new {
                   table3.MyDateField
              }
              by new  {
                   table1.Id1,
                   table2.Id2,
                   table3.MyDateField
              }
              into g    
              select new MyModel {
                   Field1 = g.Key.Id1,
                   Field2 = g.Key.Id2,
                   Field3 = g.Max(d =&gt; d.MyDateField)
              };
  </code></pre>
  <p>But this returns n records from Table3 for each record in Table1 and Table2.</p>
  <p>Then I tried (as Svyatoslav Danyliv suggested):</p>
  <pre><code>var query = from table1 in tables1
              join table2 in tables2 on table1.Id1 equals table2.FK1
              join table3 in tables3 on table2.Id2 equals table3.FK2 into JoinedTable3Table2
              from table3 in JoinedTable3Table2.DefaultIfEmpty()
  
              group new {
                   table1.Id1,
                   table2.Id2
              }
              by new  {
                   table1.Id1,
                   table2.Id2,
                   table3.MyDateField
              }
              into g    
              select new MyModel {
                   Field1 = g.Key.Id1,
                   Field2 = g.Key.Id2,
                   Field3 = g.Max(d =&gt; d.MyDateField) 
              };
  </code></pre>
  <p>But then I get an error at <code>Field3 = g.Max(d =&gt; d.MyDateField)</code> (CS 1061:anonymous type)</p>