EF core is using LEFT JOIN instead of inner join
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
I was using ToQueryString of EF Core and was faced with this use case that looks strange to me:
I have a Backpack table with this data:

As you can see, three records have students and three of them haven't. So I am creating this query:
```
var query = context.Set()
.Where(s => s.Student != null)
.Select(s => s.Student.Name);
```
And then getting SQL-query using `ToQueryString`: `var sql = query.ToQueryString();`
SQL query:
```
SELECT
[s].[Name]
FROM
[Backpack] AS [b]
LEFT JOIN [Student] AS [s] ON [b].[StudentId] = [s].[Id]
WHERE
[s].[Id] IS NOT NULL
```
The query is returning the expected result, but as you can see it is using LEFT JOIN and then adding the condition: WHERE [s].[Id] IS NOT NULL
Why it is not using INNER JOIN instead of that? Like:
```
SELECT
[s].[Name]
FROM
[Backpack] AS [b]
INNER JOIN [Student] AS [s] ON [b].[StudentId] = [s].[Id]
```
Model configuration:
```
public class MyContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("data source=localhost;integrated security=True;Database=InnerTest;Trusted_Connection=True;MultipleActiveResultSets=true;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var student = modelBuilder.Entity();
student.HasKey(x => x.Id);
var backpack = modelBuilder.Entity();
backpack.HasKey(x => x.Id);
backpack.HasOne(x => x.Student).WithMany(s => s.Backpacks).HasForeignKey(x => x.StudentId);
}
}
public class Student
{
public Guid Id { get; set; }
public string Name { get; set; }
public virtual ICollection Backpacks { get; set; }
}
public class Backpack
{
public Guid Id { get; set; }
public string Color { get; set; }
public Guid? StudentId { get; set; }
public virtual Student Student { get; set; }
}
```
I think it should use INNER JOIN instead of LEFT JOIN and WHERE statement.
EF Core version: 6.0.9
SQL Server version: 15.0.2000.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 6.0
Contributor guide
Assessment
This issue has not been assessed yet.