semi-impossible linq query (from sql) RRS feed

  • Question

  • hey, I have a sql query that i would REALLY REALLY like to pass to a linq query but i already spent 4 hours trying... 

    Cant figure out :(

    I know there is a lot of linq gurus around here... Im hoping to learn something :) Can someone help me?

    My model:


    The sql query:

     case when sum(poia.nQuantity) < sum(poi.nQuantity) then 'Parcial'
    	else 'Total'
     end [status]
    from tbPurchaseOrderArrived poa
    inner join tbPurchaseOrderItemArrived poia
     on poa.idPurchaseOrderArrived = poia.idPurchaseOrderArrived
    inner join tbPurchaseOrder po
     on po.idPurchaseOrder = poa.idPurchaseOrder
    inner join tbPurchaseOrderItem poi
     on po.idPurchaseOrder = poi.idPurchaseOrder
     and poi.idProduct = poia.idProduct
    group by dArrivalDateLOG,

    Friday, August 26, 2011 4:42 AM


  • Give this a try and let me know how you make out...


    from tbpurchaseorderarrived in db.TbPurchaseOrderArrived
    join tbpurchaseorderitemarrived in db.TbPurchaseOrderItemArrived on tbpurchaseorderarrived.IdPurchaseOrderArrived equals tbpurchaseorderarrived.IdPurchaseOrderArrived
    join tbpurchaseorder in db.TbPurchaseOrder on tbpurchaseorder.IdPurchaseOrder equals tbpurchaseorder.IdPurchaseOrder
    join tbpurchaseorderitem in db.TbPurchaseOrderItem
       on new { tbpurchaseorder.IdPurchaseOrder, tbpurchaseorderitemarrived.IdProduct }
       equals new { tbpurchaseorder.IdPurchaseOrder, tbpurchaseorderitem.IdProduct }
    group new {tbpurchaseorderarrived, tbpurchaseorderitemarrived, tbpurchaseorderitem} by new {
    } into g
    select new {
     status = 
     g.Sum(p => p.tbpurchaseorderitemarrived.NQuantity) < g.Sum(p => p.tbpurchaseorderitem.NQuantity) ? "Parcial" : "Total"

    Saturday, August 27, 2011 10:00 PM