LINQ FullJoin in .NET 11: Find Missing Rows on Both Sides

.NET 11 adds Enumerable.FullJoin, a full outer join for LINQ to Objects. It pairs elements with equal keys and keeps every element that has no match on the other side. In a stock report, that lets you distinguish a product with zero units from one that's missing from the warehouse inventory.

Which Rows Would a Left Join Miss?

Say a warehouse sends a stock file and the storefront exports its catalog. SKU A100 appears in both, with different quantities.

The warehouse lists B200 with zero units. The storefront still offers six units of C300, which the warehouse doesn't list at all.

A left join starting from the warehouse tells you about A100 and B200. It drops C300. Starting from the storefront picks up C300 but drops B200.

The left and right joins introduced in .NET 10 work when one side lists every SKU you care about.

With these snapshots, FullJoin returns four pairs, including D400, whose quantities agree. For B200, zero is the count in the warehouse. The null on the storefront side means there is no row to compare it with.

Reconcile Two Snapshots With FullJoin

Save this as StockDiff.cs and run dotnet run --file StockDiff.cs with the .NET 11 SDK. For an existing project, target .NET 11 to use the new LINQ method. Each source uses its own casing convention, so the call supplies an ordinal, case-insensitive key comparer.

#!/usr/bin/env dotnet

Stock[] warehouse = [new("A100", 8), new("B200", 0), new("D400", 5)];
Stock[] storefront = [new("a100", 10), new("C300", 6), new("D400", 5)];

var pairs = warehouse.FullJoin(
    storefront,
    item => item.Sku,
    item => item.Sku,
    StringComparer.OrdinalIgnoreCase);

foreach (var (stored, listed) in pairs)
{
    if (stored is null)
    {
        Console.WriteLine($"{listed!.Sku}: storefront only ({listed.Units})");
    }
    else if (listed is null)
    {
        Console.WriteLine($"{stored.Sku}: warehouse only ({stored.Units})");
    }
    else if (stored.Units != listed.Units)
    {
        Console.WriteLine($"{stored.Sku}: warehouse {stored.Units}, storefront {listed.Units}");
    }
}

sealed record Stock(string Sku, int Units);

The join returned four pairs, but the loop prints only the three discrepancies. Both sides report five units for D400, so it produces no output.

A100: warehouse 8, storefront 10
B200: warehouse only (0)
C300: storefront only (6)

The storefront value in the B200 pair is null because Stock is a record class. The warehouse value is a Stock whose Units is zero.

The tuple-returning overload of FullJoin fills the unmatched side with its type's default value. If you join two sequences of integers, you get zero for a missing reading as well as a measured zero.

What Happens With Duplicate or Null Keys?

Suppose A100 appears twice in the warehouse file because the export has one row per bin. If the storefront also lists A100 three times, one per sales channel, FullJoin emits six matched pairs. A later Sum over warehouse units would count each bin three times.

For a report with one row per SKU, sum the warehouse bins before joining. On the storefront side, sum the channels only if each reports its own allocation. If each channel reports the same shared stock, adding those numbers would overstate the quantity available to sell.

If the file is supposed to have unique SKUs, reject duplicate keys before comparing quantities.

A missing key is different from a missing row. The LINQ to Objects implementation returns two unmatched rows when both SKUs are null, even if their quantities agree. Report those rows separately if you have no other identifier to match them on.

How Much Does FullJoin Keep in Memory?

Assigning the query to pairs doesn't enumerate either sequence. When the loop starts and both inputs contain rows, FullJoin builds a lookup over the second sequence. It tracks which groups matched so it can return the remaining storefront rows at the end.

If the storefront export is several gigabytes, reading it a line at a time still leaves its rows in the lookup. If the warehouse export is smaller, pass it as the second sequence and swap the labels in the report.

Can EF Core 11 Run FullJoin in the Database?

Yes, EF Core 11 translates FullJoin on relational providers that support a SQL full join. EF Core 11.0.0's SQLite provider couldn't translate the tuple-returning overload used above. Give it a result selector that projects the warehouse and storefront rows.

Given two entity sets named Warehouse and Storefront, each with a Sku key and a Units column, you can inspect the translation without executing the query.

var results = db.Warehouse.FullJoin(
    db.Storefront,
    stored => stored.Sku,
    listed => listed.Sku,
    (stored, listed) => new { Stored = stored, Listed = listed });

Console.WriteLine(results.ToQueryString());

With EF Core 11.0.0 and SQLite, that produces the following SQL.

SELECT "w"."Sku", "w"."Units", "s"."Sku", "s"."Units"
FROM "Warehouse" AS "w"
FULL JOIN "Storefront" AS "s" ON "w"."Sku" = "s"."Sku"

The SQL compares the two Sku columns directly. The column collation decides whether their casing matters. If you want A100 to match a100 as it did in memory, use a case-insensitive collation or normalize both keys on import.

For a query that selects the row with the highest or lowest value, the EF Core 11 query guide compares the SQL and its execution plan before and after adding an index.

Summary

Keep B200 in the regression test and assert that the report contains warehouse only (0). If someone later substitutes zero for a missing storefront count, both sides will appear to agree and that discrepancy will disappear.