Tables having one to many relationship generates outer join

Viewed 32

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?

1 Answers

You could try out a few things

  • Is the FK relation made required or optional? Use HasRequired or .IsRequired(true) if the relation is required. Required relations might resolve to inner join. But the if type of property is collection , it might translate to LEFT JOIN

  • The 2nd option is to add a null check in the condition to filter out empty record and it essentially translates to INNER JOIN.

Related