LINQ left join, group by and Count generates incorrect result

Question

I am struggling with linq (left join - group - count). Please help me. Below is my code and it gives me this result.

Geography 2 Economy 1 Biology 1 

I expect this ...

 Geography 2 Economy 1 Biology 0 

How can i fix this?

 class Department { public int DNO { get; set; } public string DeptName { get; set; } } class Student { public string Name { get; set; } public int DNO { get; set; } } class Program { static void Main(string[] args) { List<Department> departments = new List<Department> { new Department {DNO=1, DeptName="Geography"}, new Department {DNO=2, DeptName="Economy"}, new Department {DNO=3, DeptName="Biology"} }; List<Student> students = new List<Student> { new Student {Name="Peter", DNO=2}, new Student {Name="Paul", DNO=1}, new Student {Name="Mary", DNO=1}, }; var query = from dp in departments join st in students on dp.DNO equals st.DNO into gst from st2 in gst.DefaultIfEmpty() group st2 by dp.DeptName into g select new { DName = g.Key, Count = g.Count() }; foreach (var st in query) { Console.WriteLine("{0} \t{1}", st.DName, st.Count); } } } 

Answer 1

And my answer is similar to @Igor

  var query = from dp in departments join st in students on dp.DNO equals st.DNO into gst from st2 in gst.DefaultIfEmpty() group st2 by dp.DeptName into g select new { DName = g.Key, Count = g.Count(std => std != null) }; 

g.Count (std => std! = null) is just one change you have to make.

Answer 2

 var query = from department in departments join student in students on department.DNO equals student.DNO into gst select new { DepartmentName = department.DeptName, Count = gst.Count() }; 

I do not think any kind of grouping is required to answer your question. You only want to know 2 things: - department name - number of students per department

Using "join" and "in", you put the connection results in the identifier temp gst. You only need to count the number of results in gst.

Answer 3

 var query = from dp in departments from st in students.Where(stud => stud.DNO == dp.DNO).DefaultIfEmpty() group st by dp.DeptName into g select new { DName = g.Key, Count = g.Count(x => x!=null) }; 

You want to group students by department name, but you want the graph to filter out zero students. I changed the syntax of the connection a bit, although it really doesn't matter much.

Here is a working fiddle

Answer 4

Ok, look what @Danny said in his answer, this is the best and cleanest solution for this case. By the way, you can also rewrite it to lambda syntax:

  var query = departments.GroupJoin(students, dp => dp.DNO, st => st.DNO, (dept,studs) => new { DName = dept.DNO, Count = studs.Count() }); 

I find this syntax much more predictable in results and often shorter.

BTW: .GroupJoin is actually a "left join", and .Join is an "inner join". Be careful not to make mistakes with each other.