CODEWITHSUNDEEP
c#linq
C. Jun
C. Jun

Reputation: 53

LINQ left join, group by and Count generates wrong result

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

Upvotes: 5

Views: 699

Answers (4)

quetzalcoatl
quetzalcoatl

Reputation: 33506

Well, see what @Danny said in his answer, it's the best and cleanest fix for this case. By the way, you could also rewrite it to the 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 effectively a "left join", and .Join is "inner join". Be careful to not mistake one for another.

Upvotes: 2

Danny
Danny

Reputation: 191

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.

Upvotes: 3

daniell89
daniell89

Reputation: 2272

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 only one change you should take.

Upvotes: 0

Igor
Igor

Reputation: 62213

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

Upvotes: 2

Related Questions