title: LINQ OrderBy Count of Records in a Joined Table
body: <p>I'm having trouble translating the following tSQL to LINQ to SQL in C#.  Any help would be much appreciated:</p>
  
  <pre><code>SELECT P.Name
  FROM Product P
      INNER JOIN OrderItems OI ON P.productID = OI.productID
          INNER JOIN Orders O ON OI.orderID = O.orderId
  WHERE P.Active = 1 AND O.Status &gt; 2
  ORDER BY count(OI.orderID) DESC
  </code></pre>
  
  <p>It's the ordering by the COUNT of a JOINED table that's throwing me for a loop.</p>
  
  <p>Here's what I have so far (with no orderby):</p>
  
  <pre><code>from p in CRM.tProducts
      join oi in CRM.tOrderItems on p.prodID equals oi.prodID
      join o in CRM.tOrders on oi.orderID equals o.orderID
  where o.status &gt; 1 &amp;&amp; p.active == true
  select p;
  </code></pre>
  
  <p>Thanks for any help!</p>