Entity Framework include with left join is this possible?

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

I had:

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

Instead of:

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

It seems HasRequired makes a INNER JOIN while HasOptional makes a LEFT JOIN.

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
                .Include("ClassRooms")
                .Include("StudentDescriptions")
                .Where(x=>x.StudentID==studentId)
                .Select(x => new StudentDto
                        {
                            StudentId = x.StudentId,
                            StudentName = x.StudentName
                            StudentDescription = x.StudentDescription.Description
                        })
                .SingleOrDefault();
        }

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.