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

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

var employeeRecord = (from e in Student_Assigned_courses
join d in students on e.Student_id equals d.Student_Id into table1
from d in table1.DefaultIfEmpty() where (e.Student_id == UserId)
join tc in teacherAssignedCourses on e.Course_Id equals tc.CourseId into table10
from tc in table10.DefaultIfEmpty() where (e.Year_Id == tc.Year && e.Program_Id==tc.Program_Id)
join R in registrations on tc.Teacher_Id equals R.User_Id into Table11
from R in Table11.DefaultIfEmpty()
join i in courses on e.Course_Id equals i.Course_Id into table2
from i in table2.DefaultIfEmpty()
join m in Module on e.Module_Id equals m.Id into table3
from m in table3.DefaultIfEmpty()
join y in year on e.Year_Id equals y.Id into table4
from y in table4.DefaultIfEmpty()
join p in Programs on e.Program_Id equals p.Id into table5
from p in table5.DefaultIfEmpty()
join g in grade on e.Grade equals g.Id into table6
from g in table6.DefaultIfEmpty()
join s in semesters on e.Semster_Id equals s.Semester_Id into table7
from s in table7.DefaultIfEmpty()
join b in blocks on e.Block_Id equals b.Id into table8
from b in table8.DefaultIfEmpty()
join st in status on e.Status equals st.Id into table9
from st in table9.DefaultIfEmpty()
select new StudentCoursesViewModel
{
studentAssignedCourses = e,
courses = i,
programs = p,
//curses=curr,
curses = h,
semester = s,
blocks = b,
studnets = d,
Year = y,
Modules = m,
grade = g,
Status = st,
Registrations = R,
teacherAssignedCourses=tc,
}).ToList();
int pageSize = 5;
int pageNumber = (page ?? 1);
return View(employeeRecord.ToPagedList(pageNumber, pageSize));

How I can achieve only the courses which has been assigned to the Students

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
In 99% of the time we do not need joins untill there is any performance issue, Navigation does everything
Var student=Context.Students.Where(x=>x.Id==UserId).singleOrDefault();
var Courses=Student.Courses.Tolist();


StudentCourseViewModel vm=new StudentCourseViewModel ();
Vm.Courses=Courses;
Vm.Student=Student;



/// in view
@Model.Student.Name
Foreach(var item in Model.Courses)

{

  @item.Name
  foreach(var teacher in item.Teachers)
{
   @teacher.Name
}
}
+1