Converting SQL to LINQ
How can the following be converted to LINQ
SELECT Q.MaterialID AS MaterialID, Q.ProductID AS ProductID, QB.Quantity AS Quantity,
Q.ParameterID AS ParameterID, SUM((Q.ParameterValue * Q.Quantity)/Q.TotalTonnes) AS ParameterValue
FROM @Quality Q
INNER JOIN @QuantityBreakdown QB
ON ((Q.MaterialID = QB.MaterialID) OR (Q.MaterialID IS NULL AND QB.MaterialID IS NULL))
AND ((Q.ProductID = QB.ProductID) OR (Q.ProductID IS NULL AND QB.ProductID IS NULL))
GROUP BY Q.MaterialID, Q.ProductID, ParameterID, QB.Quantity
Am upto:
(from final in (from q in qualities
from qb in quantityBreakDowns
where q.MaterialID == qb.MaterialID && q.ProductID == qb.ProductID
select q)
group final by new {final.MaterialID, final.ProductID, final.ParameterID, final.Quantity}
into FinalResult
select new
{
FinalResult.Key.MaterialID,
FinalResult.Key.ProductID,
FinalResult.Key.ParameterID
//QB.Quantity AS Quantity ??
//SUM((Q.ParameterValue * Q.Quantity)/Q.TotalTonnes) AS ParameterValue ??
}
'??' => how to get them.
Is it the right thing to do?
thanks
0
a source to share
2 answers
Something like this should do the trick. I am not using LinqToSql, so you will need to check how to get the IQuerable object. Otherwise, it should point you in the right direction.
var results = from q in myIQuerableObject<Quality>
from qb on qb.MaterialId == q.MaterialId or (qb.MaterialId == null && q.MaterialId == null)
select new {Quality = q, Breakdown = qb}
0
a source to share