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

c# entity-framework left-join


I have the following tables

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

I want to retrieve all the information on student==1

In sql I would do something like BELOW and get all the info 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

Now my problem.Using EF4 I did something like this but cannot make it work. Also can you do an include and a left join

Attempt 1

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;

Attempt 2 again incomplete and wrong

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 dont know what I am doing here. How can I convert that Sql into a EF Query?

8/27/2016 8:40:37 PM

Accepted Answer

Yes, it is possible.

Firstly, .Include does a LEFT OUTER JOIN, using the navigational property you pass through.

This is how you would explicitly do a LEFT JOIN between Student and StudentDescription:

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, it's performing the JOIN based on the entity association between Students and StudentDescriptions. In your EF model, you should have a navigational property called StudentDescriptions on your Student entity. The above code is simply using that to perform the join, and defaulting if empty.

The code is basically identical to .Include.

Please don't get confused with LEFT JOIN vs LEFT OUTER JOIN.

They are the same thing.

The "OUTER" keyword is optional, i believe it is there for ANSI-92 compatability.

Just .Include everything you need in your query:

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

Basically, make sure all your FK's are expressed as navigational properties on your model, then if so, you don't need to do any joins. Any relationships you require can be done with .Include.

11/29/2010 12:51:01 AM

Popular Answer

I just had this problem, in my case it was the EntityTypeConfiguration that was wrong

I had:

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

Instead of:

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

It seems HasRequired makes a INNER JOIN while HasOptional makes a LEFT 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