LINQでLEFT OUTER JOINする

例えばこんなデータ型と

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? DeptId { get; set; }
    public DateTime HireDate { get; set; }
}

こんなデータ型があったとして、

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
}

こんなSQLをLINQでやりたいなんてとき

SELECT
    E.Id As 'EmpId,'
    E.Name As 'EmpName',
    D.Name AS 'DeptName',
    E.HireDate
FROM
    Employee E
LEFT OUTER JOIN
    Department D
    ON D.Id = E.DeptId

こんな風に書くとできます。

var emps = new List<Employee>();
var depts = new List<Department>();

var infos = emps.GroupJoin(depts, emp => emp.DeptId, dept => dept.Id, (emp, tmpDept) => new {emp, tmpDept})
                .SelectMany(x => x.tmpDept.DefaultIfEmpty(), (x, dept) => new
                {
                    EmpId = x.emp.Id,
                    EmpName = x.emp.Name,
                    DeptName = (dept != null) ? dept.Name : "Non-Dept",
                    HireDate = x.emp.HireDate
                });

--追記

こんなとか

var emps = new List<Employee>();
var depts = new List<Department>();

var infos = emps.Select(x =>
{
    var dept = depts.FirstOrDefault(y => y.Id == x.DeptId);
    return new
    {
        EmpId = x.Id,
        EmpName = x.Name,
        DeptName = (dept != null) ? dept.Name : "Non-Dept",
        HireDate = x.HireDate
    };
});

こんな方法もありますね。

var emps = new List<Employee>();
var depts = new List<Department>().ToDictionary(x => x.Id);

var infos = emps.Select(x => new
{
    EmpId = x.Id,
    EmpName = x.Name,
    DeptName = x.DeptId.HasValue && depts.ContainsKey(x.DeptId.Value) ? depts[x.DeptId.Value].Name : "Non-Dept",
    HireDate = x.HireDate
});