title: Transforming T-SQL Query into C# LINQ with joins on multiple conditions and also grouping on multiple conditions
body: <p>First I want to say hello, I'm new to this site ;-)</p>
  
  <p>My problem is to transform the following <code>sql-query</code> into a <code>c# linq-query</code>.</p>
  
  <p>( I HAVE searched hard for an existing answer but I'm not able to combine the solution for
  the joins on multiple conditions and the grouping / counting ! )</p>
  
  <p>The sql-query :</p>
  
  <pre><code>DECLARE @datestart AS DATETIME
  DECLARE @dateend AS DATETIME
  
  SET @datestart = '01.04.2014'
  SET @dateend = '30.04.2014'
  
  SELECT md1.value AS [controller],md2.value AS [action], COUNT(md2.value) AS   [accesscount], MAX(re.TIMESTAMP) AS [lastaccess] FROM recorderentries AS re
  INNER JOIN messagedataentries AS md1 ON re.ID = md1.recorderentry_id AND md1.position =  0
  INNER JOIN messagedataentries AS md2 ON re.ID = md2.recorderentry_id AND md2.position = 1
  WHERE re.TIMESTAMP &gt;= @datestart AND re.TIMESTAMP &lt;= @dateend 
  AND re.messageid IN ('ID-01','ID-02' )
  GROUP BY md1.value,md2.value
  ORDER BY [accesscount] DESC 
  </code></pre>
  
  <p>Any suggestions are welcome ...</p>
  
  <p>What i have so far is this :</p>
  
  <pre><code>var _RecorderActionCalls = (from r in _DBContext.RecorderEntries
                                      join m1 in _DBContext.MessageDataEntries on
                                       new {
                                          a = r.ID,
                                          b = 0
                                       } equals new {
                                          a = m1.ID,
                                          b = m1.Position
                                       }
                                      join m2 in _DBContext.MessageDataEntries on
                                                         new {
                                                            a = r.ID,
                                                            b = 0
                                                         } equals new {
                                                            a = m2.ID,
                                                            b = m2.Position
                                                         }
                                      where r.TimeStamp &gt;= StartDate &amp; r.TimeStamp &lt;=     EndDate &amp; (r.MessageID == "VAREC_100_01" | r.MessageID == "VAAUTH-100.01")
                                      group r by new { md1 = m1.Value, md2 = m2.Value }    into r1
                                      select new { controller = r1.Key.md1, action =    r1.Key.md2, count = r1.Key.md2.Count() }).ToList();
  </code></pre>
  
  <p>But this throws an exception ( translated from german ) :</p>
  
  <p>DbExpressionBinding requires an input expression with a Listing Result Type ...</p>
  
  <p>UPDATE : Back with headache ... ;-)</p>
  
  <p>I found a solution to my problem :</p>
  
  <pre><code>var _RecorderActionCalls = _DBContext.RecorderEntries
                                   .Where(r =&gt; r.TimeStamp &gt;= StartDate &amp; r.TimeStamp &lt;=     EndDate &amp; (r.MessageID == "VAREC_100_01" | r.MessageID == "VAAUTH-100.01"))
                                   .GroupBy(g =&gt; new { key1 =    g.MessageData.FirstOrDefault(md1 =&gt; md1.Position == 0).Value, key2 = g.MessageData.FirstOrDefault(md2 =&gt; md2.Position == 1).Value })
                                   .Select(s =&gt; new {
                                      ControllerAction = s.Key.key1 + " - " + s.Key.key2,
                                      Value = s.Count(),
                                      Last = s.Max(d =&gt; d.TimeStamp)
                                   }).ToList();
  </code></pre>
  
  <p>With this syntax it works for me. Thank you for thinking for me :-)</p>