Sven Sven - 7 months ago 11
SQL Question

sql query to c# using northwind DB

using the northwind DB, i have to make a query to get employeename, amount of orders per employee and average price of those orders

this is what the query looks like in SQL

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


this runs fine but I have to make this in c# and I am a little stuck

So far, I have got this

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);


"edit" I am having trouble working the average in, tried a lot of things but I can't get it to work

"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, except that the field lastname and firstname are blank, even with ToString

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 => 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 => x.OrderID).Distinct().Count(),
AveragePricePerOrder = c.Sum(x => x.Quantity * x.UnitPrice) / c.Select(x => x.OrderID).Distinct().Count()
}).Take(10);

Answer

Try this query:

var query = (from emp in ctx.Employers
             join order in ctx.Orders on emp.EmployeeID equals order.EmployerID 
             join orderDet in ctx.Order_Details on order.OrderID equals orderDet.OrderID 
             group new { emp, order, orderDet } 
             by new { emp.FirstName, emp.LastName, emp.EmployeeID,  order.OrderID } 
             into orderGroup
             let a = new
             {
                 orderGroup.Key.EmployeeID,
                 orderGroup.Key.FirstName,
                 orderGroup.Key.LastName,
                 orderGroup.Key.OrderID,
                 sum1 = orderGroup.Sum(x => x.orderDet.Quantity * x.orderDet.UnitPrice),
             }
             group a by new { a.FirstName, a.LastName, a.EmployeeID } into empGroup
             let a2 = new
             {
                 empGroup.Key.FirstName,
                 empGroup.Key.LastName,
                 sum = empGroup.Sum(x => x.sum1),
                 count = empGroup.Count()
             }
             orderby a2.count descending
             select new
             {
                 a2.FirstName,
                 a2.LastName,
                 amountOfOrders = a2.count,
                 AveragePricePerOrder = a2.sum / a2.count
             }).Take(10);
Comments