Conversion SQL to LINQ

Question

How can the below 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 these.

Is this right way to do ?

Thanks

Answer 1

Something like this should do the trick. I don't to LinqToSql though so you will need to checkup on how to get the IQuerable object. Otherwise this 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}

Answer 2

I don't think we should be doing your LINQ query for you, but rather pointing you in the right direction to learn LINQ to SQL.

Otherwise the next query you have you will be in the same position.

• C# 101 Linq to SQL Examples
• VB.NET Linq to SQL 101 Examples
• http://www.linqpad.com
• http://www.hookedonlinq.com
• http://dimecasts.net/Casts/ByTag/Linq2Sql