Updating in LINQ to Objects
How to update LINQ to Objects. Trying to 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
0
a source to share
2 answers
Ok, I would do the updates imperatively. Something like that:
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 double zero checks are not required due to the way null comparisons are handled in C #. (i.e. (null == null)
true)
+1
a source to share
For better performance and what I think is cleaner code, you can use the ToLookUp method. If Q2 is large, it will be much better since there are no nested loops.
var Q2LookUp =
Q2.ToLookUp(q2 => new {q2.ParameterId, q2.MaterialID, q2.ProductId});
foreach (var q1 in Q1)
{
q1.TotalTonnes =
Q2Lookup[new {q1.ParameterId, q1.MaterialID, q1.ProductId}]
.Sum(q2 => q2.Quantity);
}
0
a source to share