SQL to L2S Translation Help

Question

So here is the original query

SELECT SUM(PickQty), SUM(ReqQty), AssignmentID, StopID FROM PickRequest GROUP BY AssignmentID, StopID

in LINQ

from a in dbReqs
group a by new { a.AssignmentID, a.StopID }
into pr
select new
{
Assignment = pr.Key,
StopID = pr.Select(s=> s.StopID),
PickQty = pr.Sum(p=> p.PickedQty),
Count = pr.Sum(c => c.ReqQty)
}

I must be doing something wrong because the LINQ version takes ages and the results seem a bit off. Ideas?

Answer 1

Try:

from a in dbReqs
group a by new { a.AssignmentID, a.StopID }
into pr
select new
{
  AssignmentID = pr.Key.AssignmentID,
  StopID = pr.Key.StopID,
  PickQty = pr.Sum(p=> p.PickedQty),
  Count = pr.Sum(c => c.ReqQty)
}

Note you can monitor what LINQ-to-SQL does quite easily:

ctx.Log = Console.Out; // or some other writer