I have an SQL query which I need to translate to Linq to SQL. I was successful until it came to Sums and I'm completely stuck there. I know how to do single/simple groupings and sums but this time I desperately need help. I'd appreciate any ideas.
Table relations are like this:
TreeNodes - one-to-many - Dispatches (not every TreeNode has Dispatch entry)
Dispatches - one-to-many - DispatchesJobs (not every Dispatch has DispatchJob entry)
DispatchesJobs one-to-many - DispatchesJobsReceivings (not every DispatchJob has DispatchJobReceiving entry)
Basically what I need to find out is:
For every Dispatch: Get the Dispatch's TreeNode name and its quantity, spent, return count from the DispatchesJobsReceivings table.
Here's my SQL query:
select t.TreeNodes_id, t.name
sum(djr.quantity) as quantity, sum(djr.returns) as returns, sum(djr.spent) as spent
from TreeNodes as t
inner join Dispatches as d
on t.TreeNodes_id = d.TreeNodes_id
left outer join DispatchesJobs as dj
on d.Dispatches_id = dj.Dispatches_id
left outer join DispatchesJobsReceivings as djr
on dj.DispatchesJobs_id = djr.DispatchesJobs_id
group by t.TreeNodes_id, t.name
Which gives this result:
TreeNodes_id name quantity returns spent
------------ ------------ ------- ------- ----------
77 CEMENT 20 17 3
122 SAND NULL NULL NULL
I've come this far translating it:
from t in db.TreeNodes
join d in db.Dispatches
on t.TreeNodes_id equals d.TreeNodes_id
join dj in db.Dispatches_Jobs
on d.Dispatches_id equals dj.Dispatches_id
into tmpDJ
from rowTmpDJ in tmpDJ.DefaultIfEmpty()
join djr in db.DispatchesJobsReceivings
on rowTmpDj.DispatchesJobs_id equals djr.DispatchesJobs_id
into tmpDjr
from rowTmpDjr in tmpDjr.DefaultIfEmpty()
select new
{
TreeNode_id = t.TreeNodes_id,
name = t.name,
quantity = rowTmpDjr.quantity,
returns = rowTmpDjr.returns,
spent = rowTmpDjr.spent
};
Which gives this result:
TreeNodes_id name quantity returns spent
------------ ------------ ------ -------- ----------
77 CEMENT 1 0 1
77 CEMENT 2 0 2
77 CEMENT 4 4 0
77 CEMENT 13 13 0
122 SAND NULL NULL NULL
And I'm stuck right here. I'd appreciate any help.