LINQ left outer join

LINQ 沒有直接提供 left outer join 指令, 則作法如下
SQL 語法

SELECT [t0].[XH], [t0].[Apply], [t1].[DisplayName] AS [ApplyName], [t0].[Covering], [t2].[DisplayName] AS [CoveringName]
FROM [Dayoff] AS [t0]
INNER JOIN [Users] AS [t1] ON [t0].[Apply] = [t1].[XH]
LEFT OUTER JOIN [Users] AS [t2] ON [t0].[Covering] = ([t2].[XH])
ORDER BY [t0].[DayoffDate] DESC

LINQ 方式

    var query = from m in Dayoffs
        join o in Users on m.Apply equals o.XH
        join c in Users on m.Covering equals c.XH into gj
        from sub in gj.DefaultIfEmpty()
        orderby m.DayoffDate descending
        select new {XH = m.XH,
            Apply = m.Apply,
            ApplyName = o.DisplayName,
            Covering = m.Covering,
            CoveringName = sub.DisplayName
        };
    query.Dump();

關鍵
將要 left join 的每一項都 into 到一個temp變數, 然後透過 from sub in temp.DefaultIfEmpty() 來供給後續的操作, 這樣 linq 就會轉化為 left outer join 命令的

寫法二, 較為簡潔, 也才能用在多層次的 join

var cri = from m in db.Complain
    join o in db.Orders on m.XHOrders equals o.XH
    from wdLeft in db.WorkDispatch.Where(w => w.XH == m.XHcase).DefaultIfEmpty() //left outer join 1
    from carLeft in db.Workcar.Where(e => e.XH == wdLeft.XHWorkcar).DefaultIfEmpty() //left join 2