how to convert a sql query into linq LEFT OUTER JOIN

Question

This is the SQL query that returns the data I need:

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 ;

This is the linq expression I'm using plus has some inconsistencies since it returns the most data are not exactly the same as the SQL query:

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
                    };

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you. — NetMage, Apr 16 '19 at 21:09

score 0 · Accepted Answer · answered Apr 16 '19 at 21:29

A left outer join in C# is just a GroupJoin followed by a SelectMany with default if empty - I've been using this extension for lambda expressions

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));
    }
}

how to convert a sql query into linq LEFT OUTER JOIN

1 Answers1