title: Using Max, Group By, Join and Where in Lambda Expression
body: <p>I'm try to make a complex query (instead to me), with Lambda expression. I have the SQL that I want to "translate" to Lambda.</p>
  
  <pre><code>SELECT MAX((SUBSTRING(tbp.dt,4,4)+SUBSTRING(tbp.dt,2,2)+SUBSTRING(tbp.dt,1,2))) as Dt, 
  tb._n, tbp.number, tbp.dsc
  FROM TB_A tb
  JOIN TB_B_C tbp ON tbp.number = tb.number
  WHERE tbp.rec = 0 AND tbp.processing = 0 AND tb._n != '' AND tbp.error = 0
  GROUP BY tb._n, tbp.number, tbp.dsc
  </code></pre>
  
  <p>Until now I have this Lambda Expression:</p>
  
  <pre><code>var results = db.a
             .Join(db.b_c, proc =&gt; proc.number, andam =&gt; andam.number, (proc, andam) =&gt; new { proc, andam })
             .Where(d =&gt; d.proc._n != "" &amp;&amp; d.andam.rec == false &amp;&amp; d.andam.processing == false &amp;&amp; d.andam.error)
             .ToList();
  </code></pre>
  
  <p>How can I finish my select to have the same result as the SQL query? If possible can you explain how to think properly when "translating" a query to Lambda?</p>
  
  <p>Thanks a lot.</p>