multiple grouping, inner joning in Linq

Question

I am trying to translate this into Linq and cannot figure it out:

SELECT
    CustomerOrder.ShipState, MONTH(OrderFulfillment.OrderDate) AS Mnth,
    YEAR(OrderFulfillment.OrderDate) AS Yer,
    SUM(OrderFulfillment.Tax) AS TotalTax
FROM
    OrderFulfillment INNER JOIN
        CustomerOrder ONOrderFulfillment.OrderID =CustomerOrder.OrderID
WHERE
    (OrderFulfillment.Tax > 0)
GROUP BY
    CustomerOrder.ShipState, MONTH(OrderFulfillment.OrderDate),
    YEAR(OrderFulfillment.OrderDate)
ORDER BY
    YEAR(OrderFulfillment.OrderDate) DESC, CustomerOrder.ShipState,
    MONTH(OrderFulfillment.OrderDate) DESC

I have Linqpad and have gone through a bunch of the examples but cannot figure this out.

Answer 1

First of all, it would be nice to know what exactly you can't figure out. If you're completely lost and don't know where to begin then you need to google around for linq joining and grouping.

Here's something I did recently that may (possibly) point you in the right direction:

// This groups items by category and lists all category ids and names
from ct in Categories
  join cst in Category_Subtypes 
    on ct.Category_Id equals cst.Category_Id
  join st in Subtypes
    on cst.Subtype_Id equals st.Subtype_Id
where 
  st.Type_Id == new Guid(id)
group ct by new { ct.Category_Id, ct.Display_Text } into ctg
select new 
{
    Id = ctg.Key.Category_Id,
    Name = ctg.Key.Display_Text
}

Answer 2

I think you want to do something like this:

from c in CustomerOrder
    join o in OrderFulfillment on c.OrderId equals o.OrderId
where 
    o.Tax > 0
group o by 
    new { c.ShipState, Mnth = of.OrderDate.Month, Yer = of.OrderDate.Year } 
        into g
orderby
    g.Key.Yer descending, g.ShipState, g.Key.Mnth descending
select
    new { g.Key.ShipState, g.Key.Mnth, g.Key.Yer, 
    TotalTax = g.Sum(i => i.Tax) };

I haven't tried to compile it, but I think this is something along the lines of what you want.

The idea is that first you perform your join to link the customers and orders. Then apply your filter condition.

At that point, you want to get all the orders that have a particular group, so the group operator is applied.

Finally, order the results, then select out all info from the keys for each group, and sum up the tax in each of the group.