title: LINQ query using join and aggregate functions
body: <p>Urgh...my LINQ has suddenly taken a turn for the worse!!</p>
  
  <p>I have two tables and want to join and perform aggregate functions upon the tables.</p>
  
  <p>Firstly, is this possible, I am assuming it is, and secondly, how do I handle the aggregate functions? As part of the same LINQ statement?</p>
  
  <p>The SQL statement will look a bit like this:</p>
  
  <pre><code>SELECT     t1.Col1, t1.Col2, t1.Col3, t2.Col10, 
                        t2.Col11, SUM(t2.Col4) AS TotalPurchases, COUNT(t1.Col5) AS ProductCount,t2.Col7, 
                        t2.Col6, t2.Col8, t2.Col9
  FROM         t1 INNER JOIN
                        t2 ON t1.Col1 = t2.Col1 AND t1.Col5 = t2.Col5 AND 
                        t1.Col2 = t2.Col2
  GROUP BY t1.Col1, t1.Col2, t1.Col3, t2.Col7, t2.Col6, 
                        t2.Col8, t2.Col9, t2.Col10, t2.Col11
  HAVING      (t1.Col1 = @p1) AND (t1.Col2 = @p2) AND (t1.Col3 = @p3)
  </code></pre>
  
  <p>Obviously, the 'AS' sections can be ignored, I can rename those in the controls.</p>
  
  <p>Any hints, tips, pointers would be greatly appreciated.</p>