Left join using Include() in EF 6 LINQ

Question

I am using Include() method in LINQ to join two tables (Student and StudentLibrary) with StudentId as foreignKey. It is working as inner join, however I need to do the left join using Include().

var result = dbcontext.Student
             .Include(x=> x.StudentLibrary).DefaultIfEmpty() 
             .Select(x=> Mapper.Map<StudentModel>(x))
             .ToListAsync();

Below is my model

public class StudentModel
{
     public int Id {get; set;}

     public string Name {get; set;}

     public StudentLibraryModel StudentLibraryInfo {get; set;}
}

[Table("Student")]
public class Student
{
     [key]
     public int Id {get; set;}

     public string Name {get; set;}

     [ForeignKey("StudentId")
     public StudentLibrary StudentLibraryInfo {get; set;}
}

The Student entity is mapped to StudentModel. The case where a student does not have StudentLibrary details, the Include() method does not return that record and act as inner join. How can I make Include() behave as left join. I tried using DefaultIfEmpty() but its not working.

Probably https://stackoverflow.com/questions/4290802/entity-framework-include-with-left-join-is-this-possible helps? — Timothy G., Sep 22 '22 at 13:48
What happens in `Mapper.Map(x)`? And which query is generated when yo only run `dbcontext.Student.Include(x=> x.StudentLibrary)`? — Gert Arnold, Sep 22 '22 at 14:58
`Include` is (apparently) already an outer join - what happens if you leave off the `DefaultIfEmpty` (which isn't doing what you think anyway)? (Basically you are doing `dbcontext.Student.DefaultIfEmpty()` while possibly confusing EF... BTW, what is the type of the foreign key field in `Student` that matches to `StudentLibrary`? — NetMage, Sep 22 '22 at 20:36
One detail is you are missing an `await` on that `ToListAsync` call. As for that, Selecting Student /w `.Include(x => x.StudentLibrary)` would not be triggering a INNER JOIN condition. If you are using Automapper I would also recommend using `ProjectTo` in the Linq expression rather than fetching a Student and then calling `Map`. This would negate the need for `Include` entirely and generate a more efficient query. — Steve Py, Sep 23 '22 at 01:43

score 0 · Answer 1 · answered Sep 23 '22 at 05:40

0

The link shared by Timothy G. does have an answer.

You should set the foreign key as nullable on both the database table and the entity class.

answered Sep 23 '22 at 05:40

Soft_API_Dev

271
1
3
12

Left join using Include() in EF 6 LINQ

1 Answers1