CODEWITHSUNDEEP
c#linq
David
David

Reputation: 1455

Linq Group & SUM

I really thought I was getting on with linq until now, Ive got a one to many relationship and I need to return the SUM for the Jobitems (jobitems.Cost)

List<Models.Inventory.JobItem> jobItems = BLL.Inventory.JobItem.GetAllActive(forceReload);
List<.Models.Inventory.Job> jobs = BLL.Inventory.Job.GetAllActive(forceReload);
    var getInvoicedItems2 = from j in jobs
    join ji in jobItems on j.JobId equals ji.JobId
    select new { JobCost = ji.Cost, JobId = ji.JobId};

ive got this far but im not sure how to group all by ji.JobId

Upvotes: 1

Views: 646

Answers (2)

David
David

Reputation: 1455

var getInvoicedItems2 = from ji in jobItems
    group ji by ji.JobId
    into g
    select new {JobId = g.Key, Cost = g.Sum(p => p.Cost)};

var getInvoicedItems = from j in jobs
    from s in schedules
    from i in invoices
    from jj in getInvoicedItems2
    where i.JobId == j.JobId && j.ScheduleId == s.ScheduleId && jj.JobId == j.JobId
    select new { JobCost = jj.Cost, InvoiceDate = i.CreatedDate, Status = i.Status, };

Seems to work

Upvotes: 0

Jeff Mercado
Jeff Mercado

Reputation: 134811

You can use a group join to group all job items into a set, then take the sum of all costs in that set.

var jobItems = BLL.Inventory.JobItem.GetAllActive(forceReload);
var jobs = BLL.Inventory.Job.GetAllActive(forceReload);
var query = from j in jobs
            join ji in JobItems on j.JobId equals ji.JobId into items
            select new
            {
                JobId = j.JobId,
                JobCost = items.Sum(ji => ji.Cost),
            };

Upvotes: 3

Related Questions