LINQ-2-SQL左外连接在C＃(Linq-2-SQL Left Outer Join in C#

什么是这个SQL查询的LINQ-2-SQL语法：

SELECT emp.id, Name, Count(t.id) as CNT 
FROM employee emp 
LEFT JOIN taskAssignment t 
on emp.id = t.FKEmployeeID GROUP BY emp.id, Name

表是这样的：

Answer 1:

下面是答案

var lst = from emp in Employeetables
join task in TaskAssignmentTables
    on  emp.EmployeeId equals task.FKEmployeeId into j 
    from result in j.DefaultIfEmpty()
    group result by new { emp.EmployeeId, emp.Name }  into groupResult
select new
{
    EmployeeId = groupResult.Key.EmployeeId,
    Name = groupResult.Key.Name,
    Count = groupResult.Count(r => r.FKEmployeeId != null)
};

这将返回相同的答案与此相关的您的SQL问题SQL LEFT OUTER JOIN问题 。 我开发了这个简单的使用LinqPad

Answer 2:

不是很确定这是否会工作，但它是绝对值得一试。

如果它不按预期工作，那么请让我知道它火数据库上查询什么，这样我可以相应地提高。

List<Employee> employee = new List<Employee>() 
{ 
    new Employee() { id = 1, Name = "Samar" }, 
    new Employee() { id = 1, Name = "Samar" },
    new Employee() { id = 1, Name = "Samar" },
    new Employee() { id = 2, Name = "Sid" }
};

List<TaskAssignment> taskAssignment = new List<TaskAssignment>() 
{ 
    new TaskAssignment(){FKEmployeeID = 1},
    new TaskAssignment(){FKEmployeeID = 1}
};


var cls = from e in employee
            join emp in taskAssignment on e.id equals emp.FKEmployeeID into empout
            group e by new { e.id, e.Name } into g
            select new { g.Key.id, g.Key.Name, CNT = g.Count() };

希望这可以帮助。

Answer 3:

尝试这个。

var employees = from emp in dbContext.Employees
                    join task in dbContext.TaskAssignmentTable 
                    on emp.employeeID equals task.FKEmployeeID
                    into tEmpWithTask
                    from tEmp in tEmpWithTask.DefaultIfEmpty()
                    group tEmp by new { emp.EmployeeID, emp.Name } into grp
                    select new {
                      grp.Key.EmployeeID,
                      grp.Key.Name,
                      grp.Count(t=>t.TaskID != null)
                    };

文章来源: Linq-2-SQL Left Outer Join in C#