Referencing same collection navigation multiple times in projection causes duplicate joins in query
- 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
Assessment
This issue has not been assessed yet.