Entity Framework include with left join is this possible?

Question

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
                .Include("ClassRooms")
                .Include("StudentDescriptions")
                .Where(x=>x.StudentID==studentId)
                .SingleOrDefault();

            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
                             .Include("ClassRooms")
                         join sd in ctx.StudentDescriptions on s.StudentID equals sd.StudentID into g
                         from stuDesc in g.DefaultIfEmpty()
                         select new
                                    {
                                        Name=s.StudentName,
                                        StudentId=s.StudentID,

         }).SingleOrDefault();

As you can see I dont know what I am doing here. How can I convert that Sql into a EF Query?

Answer 1

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.

Answer 2

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.

Answer 3

Exactly:

1. If StudentDescription.StudentId is nullable -> EF performs a LEFT JOIN, i.e. select * from Student s LEFT JOIN StudentDescription sd on s.StudentID=sd.StudentID.
2. Otherwise EF does INNER JOIN.

Answer 4

The behavior of .Include:

• The property is requeired: Always translates to INNER JOIN;
• The type of foreign key is not nullable: translates to INNER JOIN in default, but you can add .IsRequired(false) with .HasForeignKey to turn it to be LEFT OUT JOIN.
• The type of property is collection will always translates to LEFT OUT JOIN.

Answer 5

If you want a 1 to 1 relation, you can simply map your foreign Id and make it nullable.

public int? MyForeignClassId { get; set; }
public MyForeignClass MyForeignClass { get; set; }