LINQ left join, group by and Count generates wrong result

Question

I'm 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'm expecting this...

Geography       2
Economy         1
Biology         0

How can I fix it?

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

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 don't think any grouping is required for answering your question. You only want to know 2 things: - name of department - number of students per department

By using the 'join' and 'into' you're putting the results of the join in the temp identifier gst. You only have to count the number of results in the gst.

Answer 2

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 the students by the department name but you want the count to filter out null students. I did change the join syntax slightly although that really does not matter to much.

Here is a working fiddle