Saturday, 31 December 2011

Left outer join in linq

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 == 

                 foreach (var item in q.ToList())
                    Console.WriteLine(string.Format("Teacher: {0} No of Courses {1}",item.Teachers, item.Courses));