lambda多表左连接

下面是mysql多表左连接，对应的lambda表达式写法，只为快速解决你的问题，详细的就不介绍了，只出结果，相信稍微有点基础的都能看懂。相关知识点，请自行百度

关键字：GroupJoin、SelectMany

sql语句表达方式：

对应的

lambda表达方式

sql语句：

SELECT
A.psd_id,A.wo_nbr,A.creation_time,A.wo_customer_part,B.psd_qty_ord,A.net_num,A.report_num,C.user_code,C.user_name,A.machine_code
FROM mes_material_down_rd AS A
LEFT JOIN mes_sync_psd_det AS B ON A.psd_id=B.psd_id AND A.op=B.psd_op AND A.work_id=B.psd_work_id /**/
LEFT JOIN mes_busi_team_user AS C ON A.user_id=C.user_id
WHERE A.is_deleted=0 AND A.creation_time>='2021-11-01 00:00:00' AND A.creation_time<='2021-12-01 00:00:00'
ORDER BY A.creation_time DESC;

lambda语句：

var query = _materialDownRdRepo
                .WhereIf(!string.IsNullOrWhiteSpace(body.MachineName), d => d.MachineCode == body.MachineName)
                .WhereIf(body.BeginTime.HasValue, d => d.CreationTime >= body.BeginTime.Value)
                .WhereIf(body.EndTime.HasValue, d => d.CreationTime <= body.EndTime.Value)
                .WhereIf(!string.IsNullOrWhiteSpace(body.PsdId), d => d.PsdId == body.PsdId)
                .WhereIf(!string.IsNullOrWhiteSpace(body.WoNbr), d => d.WoNbr == body.WoNbr)
                ;
            var queryNew = query.GroupJoin(
                    _psdDetRepo.AsQueryable()
                    , l => new { a = l.PsdId, b = l.Op.ToString(), c = l.WorkId }
                    , r => new { a = r.PsdId, b = r.PsdOp, c = r.PsdWorkId }
                    , (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(), (lr, r) => new
                {
                    lr.l,
                    r.PsdQtyOrd
                })
                .GroupJoin(_busiTeamUser.AsQueryable(), l => l.l.UserId, r => r.UserId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(), (lr, r) => new
                {
                    lr.l.l,
                    lr.l.PsdQtyOrd,
                    r.UserCode,
                    r.UserName
                });

=========================================================================

sql语句：

SELECT 
	A.Id ApplyReceiptOrderId,
	A.XCode,
	B.Id ApplyReceiptOrderItemId,
	C.Id ReceiptOrderId,
	C.XCode,
	D.Id ReceiptOrderItemId,
	E.Id InboundOrderId,
	E.XCode
FROM ApplyReceiptOrder A
left join ApplyReceiptOrderItem B on A.Id=B.ApplyReceiptOrderId
left join ReceiptOrder C on A.XCode=C.ApplyReceiptOrderCode
left join ReceiptOrderItem D on C.Id=D.ReceiptOrderId
left join InboundOrder E on C.Id=E.ReceiptOrderId
where A.XCode='CD20240925002'

linq语句：

var query = from aro in applyReceiptOrderQueryable
            join aroi in applyReceiptOrderItemQueryable on aro.Id equals aroi.ApplyReceiptOrderId into temparoi
            from aroi in temparoi.DefaultIfEmpty()
            join ro in receiptOrderQueryable on aro.XCode equals ro.ApplyReceiptOrderCode into tempro
            from ro in tempro.DefaultIfEmpty()
            join roi in receiptOrderItemQueryable on ro.Id equals roi.ReceiptOrderId into temproi
            from roi in temproi.DefaultIfEmpty()
            join ib in inboundQueryable on ro.Id equals ib.ReceiptOrderId into tempib
            from ib in tempib.DefaultIfEmpty()
            join ibi in inboundItemItemQueryable on ib.Id equals ibi.InboundOrderId into tempibi
            from ibi in tempibi.DefaultIfEmpty()
            where codes.Contains(aro.XCode)
            select new
            {
                ApplyReceiptOrderId = aro.Id,
                ApplyReceiptOrderCode = aro.XCode,
                ApplyReceiptOrderItemId = aroi == null ? 0 : aroi.Id,
                ReceiptOrderId = ro == null ? 0 : ro.Id,
                ReceiptOrderItemId = roi == null ? 0 : roi.Id,
                InboundId = ib == null ? 0 : ib.Id,
                InboundStatus = ib == null ? WmsROStatus.OPEN : ib.XStatus,
                InboundItemId=ibi==null ? 0 : ibi.Id,
            };