I have 2 tables
public class Department : DbContext
{
public int DeptId {get; set;}
public string DeptName {get; set;}
public virtual List<Student> Students {get; set;}
}
public class Student: DbContext
{
public int StudentId {get; set;}
public string StudentName {get; set;}
public int DeptId {get; set;}
public virtual Department Department {get; set;}
}
So a department can have multiple students But I want to join and convert all the data into below structure
public class CollegeData
{
public int DeptId {get; set;}
public string DeptName {get; set;}
public List<StudentData> Students {get; set;}
}
public class StudentData
{
public int StudentId {get; set;}
public string StudentName {get; set;}
}
I wrote the below query to get the joined data
var data = (from dept in _dbContext.Department
select new CollegeData
{
DeptId = dept.DeptId,
DeptName = dept.DeptName,
Students = (from student in _dbContext.Student
where student.DeptId == dept.DeptId
select new StudentData
{
StudentId = student.StudentId,
StudentName = student.StudentName
}).ToList()
}).ToList();
But when I am profiling this, it is creating left join query. I will need inner join query in this use case
Can somebody give me a direction for this?