LINQ aggregate / SUM grouping problem

Question

I'm having trouble getting my head around converting a traditional SQL aggregate query into a LINQ one. The basic data dump works like so:

    Dim result =
    (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
    Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
    Join p As Models.Product In Data.Products.GetAll On i.ProductID Equals p.ID
    Join pg As Models.ProductGroup In Data.ProductGroups.GetAll On p.ProductGroupID Equals pg.ID
    Join gl As Models.GLAccount In Data.GLAccounts.GetAll On pg.GLAccountSellID Equals gl.ID
    Where (gl.ID = GLID)
    Select ih.Period,i.ExtendedValue)

What I need to really be getting out is ih.Period (a value from 1 to 12) and a corresponding aggregate value for i.ExtendedValue. When I try to Group ih I get errors about i being out of scope/context, and I'm not sure how else to go about it.

Answer 1

Try splitting it into effectively two different queries for simplicity. Example:

From result In
    (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll
    Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID
    Join p As Models.Product In Data.Products.GetAll On i.ProductID Equals p.ID
    Join pg As Models.ProductGroup In Data.ProductGroups.GetAll On p.ProductGroupID Equals pg.ID
    Join gl As Models.GLAccount In Data.GLAccounts.GetAll On pg.GLAccountSellID Equals gl.ID
    Where (gl.ID = GLID) And (ih.FinancialYear = FinancialYear)
    Select ih.Period, i.ExtendedValue)
Group By result.Period Into Sum(result.ExtendedValue)