Mark Tabor

Mark Tabor

  • 569
  • 1.9k
  • 430.8k

Getting All Rows and not matching how to use left join in LINQ stateme

Oct 26 2020 5:45 AM
Hi I have a query and i have tables as well ,Courses Table,STudent&courses table,Taacher Table,Teacher and Courses table , Now on student Dashboard i want to show the courses only those who have been assigned to this student , and I also want to show teacher Name so I have joined all the Related tables , MY Registration Table contain the Taecher Name , The issue is Now I am getting 2 Rows instead of one , This student has assigned only one course , but it shows 2 rows I found the issue as well because two teacher have been assigned to that course and it comes due to highlighted section of the below query
  1. var employeeRecord = (from e in Student_Assigned_courses  
  2. join d in students on e.Student_id equals d.Student_Id into table1  
  3. from d in table1.DefaultIfEmpty() where (e.Student_id == UserId)  
  4. join tc in teacherAssignedCourses on e.Course_Id equals tc.CourseId into table10  
  5. from tc in table10.DefaultIfEmpty() where (e.Year_Id == tc.Year && e.Program_Id==tc.Program_Id)  
  6. join R in registrations on tc.Teacher_Id equals R.User_Id into Table11  
  7. from R in Table11.DefaultIfEmpty()  
  8. join i in courses on e.Course_Id equals i.Course_Id into table2  
  9. from i in table2.DefaultIfEmpty()  
  10. join m in Module on e.Module_Id equals m.Id into table3  
  11. from m in table3.DefaultIfEmpty()  
  12. join y in year on e.Year_Id equals y.Id into table4  
  13. from y in table4.DefaultIfEmpty()  
  14. join p in Programs on e.Program_Id equals p.Id into table5  
  15. from p in table5.DefaultIfEmpty()  
  16. join g in grade on e.Grade equals g.Id into table6  
  17. from g in table6.DefaultIfEmpty()  
  18. join s in semesters on e.Semster_Id equals s.Semester_Id into table7  
  19. from s in table7.DefaultIfEmpty()  
  20. join b in blocks on e.Block_Id equals b.Id into table8  
  21. from b in table8.DefaultIfEmpty()  
  22. join st in status on e.Status equals st.Id into table9  
  23. from st in table9.DefaultIfEmpty()  
  24. select new StudentCoursesViewModel  
  25. {  
  26. studentAssignedCourses = e,  
  27. courses = i,  
  28. programs = p,  
  29. //curses=curr,  
  30. curses = h,  
  31. semester = s,  
  32. blocks = b,  
  33. studnets = d,  
  34. Year = y,  
  35. Modules = m,  
  36. grade = g,  
  37. Status = st,  
  38. Registrations = R,  
  39. teacherAssignedCourses=tc,  
  40. }).ToList();  
  41. int pageSize = 5;  
  42. int pageNumber = (page ?? 1);  
  43. return View(employeeRecord.ToPagedList(pageNumber, pageSize));
How I can achieve only the courses which has been assigned to the Students

Answers (1)