Efcore: ThenInclude as a Left Join

[travisjs]

Describe what is not working as expected.
I would expect someway to use ThenInclude as a LEFT JOIN in SQL. Right now it is not.

How can this be achieved now?

Further technical details

EF Core version: 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.5.6

[smitpatel]

EF Core optimizes the join based on relationship configuration.
For parent to child entity join since parent can have no child too, it uses left join.
For child to parent entity join if the FK is non-nullable then each child must have a parent hence we use inner join instead of left join.

Include/ThenInclude APIs are for loading related data.
If you just want to have join in your SQL then you can manually write join in your linq query.
If you are trying to load related data but EF core is generating inner join instead of left join then configuration of the relationship in EF model is incorrect. Correct the EF model to match database and then it would work correctly.

Having mismatch between EF model & database schema is prone to cause issues.

[travisjs]

What is the "correct" way to model an Include/ThenInclude scenario to get a left join?

[smitpatel]

For parent to child entity join since parent can have no child too, it uses left join.
For child to parent entity join if the FK is non-nullable then each child must have a parent hence we use inner join instead of left join.

[smitpatel]

@travisjs - Share your model entities and relationship configuration and tell us where are you expecting Left join. Without that info, it is hard for us to predict what is exactly misconfiguration in the model.

[shaahryarrazzak]

Very simple.

Make your reference id nullable (question mark in C#) in your model and ThenInclude will behave like left join.

Make your reference id not nullable in your model and ThenInclude will behave like inner join.