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

c# entity-framework left-join

Question

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
                .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;
    }

A second unsuccessful and partial attempt

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 have no idea what I'm doing. How can I turn that SQL code into an EF Query?

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

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 .

34
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)
                .WithMany()
                .HasForeignKey(student => student.ClassRoomId);

in place of

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

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



Related Questions





Related

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