dotnet / dotnet/efcore

Left join via SelectMany + DefaultIfEmpty returns null outer entity instead of just null inner

Open
#38,813 2 comments 0 reactions 1 assignee Claimed by @AndriySvyryd View on GitHub
area-query customer-reported
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

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.