Let's have a scenario in which there are two tables Teachers and Teacher_Courses .Teachers are assigned courses having teacherid in Teacher_Courses table as the foreign key. So to pick up all the Teacher's list with no of courses assigned regardless of having been assigned a course or not in linq you will be using left outer join as follows.
using (TeacherContext context = new TeacherContext())
{
var Teachers = context.Teachers;
var TeacherCourse = context.TeacherCourses;
var q = from c in Teachers
join o in TeacherCourse on
c.int_TeacherId equals o.int_TeacherId into j
from Course in j.DefaultIfEmpty().GroupBy(m=>m.int_TeacherId)
select new
{
Teachers = c.vcr_TeacherName,
Courses = Course.Count() == 0 ? "(no Courses Assigned)" : Course.Where(m => m.int_TeacherId ==
c.int_TeacherId).Count().ToString()
};
foreach (var item in q.ToList())
Console.WriteLine(string.Format("Teacher: {0} No of Courses {1}",item.Teachers, item.Courses));
}
Comments
Post a Comment