how to get related data from second table based on the value of first

Mark Tabor

I have two tables student_courses and student table

student_ID	Email	NAme	Password
1	[email protected]	TABOR	123123
2	[email protected]	MARK	123123

Second I have student Courses table

id	student_ID	course_Id	year	program
1	1	1	1	2
2	1	2	1	2

now i want to get the email of all students who is having year 1 and program 2 below is my code by which i am getting data from student courses table , but it did not have email , email field is in student table how to achieve that .

return std.Student_courses.Where(x => x.Program_Id == program && x.Year_Id == year ).ToList();

4 Replies

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

Sachin Singh5y ago
Accepted answer
use joins
var res=(from s in Students
join sc in Student_Courses
on s.Id equals sc.Student_Id
where sc.Program_Id=-2 && sc.Year_Id==2
select new
{
  Email=s.Email
}).ToList();
List EmailList=new List();
foreach(var item in res)
{
  EmailList.Add(item.Email);
}
Mark Tabor5y ago
Although I marked the answer is there a simpler way to just add include property before where class in my existing query and than get email id from there as with include my query will return the columns of both models.

return std.Student_courses.Where(x => x.Program_Id == program && x.Year_Id == year && x.Module_Id == module && x.Block_Id == block ).ToList();

how to add include to the above query
Mark Tabor5y ago
it was my mistake
Mark Tabor5y ago
it shows zero records while my previous query has two rows.