title: Convert SQL to LINQ with group by
body: <p>I'm stumped trying to convert the following sql to linq:</p>
  
  <pre><code>SELECT t.* FROM(SELECT mwfieldid,MAX([TimeStamp]) AS MaxValue, BatchDocumentID
     FROM mw_BatchField 
     GROUP BY mwfieldid,BatchDocumentID) x
    JOIN mw_BatchField t ON x.mwfieldid = t.mwfieldid
     AND x.MaxValue = t.TimeStamp
     and x.BatchDocumentID = t.BatchDocumentID 
  </code></pre>
  
  <p>So far I had to convert it to a stored proc to get it to work. I'd rather know how to write this correctly in linq. I tried using a sql to linq converter (<a href="http://www.sqltolinq.com/" rel="nofollow">http://www.sqltolinq.com/</a>) which produced this code that had errors in it: (Are these converters any good? It didn't seem to produce anything useful with a few tries.)</p>
  
  <pre><code>From x In ( 
      (From mw_BatchFields In db.mw_BatchFields
      Group mw_BatchFields By 
        mw_BatchFields.MWFieldID,
        mw_BatchFields.BatchDocumentID
       Into g = Group 
      Select 
        MWFieldID,
        MaxValue = CType(g.Max(Function(p) p.TimeStamp),DateTime?),
        BatchDocumentID)
      )
  Join t In db.mw_BatchFields
        On New With { .MWFieldID = CInt(x.MWFieldID), .MaxValue = CDate(x.MaxValue), .BatchDocumentID = CInt(x.BatchDocumentID) }
    Equals New With { .MWFieldID = t.MWFieldID, .MaxValue = t.TimeStamp, .BatchDocumentID = t.BatchDocumentID }
  Select 
    BatchFieldID = t.BatchFieldID,
    BatchDocumentID = t.BatchDocumentID,
    MWFieldID = t.MWFieldID,
    TimeStamp = t.TimeStamp,
    value = t.value,
    DictionaryValue = t.DictionaryValue,
    AutoFilled = t.AutoFilled,
    employeeID = t.employeeID
  </code></pre>
  
  <p>Seems like a lot of code for such a simple query, and it doesn't compile.</p>