LINQ aggregate across more than one table
I want to replicate this query in LINQ to SQL but am too unfamiliar with how to do it.
SELECT开发者_运维知识库 A.Recruiter, SUM(O.SaleAmount * I.Commission) --This sum from fields in two different tables is what I don't know how to replicate
FROM Orders AS O
INNER JOIN Affiliate A ON O.AffiliateID = A.AffiliateID
INNER JOIN Items AS I ON O.ItemID = I.ItemID
GROUP BY A.Recruiter
I've got this far:
from order in ctx.Orders
join item in ctx.Items on order.ItemI == item.ItemID
join affiliate in ctx.Affiliates on order.AffiliateID == affiliate.AffiliateID
group order //can I only group one table here?
by affiliate.Recruiter into mygroup
select new { Recruiter = mygroup.Key, Commission = mygroup.Sum(record => record.SaleAmount * ?????) };
group new {order, item} by affiliate.Recruiter into mygroup
select new {
Recruiter = mygroup.Key,
Commission = mygroup
.Sum(x => x.order.SaleAmount * x.item.Commission)
};
And an alternative way of writing the query:
from aff in ctx.Affiliates
where aff.orders.Any(order => order.Items.Any())
select new {
Recruiter = aff.Recruiter,
Commission = (
from order in aff.orders
from item in order.Items
select item.Commission * order.SaleAmount
).Sum()
};
try linqpad, just Google, amazing tool!
精彩评论