如何将 sql 查询转换为 linq LEFT OUTER JOIN

debugcn 发表于 Dev

埃尔文

这是返回我需要的数据的 SQL 查询：

SELECT 
    E.DESCS AS EMP, C.USERNAME,
    (SELECT A.DESCS 
     FROM CAD_COLABORADOR H, CAD_DEPT A 
     WHERE H.DEPT = A.ID AND H.ID = C.ID) AS DEPT,
    D.IDENTIFICADOR, D.MODELO, O.DESCS AS OFFICE,
    D.K_OFFICE AS 'KEY OFFICE', S.DESCS AS SO, D.K_SO AS 'KEY SO'
FROM 
    IN_DESKTOP D 
LEFT OUTER JOIN 
    CAD_COLABORADOR C ON D.ID = C.DESKTOP
INNER JOIN 
    CAD_EMP E ON D.EMP = E.ID
INNER JOIN 
    CAD_OFFICE O ON D.V_OFFICE = O.ID        
INNER JOIN 
    CAD_SO S ON D.V_SO = S.ID ;

这是我正在使用的 linq 表达式 plus 有一些不一致之处，因为它返回的大多数数据与 SQL 查询不完全相同：

var result = from desk in db.IN_DESKTOP
             join co in db.CAD_COLABORADOR on desk.id equals co.id into egroup
             from co in egroup.DefaultIfEmpty()
             join e in  db.CAD_EMP on desk.emp equals e.id
             join o in db.CAD_OFFICE on desk.v_office equals o.id 
             join s in db.CAD_SO on desk.v_so equals s.id
             select new
                    {
                            Empresa = e.descs,
                            UserName = co.username,
                            Departamento = co.CAD_DEPT.descs,
                            Identificador = desk.identificador,
                            Modelo = desk.modelo ,
                            Offices = o.descs,
                            KeyOfice =  desk.k_office,
                            KeySo = desk.k_so
                    };

伊恩·弗莱明

C# 中的左外连接只是一个 GroupJoin 后跟一个 SelectMany，如果为空则默认 - 我一直在将此扩展用于 lambda 表达式

public static class LinqExtension
{
    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftCollection, IEnumerable<TRight> rightCollection, 
        Func<TLeft, TKey> leftKey, Func<TRight, TKey> rightKey,
        Func<TLeft, TRight, TResult> result)
    {
        return leftCollection.GroupJoin(rightCollection,
                leftKey,
                rightKey,
                (leftObject, rightObject) => new { leftObject, rightObject })
            .SelectMany(x => x.rightObject.DefaultIfEmpty(), 
                (l, r) => new { left = l.leftObject, right = r })
            .Select(x => result.Invoke(x.left, x.right));
    }
}

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

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