Left join via SelectMany + DefaultIfEmpty returns null outer entity instead of just null inner
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
### Bug description
Projecting an anonymous type that captures both the outer entity and the inner element inside `SelectMany(s => s.Nav.Take(1).DefaultIfEmpty().Select(f => new { s, f }))` returns rows in which the outer entity `s` is null, even though `s` comes from the outer table and its navigation is a required, non-nullable reference. The nulls occur on exactly the rows whose inner collection is empty, and the equivalent `Select(s => new { s, s.Nav.Take(1).FirstOrDefault() })` query over the same data materializes all 40 outer entities correctly
("equivalent" in terms of results, not in terms of the same translated query)
This occurs in both EFC 10.0.11, and the EFC 11.0.0-preview.7.26381.103
Related to what was discussed in #30450
### Your code
Full MRE:
https://github.com/AlmightyMRE/EFC_DefaultIfEmpty_Bug/blob/main/Program.cs
```csharp
var ids = await db.OCR.Where(x => x.Status == ProcessStatus.Pending).Select(x => x.Id).Take(40).ToListAsync();
var query = db.OCR.Where(x => ids.Contains(x.Id));
var broken = await query.SelectMany(s => s.FileData.UsedByFiles.Take(1).DefaultIfEmpty().Select(f => new { FileOcr = s, File = f })).ToListAsync();
var control = await query.Select(s => new { FileOcr = s, File = s.FileData.UsedByFiles.Take(1).FirstOrDefault() }).ToListAsync();
Console.WriteLine($"SelectMany+DefaultIfEmpty: rows {broken.Count}, {broken.Count(q => q.FileOcr is null)} FileOcr nulls, {broken.Count(q => q.File is null)} File nulls");
Console.WriteLine($"Select+FirstOrDefault: rows {control.Count}, {control.Count(q => q.FileOcr is null)} FileOcr nulls, {control.Count(q => q.File is null)} File nulls");
```
Output:
```
SelectMany+DefaultIfEmpty: rows 40, 14 FileOcr nulls, 14 File nulls
Select+FirstOrDefault: rows 40, 0 FileOcr nulls, 14 File nulls
```
### EF Core version
10.0.11
### Database provider
Microsoft.EntityFrameworkCore.SqlServer
### Target framework
.NET 10
### Operating system
Windows 11
### IDE
JetBrains Rider
Contributor guide
Assessment
This issue has not been assessed yet.