dotnet / dotnet/efcore

Referencing same collection navigation multiple times in projection causes duplicate joins in query

Open
#29,608 2 comments 0 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

```csharp
public class Product{
[...]

public ICollection Categories {get; set}
}

public class Category{
[...]

public ICollection Products {get; set}
}
```
---
```csharp
Db.Categories!
.Where(c => [...])
.Select(c=> new {
[...]
Products = c.Products
}).ToList();
```
this produces a regular query:
```sql
SELECT [...]
FROM [Categories] AS [c]
LEFT JOIN (
[...]
) AS [t0] ON [...]
WHERE [...]
ORDER BY [...]
```
---

While this:
```csharp
Db.Categories!
.Where(c => [...])
.Select(c=> new {
[...]
Products = c.Products,
Products2 = c.Products
}).ToList();
```
produces a second join, even if the collection is the same:
```sql
SELECT [...]
FROM [Categories] AS [c]
LEFT JOIN (
[...]
) AS [t0] ON [...]
LEFT JOIN (
[...]
) AS [t1] ON [...]
WHERE [...]
ORDER BY [...]
```

This happens with Owned entities collections and regular collections.
If the above is performed AsSplitQuery it loads the collection twice with two separate commands.

### Include provider and version information

EF Core version: 6.0.3
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.