将 sql 转换为 linq（连接和分组依据）

debugcn 发表于 Dev

Zhongwenslayer

我正在将旧的 webforms 应用程序转换为 asp.net mvc，并且在将我的一个 sql 语句转换为 linq 时遇到问题。特别是，我需要有关分组和连接的帮助。我通过查看此处的各种示例尝试了多种方法，但没有一种方法对我有用。

SELECT cp.PartNumber, cp.PartDescription, PFEP.PFEPTx, PFEP.KBQty, 
TX_QOH.QOH, TX_ReworkQOH.Rework_QOH as Rework, SUM(ShippingInput.Qty) AS 
'Ocean'
FROM CustomerParts as cp 

LEFT JOIN TX_QOH 
ON cp.PartNumber = TX_QOH.PN

LEFT JOIN TX_ReworkQOH 
ON cp.PartNumber = TX_ReworkQOH.PN

LEFT JOIN ShippingInput 
ON cp.PartNumber = ShippingInput.PN AND  ShippingInput.Status <> 'Received' 

LEFT JOIN PFEP
ON cp.PartNumber= PFEP.PN

WHERE cp.PartType = 'Actuator Part' AND cp.Division = 'Bayne' AND cp.Active 
= 'Yes' AND TX_QOH.QOH = '0'

Group By cp.PartNumber, TX_QOH.QOH, TX_ReworkQOH.Rework_QOH, 
cp.PartDescription, PFEP.PFEPTx, PFEP.KBQty
Order By cp.PartNumber ASC

Zhongwenslayer

LINQ版本

var queryNew = (from cp in db.MasterPartLists
            join tx in db.TxQohs on cp.CustomerPn equals tx.Pn into jTxQoh
            from tx in jTxQoh.DefaultIfEmpty()
            join c in db.ShipIns.Where(r => r.ShipInStatusId != 3) on cp.CustomerPn equals c.Pn into jShipIns
            from c in jShipIns.DefaultIfEmpty()
            join d in db.Pfeps on cp.CustomerPn equals d.CustomerPn into jPfeps
            from d in jPfeps.DefaultIfEmpty()
            where d.PartTypeId == parttype && cp.CustomerDivisionId == division && cp.ActivePartId == 1 && tx.Qoh == 0
            group new {cp, d, tx, c} by new {cp.CustomerPn, tx.Qoh, cp.PartDescription, d.PfepTx, d.KbQty}
            into gcp
            orderby gcp.Key.CustomerPn
            select new
            {
                gcp.Key.CustomerPn,
                gcp.Key.PartDescription,
                gcp.Key.PfepTx,
                gcp.Key.KbQty,
                gcp.Key.Qoh,
                Ocean = (int?)gcp.Sum(r => r.c.Qty)
            });

SQL 版本

var queryNew = "SELECT cp.CustomerPn, cp.PartDescription, Pfeps.PFEPTx, Pfeps.KBQty, TxQohs.Qoh, SUM(ShipIns.Qty) AS 'Ocean' "
                          + "FROM MasterPartLists as cp "
                          + "LEFT JOIN TxQohs "
                          + "ON cp.CustomerPn = TxQohs.Pn AND TxQohs.Qoh = '0' "
                          + "LEFT JOIN ShipIns "
                          + "ON cp.CustomerPn = ShipIns.Pn AND ShipIns.ShipStatusId <> '3' "
                          + "LEFT JOIN Pfep "
                          + "ON cp.CustomerPn = Pfeps.CustomerPn "
                          + "WHERE cp.PartTypeId = parttype AND cp.CustomerDivisionId = division AND cp.ActivePartId = '1' "
                          + "Group By cp.CustomerPn, TxQohs.Qoh, cp.PartDescription, Pfeps.PfepTx, Pfeps.KbQty "
                          + "Order By cp.CustomerPn ASC ";

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。