Update in LINQ to Objects

Question

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

Answer 1

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)

Answer 2

For better performance and what I think is cleaner code you can use the ToLookUp method. If Q2 is large this will be much better performance as there is 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);
}