Linq issue select Sum of one column minus another from different table
This is my sql code
SELECT M.PartNo AS ModulePartNo,
(SELECT SUM(Delivered - Allocated)
FROM V_InStock Stk
WHERE Stk.PartNo = M.PartNo) AS Available
FROM V_Products M
WHERE M.PartNo='100-25897'
I am having real problems getti开发者_Python百科ng this to work in Linq, can't work out the sum part - any help would be really appreciated
This should work.
var query = from stk in V_InStock
group stk by stk.PartNo into stkg
where stkg.Key == '100-25897'
select new
{
ModulePartNo = stkg.Key,
Available = stkg.Sum(s => s.Delivered) - stkg.Sum(s => s.Allocated)
}
If you need to do aggregate grouping etc you will probably need to set up an object to "select" everything into. However for a straight join something like this should work.
var outputObject = (from t1 in dbContext.table1
join t2 in dbContext.table2 on t1.PartNo equals t2.PartNo
select (t1.Delivered - t2.Allocated));
精彩评论