Linq to Entities Join, Group, Sum with Northwind Orders

Question

I'm trying to write a linq query which should sum order_detail lines from the Northwind.mdb and then return a summarized total along with a few details of the employee responsible. I'm using LINQpad to test it and this is what I have so far

void Main()
{
    var result = (from e in Employees
        join o in Orders on e.EmployeeID equals o.EmployeeID
        join d in OrderDetails on o.OrderID equals d.OrderID
        where o.OrderID == 10250
        group new { e, o, d } by new 
        {
             o.OrderID, e.Address, e.BirthDate, e.City, e.Country,
             e.LastName, e.FirstName, e.Title, e.TitleOfCourtesy, e.HireDate,
             e.Region, e.PostalCode, e.HomePhone,
             e.Extension, e.ReportsTo,e.PhotoPath, e.EmployeeID,
             d.Quantity, d.UnitPrice, d.Discount
        }
        into grp select new   
        {
            Name = grp.Key.FirstName + " " + grp.Key.LastName,
            OrderID = grp.Key.OrderID,      
            Address = grp.Key.Address,
            SalesTotal = grp.Sum(x => x.d.UnitPrice * x.d.Quantity)
        });

        result.Dump();       
}

Output from LINQPad

I was expecting just one line with a total of 1813.00. Can someone tell me what I'm doing wrong?

Answer 1

You're not grouping at the right level. If you change it to group by the employee, then you should get the right results:

void Main()
{
    var result = (from e in Employees
        join o in Orders on e.EmployeeID equals o.EmployeeID
        join d in OrderDetails on o.OrderID equals d.OrderID
        where o.OrderID == 10250
        group new { e, o, d } by new 
        {
             e.EmployeeID, e.FirstName, e.LastName, e.Address
        }
        into grp select new   
        {
            Name = grp.Key.FirstName + " " + grp.Key.LastName,
            Address = grp.Key.Address,
            SalesTotal = grp.Sum(x => x.d.UnitPrice * x.d.Quantity)
        });

        result.Dump();       
}

If you want one line per Order, group by that too:

void Main()
{
    var result = (from e in Employees
        join o in Orders on e.EmployeeID equals o.EmployeeID
        join d in OrderDetails on o.OrderID equals d.OrderID
        where o.OrderID == 10250
        group new { e, o, d } by new 
        {
             e.EmployeeID, e.FirstName, e.LastName, e.Address,
             o.OrderID
        }
        into grp select new   
        {
            Name = grp.Key.FirstName + " " + grp.Key.LastName,
            OrderID = grp.Key.OrderID,
            Address = grp.Key.Address,
            SalesTotal = grp.Sum(x => x.d.UnitPrice * x.d.Quantity)
        });

        result.Dump();       
}