Joining data from two sources is one of the most common operations in application development.
In a typical .NET application, you may need to combine:
LINQ has traditionally provided Join, GroupJoin, and patterns that developers could use to construct left or right outer joins.
.NET 11 expands the LINQ join capabilities with first-class LeftJoin, RightJoin, and FullJoin operations. These APIs are available for Enumerable, Queryable, and AsyncEnumerable.
For database-backed applications, the change is particularly interesting because EF Core 11 can translate FullJoin into a database-level FULL JOIN.
That raises an important practical question:
Does the new LINQ syntax merely make code easier to read, or can it also produce a better database query?
The answer depends on the data source, query shape, database provider, indexes, and execution plan.
What Is a Full Outer Join?
A full outer join returns:
Matching rows from both sources.
Rows that exist only in the left source.
Rows that exist only in the right source.
Consider two datasets.
Customers:
Orders:
| Id | CustomerId | Amount |
|---|
| 101 | 1 | 100 |
| 102 | 2 | 250 |
| 103 | 5 | 500 |
A full outer join produces:
| Customer | Order | Result |
|---|
| Alice | 101 | Match |
| Bob | 102 | Match |
| Charlie | null | Left only |
| null | 103 | Right only |
This is different from an inner join, which would return only Alice and Bob.
Why Full Outer Joins Are Useful
A full outer join is particularly useful when the application needs to identify differences between two datasets.
Examples include:
Expected Products
|
v
Actual Inventory
|
v
Full Join
|
+--> Matching
+--> Missing Inventory
+--> Unexpected Inventory
This makes full joins useful for:
FullJoin in .NET 11
.NET 11 adds a first-class FullJoin LINQ operator.
A simplified in-memory example is:
var results = customers.FullJoin(
orders,
customer => customer.Id,
order => order.CustomerId,
(customer, order) => new
{
Customer = customer,
Order = order
});
The result preserves elements from both sequences.
If there is no corresponding element on one side, the corresponding value is default.
Microsoft documents the new join operations as part of the .NET 11 LINQ improvements.
FullJoin vs Join
The difference is important.
A normal Join returns only matching elements.
var results = customers.Join(
orders,
customer => customer.Id,
order => order.CustomerId,
(customer, order) => new
{
Customer = customer,
Order = order
});
Conceptually:
Customers Orders
| |
+---- Match ---+
|
v
Results
A full join instead preserves unmatched records:
Customers Orders
| |
+---- Match ---+
| |
v v
Left only Right only
\ /
\ /
FullJoin
|
v
Results
FullJoin vs LeftJoin
A left join preserves every record from the left sequence.
var results = customers.LeftJoin(
orders,
customer => customer.Id,
order => order.CustomerId,
(customer, order) => new
{
Customer = customer,
Order = order
});
If an order has no matching customer, it is not included as a left-join result.
With FullJoin, it is.
| Join Type | Left-only | Matching | Right-only |
|---|
| Inner Join | No | Yes | No |
| Left Join | Yes | Yes | No |
| Right Join | No | Yes | Yes |
| Full Join | Yes | Yes | Yes |
This makes the intent much clearer when the business requirement is genuinely a full outer join.
FullJoin With Entity Framework Core
The feature becomes particularly interesting when working with EF Core.
For example:
var results = await context.Customers
.FullJoin(
context.Orders,
customer => customer.Id,
order => order.CustomerId,
(customer, order) => new
{
Customer = customer,
Order = order
})
.ToListAsync();
EF Core 11 translates this query to a database FULL JOIN for relational providers that support the operation. Microsoft documents the generated SQL as being equivalent to:
SELECT
[c].[Id],
[c].[Name],
[o].[Id],
[o].[CustomerId],
[o].[OrderDate]
FROM [Customers] AS [c]
FULL JOIN [Orders] AS [o]
ON [c].[Id] = [o].[CustomerId]
This is significant because the join can be executed by the database rather than reconstructing the operation in application memory.
Why SQL Translation Matters
Consider two approaches.
Database-Side Join
Database
|
+-- Customers
|
+-- Orders
|
v
FULL JOIN
|
v
Filtered Results
|
v
Application
Application-Side Join
Database
|
+-- Customers ---> Application
|
+-- Orders ------> Application
|
v
Join
The second approach can require transferring significantly more data.
For large tables, that can increase:
Network traffic
Application memory
Serialization cost
CPU consumption
Query latency
A database-side join allows the database engine to use its optimizer and available indexes.
Queryable vs Enumerable
One important LINQ concept is the difference between Enumerable and Queryable.
Enumerable generally operates on objects already in memory:
IEnumerable<Customer> customers;
Queryable builds an expression tree that a provider can translate:
IQueryable<Customer> customers;
For EF Core:
var query = context.Customers
.FullJoin(
context.Orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
Customer = c,
Order = o
});
The query remains database-oriented until materialization:
var results = await query.ToListAsync();
This distinction is critical when evaluating performance.
Do Not Call ToList Too Early
Avoid:
var customers =
await context.Customers.ToListAsync();
var orders =
await context.Orders.ToListAsync();
var results = customers.FullJoin(
orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
Customer = c,
Order = o
});
This moves both datasets into application memory.
The database can no longer perform the join.
Prefer keeping the operation queryable:
var results = await context.Customers
.FullJoin(
context.Orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
Customer = c,
Order = o
})
.ToListAsync();
This allows EF Core to translate the operation.
Filtering Before the Join
Filtering can reduce the amount of data involved in the operation.
For example:
var results = await context.Customers
.Where(c => c.IsActive)
.FullJoin(
context.Orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
Customer = c,
Order = o
})
.ToListAsync();
However, do not assume that moving every filter before the join automatically improves the query.
The resulting SQL and execution plan should be inspected.
Query optimization is provider- and workload-dependent.
Projection Matters
Avoid retrieving entire entities when the report requires only a few columns.
For example:
var results = await context.Customers
.FullJoin(
context.Orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
CustomerId = c == null
? null
: c.Id,
CustomerName = c == null
? null
: c.Name,
OrderId = o == null
? null
: o.Id,
Amount = o == null
? null
: o.Amount
})
.ToListAsync();
A focused projection can reduce the amount of data returned from the database.
The exact SQL should still be inspected.
Handling Null Values
Full joins naturally produce missing values.
For example:
foreach (var result in results)
{
if (result.Customer is null)
{
Console.WriteLine(
$"Order {result.Order?.Id} has no customer.");
}
if (result.Order is null)
{
Console.WriteLine(
$"Customer {result.Customer?.Id} has no order.");
}
}
Do not treat missing values as exceptional unless the business rule requires it.
An unmatched row is an expected outcome of a full outer join.
A Reconciliation Example
Suppose an organization needs to compare customer records with an external billing system.
Internal customers:
C001
C002
C003
Billing customers:
C001
C002
C004
A full join makes the differences visible:
C001 -> Match
C002 -> Match
C003 -> Internal only
C004 -> Billing only
The application can then classify the result:
var reconciliation =
results.Select(x => new
{
CustomerId =
x.Customer?.ExternalId ??
x.Order?.CustomerId,
Status =
x.Customer is not null &&
x.Order is not null
? "Match"
: x.Customer is not null
? "InternalOnly"
: "ExternalOnly"
});
The exact domain model will vary, but the pattern is useful for reconciliation workflows.
Benchmarking FullJoin
A useful benchmark should compare multiple approaches.
For example:
| Approach | Execution Location | Purpose |
|---|
| FullJoin on Enumerable | Application | In-memory baseline |
| FullJoin on Queryable | Database | Provider translation |
| Manual left/right join | Database | Compatibility comparison |
| Materialized collections | Application | Identify data-transfer cost |
The goal is not to prove that one approach is universally faster.
The goal is to determine how each approach behaves for the application's data volume.
Benchmark In-Memory FullJoin
For an in-memory benchmark:
[Benchmark]
public void FullJoinInMemory()
{
var results = customers.FullJoin(
orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
Customer = c,
Order = o
});
_ = results.Count();
}
The benchmark should control:
Number of customers
Number of orders
Match percentage
Duplicate keys
Payload size
For example:
10,000 customers
50,000 orders
70% matching keys
The actual benchmark values should be selected based on the workload being investigated.
Benchmark Database Queries
For EF Core, measure the database query separately.
Important metrics include:
Execution time
Rows returned
Logical reads
CPU
Network transfer
Application allocations
The database execution plan is often more informative than application-level timing alone.
For SQL Server, inspect the actual execution plan and relevant query statistics.
Do not conclude that a LINQ query is inefficient merely because the C# expression looks complex.
The database sees the translated SQL.
Inspect Generated SQL
EF Core allows developers to inspect generated SQL.
For example:
var query = context.Customers
.FullJoin(
context.Orders,
c => c.Id,
o => o.CustomerId,
(c, o) => new
{
Customer = c,
Order = o
});
Console.WriteLine(
query.ToQueryString());
This is extremely useful during development.
You can verify whether the query actually contains the expected join.
For EF Core 11, Microsoft documents FullJoin translation to relational FULL JOIN.
Database Indexes Still Matter
A convenient LINQ API does not eliminate database optimization requirements.
If the join uses:
Customers.Id
Orders.CustomerId
the database should have appropriate keys or indexes for the workload.
For example:
CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);
Whether an additional index is beneficial depends on the existing schema, query workload, database engine, and execution plan.
Do not add indexes blindly.
Use query plans and workload measurements to justify them.
FullJoin With Duplicate Keys
Consider:
Customers
C001
Orders
C001 - Order A
C001 - Order B
A join can produce multiple result rows.
Conceptually:
C001 + Order A
C001 + Order B
This is normal relational behavior.
Therefore, benchmark datasets should include realistic duplicate-key distributions.
Otherwise, a benchmark can underestimate the number of rows produced by the query.
FullJoin and Large Tables
For large tables, consider:
Customer rows
+
Order rows
|
v
FULL JOIN
|
v
Potentially large result
The result itself can be much larger than expected.
Before executing a full join against large production tables, estimate:
Number of input rows
Match rate
Duplicate keys
Expected result size
Required columns
Filtering conditions
A full join is powerful, but it can intentionally return unmatched data from both sides.
When FullJoin Is Not the Right Choice
A full join should not automatically replace other join types.
Use an inner join when only matches matter.
Use a left join when the left side is authoritative and unmatched right-side data is irrelevant.
Use a right join when the reverse is true.
Use a full join when both unmatched sides are meaningful.
Choosing the correct semantic operation is more important than minimizing the number of LINQ operators.
Common Mistakes
Materializing Data Before Joining
Calling:
ToListAsync()
before the join can move the operation into application memory.
Ignoring Generated SQL
A LINQ expression is not the final database query.
Inspect the SQL.
Assuming FullJoin Is Always Faster
The new operator improves expressiveness and can translate directly to FULL JOIN, but performance still depends on schema, indexes, data distribution, provider, and execution plan. Microsoft explicitly notes that actual performance varies with the application and data.
Returning Entire Entities
Large projections can increase network and materialization costs.
Ignoring Duplicate Keys
Duplicate join keys can multiply result rows.
Benchmarking Only Tiny Datasets
Small datasets may hide query-plan and I/O behavior that appears at larger scale.
Troubleshooting
FullJoin Does Not Translate
Check that the query remains an IQueryable and that the database provider supports the required translation.
Also verify the EF Core version.
The Query Is Slow
Inspect:
Generated SQL
Execution plan
Indexes
Rows scanned
Rows returned
Database CPU
Logical reads
Do not optimize the LINQ expression before understanding the database query.
Too Many Rows Are Returned
Check duplicate join keys.
A full join preserves all matching combinations, not simply one result per key.
Memory Usage Is High
Check whether the query was accidentally materialized before joining.
Also review the projection and result size.
Results Contain Null Values
This is expected for unmatched records.
Handle the missing side explicitly.
Best Practices
Use FullJoin when the business requirement is genuinely a full outer join.
Keep EF Core queries as IQueryable until the database operation is complete.
Inspect generated SQL.
Inspect the database execution plan for important queries.
Project only the columns required by the application.
Validate indexes using actual workload evidence.
Test realistic match rates.
Test duplicate-key scenarios.
Measure database and application performance separately.
Do not assume the new LINQ operator is automatically faster.
Handle unmatched records explicitly.
Use integration tests against the actual database provider.
Frequently Asked Questions
What does FullJoin do in LINQ?
FullJoin returns matching elements plus elements that exist only in the left or right sequence. .NET 11 adds first-class FullJoin support to LINQ.
Is FullJoin available for IQueryable?
Yes. .NET 11 provides the join improvements across Enumerable, Queryable, and AsyncEnumerable.
Does EF Core 11 translate FullJoin to SQL?
Yes. EF Core 11 translates FullJoin to a relational FULL JOIN where supported.
Is FullJoin faster than a manually constructed join?
Not universally.
The advantage is that the operation expresses the intended semantics directly and can be translated to a native database full join. Actual performance depends on the provider, schema, indexes, data distribution, and execution plan.
Should I use FullJoin for every comparison?
No.
Use the join type that matches the business requirement. A full join is appropriate when unmatched rows from both sides are significant.
Can FullJoin work with in-memory collections?
Yes. The new LINQ join APIs include Enumerable implementations, so full joins can also be performed over in-memory sequences.
Conclusion
.NET 11's new LINQ join capabilities address a long-standing gap in expressive data querying.
The new FullJoin operation makes the intent of a full outer join explicit:
Left Only
+
Matching
+
Right Only
=
FullJoin
For in-memory collections, it provides a direct LINQ representation of the operation.
For EF Core applications, the feature is even more significant because EF Core 11 can translate the query to a database-level FULL JOIN.
The performance opportunity, however, should be evaluated scientifically.
A useful investigation should follow:
LINQ Query
|
v
Generated SQL
|
v
Execution Plan
|
v
Database Metrics
|
v
Application Metrics
The key principle is:
A better LINQ abstraction does not automatically mean a faster query.
The real benefit comes when the new abstraction expresses the correct relational operation and the database can execute that operation efficiently.
For .NET 11 applications, FullJoin therefore deserves attention not just as a convenient API, but as a new boundary between application-level LINQ and database-level query execution.