Converting a SQL query with group by into LINQ query.

Question

I'm stuggling to replicate a SQL query into LINQ. Can any one help?

SQL:

 SELECT tblInvoice.lngID AS InvoiceID,
                tblInvoice.dtTimeStamp AS InvoiceDate,
                tblInvoice.strReference,
                tblInvoice.fltTotalValue,
                max(Project.ProjectID) AS ProjectID,
                max(Project.ProjectName) AS ProjectName,
                max(Project.Location) AS ProjectLocation
        FROM    tblInvoice INNER JOIN
                tblInvoiceLine ON tblInvoice.lngID = tblInvoiceLine.lngInvoiceID 
        WHERE   (tblInvoice.intStatus != 0)
        AND     (tblInvoice.lngPersonID = @PersonID)
        GROUP BY tblInvoice.lngID, tblInvoice.dtTimeStamp, strReference, fltTotalValue
        ORDER BY tblInvoice.lngID DESC

LINQ so far:

var invoices = from inv in db.TblInvoices
    join invLine in db.TblInvoiceLines on inv.LngID equals invLine.LngInvoiceID
    where inv.IntStatus != 0
    where inv.LngPersonID == personID
    group inv by  new {inv.LngID,inv.DtTimeStamp,inv.StrReference,inv.FltTotalValue} into newInv

Part of the problem is that I want to do a

select new Invoice(){
}

and build up my custom Invoice object but, I cant see any of the properties in newInv.

Can any one advise?

Answer 1

I don't have time for a full answer now, but:

• To get at properties of the key, use newInv.Key.StrReference etc
• To get at aggregates (e.g. max values) use newInv.Max(x => x.ProjectId) etc

Hopefully that'll be enough to get you going. Basically, newInv will be a group of entries, with an associated key (which is what you grouped by).