A Practice for LINQ and ASP.NET – II

In the last post we discussed Task Two of the exercise. There is another way that we can achieve the same result.

Task 2:

  • Subject page: show number of staffs, classes and enrollments belong to each subject
  • Teacher page: show number of classes and subjects belong to each teacher
  • Student page: show number of enrollments each student has enrolled in
  • Class page: show which subject each class belong to, who is teaching that class and its enrollment count.

Note: Be careful when you perform joins between different tables.

Here we are going to use Join statement in LINQ. We get number of classes belong to each subject:

//Get number of classes belong to each subject
var classCount = (from s in db.Subjects
                    join c in db.Classes
                    on s.SubjectID equals c.SubjectID into box
                    from b in box.DefaultIfEmpty()
                    select new
                    {
                        Subject = s,
                        Class = b
                    }).GroupBy(x => new { x.Subject }).Select(x => new
                    {
                        Id = x.Key.Subject.SubjectID,
                        Name = x.Key.Subject.Name,
                        ClassCount = x.Where(g => g.Class != null).Distinct().Count()
                    });

The statement is long and tricky, and hard to understand. We can separate them to several part and use LINQPad to see the result of LINQ. (Go to LINQPad website for more information)

Let’s from the very beginning. If we use SQL to join the tables, like this:

SELECT *
FROM dbo.Subject AS s
LEFT OUTER JOIN dbo.Class AS c
ON c.SubjectID = s.SubjectID

We can get the result:

When we use LINQ to join the tables, the statement is like this:

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID into box

Here we put the result of join into another table, box. There comes the question, WHAT BOX IS?

You might say that box is the table that contains the fields from both subject and class table, just like the table generated by SQL.  If we run the following query:

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID into box
from b in box.DefaultIfEmpty()
select b

You might say that we can get the following result (the joined table):

BUT, when we actually run the query, we get this result, which is totally different from the table above:

It’s not the joined table, but the class part of the joined table. It looks strange and hard to understand.

When we select s,

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID into box
from b in box.DefaultIfEmpty()
select s

we can get the subject part of the joined table.

When we select c,

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID into box
from b in box.DefaultIfEmpty()
select c

sorry, there will be an error,

telling me that c does not exist anymore! It has been replaced by b.

It’s so confusing that we should be very careful about it.

There’s a another question, why we use a third table, box? The reason why we join the two tables into another table, box, is that here we are going to use LEFT OUTER JOIN. Alternatively, if we just want to use INNER JOIN, we can simplify the code to:

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID
select s

Thus we will get the subject part of the joined table, and change select s to  select c we can get the class part. No ambiguous understanding here. If we want to get the entire joined table, we can put:

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID
select new {s, c}

Then the result will be:

Similarly, if we want to use LEFT OUTER JOIN, we can change the code to

from s in Subjects
join c in Classes
on s.SubjectID equals c.SubjectID into box
from b in box.DefaultIfEmpty()
select new {s, b}

to show the entire joined table.

Then back to the code in Task Two, we want to show the entire joined table of a LEFT OUTER JOIN

var classCount = (from s in db.Subjects
                    join c in db.Classes
                    on s.SubjectID equals c.SubjectID into box
                    from b in box.DefaultIfEmpty()
                    select new
                    {
                        Subject = s,
                        Class = b
                    })

and then group by the subject part:

var classCount = (from s in db.Subjects
                    join c in db.Classes
                    on s.SubjectID equals c.SubjectID into box
                    from b in box.DefaultIfEmpty()
                    select new
                    {
                        Subject = s,
                        Class = b
                    }).GroupBy(x => new { x.Subject })

the result is:

Here each element in the list is one group, which includes a key and an IGrouping<> property that contains the entire records of the joined table that belong to the group.

At last, we select the properties we want from the table above:

var classCount = (from s in db.Subjects
                    join c in db.Classes
                    on s.SubjectID equals c.SubjectID into box
                    from b in box.DefaultIfEmpty()
                    select new
                    {
                        Subject = s,
                        Class = b
                    }).GroupBy(x => new { x.Subject }).Select(x => new
                    {
                        Id = x.Key.Subject.SubjectID,
                        Name = x.Key.Subject.Name,
                        ClassCount = x.Where(g => g.Class != null).Distinct().Count()
                    });

Here, the x in Select(x => new...) refer to each group of the table.

Then we can see the final result:

(to be continued)

** You can download the source code on GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *