c# - 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.