How to do an update in LINQ to Objects. Trying convert SQL to Linq
Quality
(
TransactionID int,
Quantity float,
MaterialID int,
ProductID int,
ParameterID int,
ParameterValue float,
TotalTonnes float
)
How to convert below SQL to linq:
UPDATE Q1
SET TotalTonnes = ( SELECT SUM(Quantity)
FROM @Quality Q2
WHERE Q1.ParameterID = Q2.ParameterID
AND ( ( Q1.MaterialID = Q2.MaterialID )
OR ( Q1.MaterialID IS NULL
AND Q2.MaterialID IS NULL
)
)
AND ( ( Q1.ProductID = Q2.ProductID )
OR ( Q1.ProductID IS NULL
AND Q2.ProductID IS NULL
)
)
)
FROM @Quality Q1
Thanks
Well, I would do updates imperatively. Something like this:
foreach (var q1 in Q1)
{
q1.TotalTonnes = (from q2 in Q2
where q1.ParameterID == q2.ParameterID
&& q1.MaterialID == q2.MaterialID
&& q1.ProductID == q2.ProductID
select q2.Quantity).Sum();
}
Note that the double null checks aren't required because of the way null comparisons are handled in C#. (i.e. (null == null)
is true)