CODEWITHSUNDEEP
linq-to-sqlado.netnorthwind
Bega
Bega

Reputation: 1

Write a LINQ query for total sales of each salesman according product category

I'm trying to write a LINQ query to find number of total sales made by each employee, according product categories using Northwind database.

Required result should be like:

EmployeeID : ProductCategoryID : totalNumberofSales

for example:

1stEmployee : 1st category : x sales . . .

nthEmployee : nthCategory : y sales

Tables from NorthWind database are EMPLOYEES, ORDERS, ORDER_DETAILS, PRODUCTS, CATEGORIES.

I tried it this, but stuck at the end.

    List<ORDER_DETAILS> o_details = db.ORDER_DETAILS.ToList();
                    List<ORDERS> orders = db.ORDERS.ToList();
                    List<CATEGORIES> categories = db.CATEGORIES.ToList();
                    List<EMPLOYEES> employee = db.EMPLOYEES.ToList();
                    List<PRODUCTS> products= db.PRODUCTS.ToList();
    
                    var list= orders.GroupJoin(employee, o => o.PersonelID, e => e.PersonelID, (e, os) => new { e, os.})
.GroupJoin(o_details, tp => tp.e.OrderID, od => od.OrderID, (od, tps) => new { od, tps })
.GroupJoin(products, tp2 => tp2.od.e.ORDER_DETAILS, p => p.ORDER_DETAILS, (tp2,ps) => new{tp2, ps})
.GroupJoin(categories, tp3=>tp3.ps, c=>c.CategoryID, (tp3s,cs)=>new { tp3s, cs}).GroupBy(m => new {  }

Upvotes: 0

Views: 417

Answers (1)

sgmoore
sgmoore

Reputation: 16077

Your Northwind database may be different from mine as I don't have a PersonelID column, but have a EmployeeID, but hopefully this will help.

If it wasn't for the discount, then could be a simple matter of grouping the orderDetails records, ie

 var summary = (from od in OrderDetails
 group od by new { od.Order.EmployeeID, od.Product.CategoryID } into results
 orderby results.Key.EmployeeID, results.Key.CategoryID 
 select new
 {
    results.Key.EmployeeID,
    results.Key.CategoryID,
    Sales = results.Sum(a => a.UnitPrice * a.Quantity)
 } 
 ).ToList();

The discount makes it more complicated because of rounding issues etc, but you can use the OrderDetailsExtended view which provides the ExtendedPrice column, but means you need to perform explicit joins rather than navigation properties, eg

 var summary2 = (from od in OrderDetailsExtended 
    join order   in Orders   on od.OrderID   equals order.OrderID
    join product in Products on od.ProductID equals product.ProductID
    group od by new { order.EmployeeID, product.CategoryID } into results
    orderby results.Key.EmployeeID, results.Key.CategoryID
    select new
    {
        results.Key.EmployeeID,
        results.Key.CategoryID,
        Sales = results.Sum(a => a.ExtendedPrice)
    }
    ).ToList();

Upvotes: 0

Related Questions