none
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:

    select
    
     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,
           dInvoiceDate,
           sInvoiceNumber
    


    Friday, August 26, 2011 4:42 AM

Answers

  • 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 {
     tbpurchaseorderarrived.DArrivalDateLOG,
     tbpurchaseorderarrived.DInvoiceDate,
     tbpurchaseorderarrived.SInvoiceNumber
    } 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