Aggregating parent/child tables using Linq to SQL and VB.net

Question

I'm having a nightmare with LINQ.

I've got a table of Projects, and each project has many InvoiceHeaders. The Invoice header had a field AmountNet - the value of the invoice.

I want to retrive, for arguments sake, all of the fields from Projects, with the count of invoice headers and the sum of AmountNet. I.e. list of my projects, how many invoices I've raised and the value.

Doesn't sound hard does it.... well, I've just spent the best part of two hours charging through Google and SO to find a solution. I've managed this:

from c in Projects _
select new with _
{ _
    .ID = c.ID, _
    .Invoices = InvoiceHeaders.Where(Function(p) c.ID = p.ProjectID).Count, _
    .InvoiceValue = InvoiceHeaders.Where(Function(p) c.ID = p.ProjectID).Sum(function(f) f.AmountNet) _
}

Those with eagle eyes will spot how HORRIFIC this statement it - for every project it runs two queries to get the aggregates. Eughk.

I've also got somewhere with multiple aggregates in Linq / vb:

from ih in InvoiceHeaders _
group ih by ih.projectid into g = Group  _
select new with { _
    .InvoiceValue = g.Sum(function(f) f.AmountNet), _
    .InvoiceCount = g.Count _
} _

But with this I can't figure out how to return the details of the project. Esp seeing as 'Key' doesn't work in Vb.net.

BTW - I'm using VB.net :)

Any ideas?

Answer 1

My try would be this

from ih in InvoiceHeaders _
group ih by ih.projectid into g = Group  _
select new with {.pjs=(from p in projects where p.ID=ih.projectid select p), _
    .InvoiceValue = g.Sum(function(f) f.AmountNet), _
    .InvoiceCount = g.Count _
}