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