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

[AlmightyLks]

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

    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

[MihuBot]

I'm a bot. Here is a possible related and/or duplicate issue (I may be wrong):

• Logic for lifting DefaultIfEmpty out of SelectMany (to LEFT JOIN/OUTER APPLY) is incorrect #33343

[AndriySvyryd]

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.
 

Conclusion

 
Confirmed as a longstanding query bug and an exact duplicate of #19095. The generated OUTER APPLY incorrectly moves the projected outer entity columns into the nullable inner subquery. When the inner collection is empty, SQL returns NULL for those columns and EF consequently materializes the outer entity as null.

 

Reproduction

 
Reproduced against SQL Server with a minimized model containing two outer rows, only one of which has an inner row.
 

minimal repro

  ```csharp using Microsoft.EntityFrameworkCore;   await using (var context = new Context()) { await context.Database.EnsureDeletedAsync(); await context.Database.EnsureCreatedAsync();   var first = new Outer(); var second = new Outer(); second.Inners.Add(new Inner()); context.AddRange(first, second); await context.SaveChangesAsync(); }   await using (var context = new Context()) { var results = await context.Outers .OrderBy(o => o.Id) .SelectMany(o => o.Inners.Take(1).DefaultIfEmpty().Select(i => new { Outer = o, Inner = i })) .ToListAsync();   Console.WriteLine( $"Rows: {results.Count}; " + $"null outers: {results.Count(r => r.Outer is null)}; " + $"null inners: {results.Count(r => r.Inner is null)}"); }   sealed class Context : DbContext { public DbSet Outers => Set();   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer( Environment.GetEnvironmentVariable("Test__SqlServer__DefaultConnection")); }   sealed class Outer { public int Id { get; set; } public ICollection Inners { get; } = new List(); }   sealed class Inner { public int Id { get; set; } public int OuterId { get; set; } } ```  

 

Observed:
 

Rows: 2; null outers: 1; null inners: 1

 
Expected:
 

Rows: 2; null outers: 0; null inners: 1

 
The bug reproduces with or without Take(1).
 

Affected versions

 
The minimized repro produced the same incorrect result with:
 

• EF Core 8.0.11
• EF Core 9.0.11
• EF Core 10.0.11
• EF Core 11.0.0-preview.7.26381.103
   
  This is not a recent regression. The duplicate DefaultIfEmpty applied on child collection wipes the parent info in query result #19095 reported the same behavior in EF Core 3.1 preview 3.
   

Technical analysis

 
EF Core 10.0.11 generates SQL equivalent to:
 

SELECT [i0].[Id], [i0].[Id0], [i0].[OuterId]
FROM [Outers] AS [o]
OUTER APPLY (
SELECT TOP(1) [o].[Id], [i].[Id] AS [Id0], [i].[OuterId]
FROM [Inner] AS [i]
WHERE [o].[Id] = [i].[OuterId]
) AS [i0]
ORDER BY [o].[Id]

 
[o].[Id] is selected inside the OUTER APPLY and then read as [i0].[Id]. It is therefore NULL when the apply has no matching inner row. The outer entity should instead be shaped from columns projected directly from [o].
 
This is the same faulty SQL shape described in #19095:
 

SELECT [t].[Id], ...
FROM [Membre] AS [m]
OUTER APPLY (
SELECT [m].[Id], ...
) AS [t]

 
The current DefaultIfEmpty lifting logic allows lifting through Select. In this query, that Select captures the outer entity inside the collection selector. The resulting projection is treated as the nullable side of the apply, so the captured outer entity shaper is also made nullable. The relevant logic is in RelationalQueryableMethodTranslatingExpressionVisitor.
 

Related issues

 

• #19095: exact duplicate; same LINQ shape, result corruption, and generated SQL defect.
• #33343: related DefaultIfEmpty lifting bug fixed by Fix DefaultIfEmpty lifting logic within SelectMany #36248, but it concerns a nested DefaultIfEmpty with different semantics.
• #30450: related query-shape/performance discussion, but not a duplicate.
• #30915 and #22517: related nullable-side projection materialization problems, but they concern non-entity projections rather than outer entity columns being moved into the nullable side.