Linq-2-SQL Left Outer Join in C#

Question

What would be the linq-2-sql syntax for this SQL Query:

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

tables are like this: enter image description here

Try http://www.linqpad.net/. You can create your linq from sql queries. — samar, Mar 13 '14 at 11:39
Also check this link http://itworksonmymachine.wordpress.com/2009/09/27/sql-to-linq-cheat-sheet/ — samar, Mar 13 '14 at 11:49

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

Here is the answer

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

This returns the same answer as your SQL question related to this SQL Left outer join question. I developed this simply using LinqPad

edited May 23 '17 at 11:52

Community

1
1

answered Mar 13 '14 at 16:00

Steve Ford

7,433
19
40

How linqpad helped you to complete this? I just could run some queries and watch the result. – VSB Mar 14 '14 at 13:18

samar · Answer 2 · 2014-03-13T12:47:56.950

0

Not very sure if this will work but it is definitely worth a try.

If it doesnt work as expected then please let me know what query does it fire on the database so that I can improve accordingly.

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

Hope this helps.

edited Mar 13 '14 at 12:47

answered Mar 13 '14 at 12:09

samar

5,021
9
47
71

It got syntax error: Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer' because it is not a delegate type – VSB Mar 13 '14 at 12:38
Are you sure it is because of my code? Because it is working fine here. I have tested with some sample data. I have updated my answer as well. – samar Mar 13 '14 at 12:48
I changed it to: ` var cls = from e in dbContext.employees join emp in dbContext.taskAssignments 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() };` but it returns 1 for count of all columns – VSB Mar 13 '14 at 13:07
Is "Id" column in "employees" table a unique key or a primary key? If yes then the output of the query is correct because grouping by id will have unique rows. If there are duplicate values for id and name together then only the count will be more than 1. Please check. – samar Mar 14 '14 at 05:52

score 0 · Answer 3 · answered Mar 15 '14 at 10:37

Try this.

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#

3 Answers3

Linked