dotnet / dotnet/efcore

EF core is using LEFT JOIN instead of inner join

Open
#29,305 3 comments 3 reactions 0 assignees View on GitHub
area-perf area-query customer-reported
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:

![image](https://user-images.githubusercontent.com/50167116/194825207-59b8b7de-4ab1-413f-894a-c11ec1f609fa.png)

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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.