CODEWITHSUNDEEP
linqlinq-to-sql
Sreedhar
Sreedhar

Reputation: 30025

Conversion SQL to LINQ

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

Upvotes: 0

Views: 149

Answers (3)

Sreedhar
Sreedhar

Reputation: 30025

Ok Sorted out this as : 

var result = (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,
                                                 finalResult.Key.Quantity,
                                                 ParameterValue = finalResult.Sum(final => (final.ActualValue*final.Quantity/final.TotalTonnes))}).ToList();

Is this right ?

Upvotes: 0

GONeale
GONeale

Reputation: 26494

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.

Upvotes: 3

Derek Ekins
Derek Ekins

Reputation: 11391

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}

Upvotes: 0

Related Questions