CODEWITHSUNDEEP
c#sqllinq
KingOfHypocrites
KingOfHypocrites

Reputation: 9537

Trouble converting sql statement to linq

I've read through the 101 linq samples and they seem to have pieces of what I'm trying to do but having trouble putting it together. I have a query like so which joins my 'Customers' to some 'Orders' values (e.g. The total of all orders and the number of orders).

SELECT * 
FROM cm_Customers 
LEFT OUTER JOIN 
(
 SELECT UserID, SUM(Total) as Total, COUNT(OrderID) as Orders 
 FROM cm_orders 
 GROUP BY UserID
) TOTALS ON cm_Customers.UserID = TOTALS.UserID 
ORDER BY TOTALS.Total DESC

I figured out how to do a left outer join but I do see any way to get the sums without grouping perhaps? I don't see any examples of grouping for sums then left joining this result back to another select.

var x = 
    from c in db.Customers
    join o in db.Orders on c.UserID equals o.UserID into oo
    from o in oo.DefaultIfEmpty()
    select new { FirstName = c.FirstName, SUM??(o.Total),  };

I seem to just be spinning my wheels at this point. Any help would be appreciated.

Upvotes: 0

Views: 64

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

You can perform the group by after join. The results should be exactly the same:

var x = from c in db.Customers
            join o in db.Orders on c.UserID equals o.UserID into oo
            from o in oo.DefaultIfEmpty()
            group new { c, o } by c.UserID into g
            select new {
                Customer = g.FirstOrDefault().c,
                Total = g.Sum(x => x.o.Total)
                Count = g.Select(x => x.o.OrderId).Distinct().Count()
            };

Upvotes: 1

Related Questions