使用northwind DB对c＃进行sql查询

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

使用northwind DB，我必须进行查询以获得雇员名称，每位员工的订单数量和这些订单的平均价格

this is what the query looks like in SQL

这是查询在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

这很好但我必须在c＃中制作这个并且我有点卡住了

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

“编辑”我在Dohnal的建议的帮助下稍微改变了一下查询。这看起来几乎完全像我想要的列，除了字段lastname和firstname是空白，即使使用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);

1 个解决方案

#1

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