title: sql query to c# using northwind DB
body: <p>using the northwind DB, i have to make a query to get employeename, amount of orders per employee and average price of those orders</p>
  
  <p>this is what the query looks like in SQL</p>
  
  <pre><code>SELECT TOP 10
  a.LastName, a.FirstName, amountOfOrders = COUNT(DISTINCT b.OrderID), AveragePricePerOrder = SUM(c.Quantity*c.UnitPrice) /COUNT(DISTINCT b.OrderID)
  FROM Employees a join orders b on (a.EmployeeID = b.EmployeeID)
      join [Order Details] c on b.OrderID = c.OrderID
  Group BY a.EmployeeID, a.LastName, a.FirstName
  ORDER BY amountOfOrders Desc
  </code></pre>
  
  <p>this runs fine but I have to make this in c#  and I am a little stuck</p>
  
  <p>So far, I have got this </p>
  
  <pre><code>var query_rx = (from c in ctx.Employees
                          join or in ctx.Orders on c.EmployeeID equals or.EmployeeID
                          join ord in ctx.Order_Details on or.OrderID equals ord.OrderID
                          group c by new 
                          { 
                               c.EmployeeID, 
                               c.LastName, 
                               c.FirstName, 
                               amount = c.Orders.Count 
                          } into c
                          orderby c.Key.amount descending
                          select new
                          {
                              c.Key.LastName,
                              c.Key.FirstName,
                              amountOfOrders = c.Key.amount
                          }).Take(10);
  </code></pre>
  
  <p>"edit" I am having trouble working the average in, tried a lot of things but I can't get it to work</p>
  
  <p>"edit" I have changed the query a bit with help from Dohnal's suggestion.
  This looks almost exactly like what i want in terms of columns, <strong>except</strong> that the field lastname and firstname are <strong>blank</strong>, even with ToString        </p>
  
  <pre><code>var query_rx = (from or in ctx.Order_Details
                          join ord in ctx.Orders on or.OrderID equals ord.OrderID
                          group or by new
                          {
                              ord.EmployeeID
                          } into c
                          orderby c.Select(x =&gt; x.OrderID).Distinct().Count() descending
                          select new
                          {
                              Lastname = (from emp in ctx.Employees
                                         where c.Key.EmployeeID == emp.EmployeeID
                                         select emp.LastName),
                              Firstname = (from emp in ctx.Employees
                                          where c.Key.EmployeeID == emp.EmployeeID
                                          select emp.FirstName),
                              c.Key.EmployeeID,
                              AmountOfOrders = c.Select(x =&gt; x.OrderID).Distinct().Count(),
                              AveragePricePerOrder = c.Sum(x =&gt; x.Quantity * x.UnitPrice) / c.Select(x =&gt; x.OrderID).Distinct().Count()
                          }).Take(10);
  </code></pre>