针对linq左连接（多个条件）的Lambda代码编写方法

转自https://www.codenong.com/50463628/

sql:

SELECT [t0].[ID], [t0].[IsNeedJS], [t0].[TCID], [t0].[JGID], [t0].[TCMC], [t0].[JGMC], [t0].[CJJ], [t0].[PTJ], [t0].[JSJ], COALESCE([t3].[TCName],[t3].[TCName]) AS [TCname], [t2].[CSMC]
FROM [Group_TCInfo] AS [t0]
INNER JOIN [JG_XXB] AS [t1] ON [t0].[JGID] = [t1].[TJJGID]
INNER JOIN [XT_CSLB] AS [t2] ON [t1].[CSBH] = [t2].[CSBH]
LEFT OUTER JOIN [TCCOMPANY] AS [t3] ON [t0].[TCID] = [t3].[TCID] AND ([t3].[CompanyID] = 2022053000001034) 
WHERE  ([t0].[GroupID] = 2022053100000694) AND ([t0].[OrderId] = 2022053100000846) AND ([t0].[IsDel] = 0)

linq. Lambda写法：

var tcinfo = db.Group_TCInfo
                            .Join(db.JG_XXB, a => a.JGID, b => b.TJJGID, (a, b) => new { Group = a, JG = b })
                            .Join(db.XT_CSLB, a => a.JG.CSBH, b => b.CSBH, (a, b) => new { a.Group, a.JG, CS = b })
                            .GroupJoin(db.TCCOMPANY, a => new { a.Group.TCID, qyid }, b => new { b.TCID, qyid = b.CompanyID }, (a, b) => new { a.Group, a.JG, a.CS, TCCOM = b })
                            .Where(t => t.Group.GroupID == item.GroupID && t.Group.OrderId == orderid && t.Group.IsDel == 0)
                            .SelectMany(p => p.TCCOM.DefaultIfEmpty(), (a, b) => new { a.Group, a.JG, a.CS, TCCOM = b })
                            .Select(t => new OrderTcInfo
                            {
                                ID = t.Group.ID,
                                IsNeedJS = t.Group.IsNeedJS,
                                TCID = t.Group.TCID,
                                JGID = t.Group.JGID,
                                TCMC = t.Group.TCMC,
                                JGMC = t.Group.JGMC,
                                PTJ = t.Group.PTJ,
                                JSJ = t.Group.JSJ,
                                CJJ = t.Group.CJJ,
                                TCname = t.TCCOM.TCName ?? t.Group.TCMC,
                                CSMC = t.CS.CSMC,
                            }
                            )
                            .ToList();