Entity Framework comes with a left join feature. Is it even possible?

c# entity-framework left-join


I have the tables below.

  1. ClassRoom (ClassID,ClassName)
  2. StudentClass (StudentID,ClassID)
  3. Student (StudentID,StudentName,Etc..)
  4. StudentDescription (StudentDescriptionID,StudentID,StudentDescription)

I want to get every bit of data about student==1.

I would enter the following in sql to get all the information about a student.

 select * from Student s
 join StudentClass sc on s.StudentID=sc.StudentID
 join ClassRoom c on sc.ClassID=c.ClassID
 left join StudentDescription sd on s.StudentID=sd.StudentID
 where s.StudentID=14

My issue now. I attempted to do something similar using EF4 but was unsuccessful. Can you also do an inclusion and a left join?

1st attempt

private static StudentDto LoadStudent(int studentId)
        StudentDto studentDto = null;
        using (var ctx = new TrainingContext())
            var query = ctx.Students

            studentDto = new StudentDto();
            studentDto.StudentId = query.StudentID;
            studentDto.StudentName = query.StudentName;
            studentDto.StudentDescription = ??


        return studentDto;

A second unsuccessful and partial attempt

using (var ctx = new TrainingContext())
             var query = (from s in ctx.Students
                         join sd in ctx.StudentDescriptions on s.StudentID equals sd.StudentID into g
                         from stuDesc in g.DefaultIfEmpty()
                         select new


As you can see, I have no idea what I'm doing. How can I turn that SQL code into an EF Query?

8/27/2016 8:40:37 PM

Accepted Answer

It is indeed feasible.

Firstly, .Include Using the a navigational asset you pass through, conducts an LEFT OUTER JOIN.

In order to explicitly do an LEFT JOIN between Student and StudentDescription, follow these steps:

var query = from s in ctx.Students
            from sd in s.StudentDescriptions.DefaultIfEmpty()
            select new { StudentName = s.Name, StudentDescription = sd.Description };

As you can see, the entity association between Students and StudentDescriptions is being used to conduct the JOIN. On your Student entity in your EF model, there should be a navigational property with the name StudentDescriptions. The aforementioned code only uses it to carry out the join and defaults if empty.

The code is essentially equivalent to.Include .

Please avoid confusion between RIGHT JOIN and OUTER LEFT JOIN..

They are interchangeable.

I assume the "OUTER" term is included for ANSI-92 compatibility, although it is optional.

Just .Include the whole of your inquiry:

using (var ctx = new TrainingContext())
            studentDo = ctx.Students
                .Select(x => new StudentDto
                            StudentId = x.StudentId,
                            StudentName = x.StudentName
                            StudentDescription = x.StudentDescription.Description

In short, if all of your FKs are specified on your model as navigational properties, you won't need to do any joins. Any connections you need may be made using.Include .

11/29/2010 12:51:01 AM

Popular Answer

I recently encountered this issue, and in my case the EntityTypeConfiguration was incorrect.

I had:

   HasRequired(s => s.ClassRoom)
                .HasForeignKey(student => student.ClassRoomId);

in place of

   HasOptional(s => s.ClassRoom)
                .HasForeignKey(student => student.ClassRoomId);

While HasOptional seems to produce an LEFT JOIN, HasRequired appears to make an INNER JOIN.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow