Join Two Entities in .NET Core Using Lambda and Entity Framework Core

Entity Framework Core maps each database table to an entity class and gives you a DbContext to query them. Most of the time you read from a single DbSet, but sooner or later you need data that lives across two tables — exactly what a SQL JOIN gives you. In EF Core, the lambda-based Join() method does the same job, and it translates into real SQL that runs on the database server.

Everything below targets .NET 10 and EF Core 10, the current Long Term Support releases.

Prerequisites

Before you start, make sure that you have:

• The .NET 10 SDK installed. EF Core 10 requires the .NET 10 SDK to build and the .NET 10 runtime to run — it does not run on earlier .NET versions or on .NET Framework.
• A project targeting net10.0.
• The EF Core 10 packages installed, plus a database provider:

dotnet add package Microsoft.EntityFrameworkCore --version 10.0.0
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 10.0.0

• A DbContext with at least two related DbSet properties.
• using Microsoft.EntityFrameworkCore; at the top of your file — without it, ToListAsync() will not be available.

The Example Model

Every example below uses two simple entities: a Customer and the Order rows that belong to it.

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Region { get; set; } = string.Empty;
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public string Region { get; set; } = string.Empty;
    public decimal Total { get; set; }
    public DateTime? ShippedDate { get; set; }
}

And the context that exposes them:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
}

Customer.CustomerId is the primary key and Order.CustomerId is the foreign key that points back to it. That is the pair of columns you will join on.

Understand the Join() Method

The lambda overload of Join() takes four arguments, and knowing what each one is for makes every example below easy to read:

Argument	Purpose
Inner sequence	The second table to join, for example _context.Orders.
Outer key selector	The key on the first entity, for example customer => customer.CustomerId.
Inner key selector	The matching key on the second entity, for example order => order.CustomerId.
Result selector	How to shape each matched pair, for example into an anonymous type.

The two key selectors must return the same type. That single rule is behind most compiler errors you will hit.

Join Two Entities and Return Both

Start with the SQL you are reproducing:

SELECT c.*, o.*
FROM Customers AS c
JOIN Orders AS o ON c.CustomerId = o.CustomerId;

The lambda equivalent in EF Core:

var results = await _context.Customers
    .Join(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new
        {
            Customer = customer,
            Order = order
        })
    .ToListAsync();

Each item in results is an anonymous object with a Customer and an Order property, so you can read both sides of the match:

foreach (var row in results)
{
    Console.WriteLine($"{row.Customer.Name} ordered {row.Order.Total:C}");
}

tip

Join() always produces an inner join. Customers with no orders are dropped from the result, exactly as they would be in SQL. Skip ahead to the left join section if you need to keep them.

Return Data From Only One Entity

Often you join purely to filter, and you only want columns from the first table:

SELECT c.*
FROM Customers AS c
JOIN Orders AS o ON c.CustomerId = o.CustomerId;

Add a Select() after the join to keep just that side:

var results = await _context.Customers
    .Join(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new
        {
            Customer = customer,
            Order = order
        })
    .Select(row => row.Customer)
    .ToListAsync();

warning

A customer with five orders appears five times in this result, just like in SQL. Add .Distinct() after the Select(), or filter with Any() instead of joining, when you want one row per customer.

A cleaner alternative when you never need the order columns at all:

var results = await _context.Customers
    .Where(customer => _context.Orders.Any(order => order.CustomerId == customer.CustomerId))
    .ToListAsync();

This translates to an EXISTS subquery and cannot produce duplicates.

Add a Condition to the Join

To filter the joined set, add a Where() before the projection:

SELECT c.*
FROM Customers AS c
JOIN Orders AS o ON c.CustomerId = o.CustomerId
WHERE o.ShippedDate IS NOT NULL;

var results = await _context.Customers
    .Join(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new
        {
            Customer = customer,
            Order = order
        })
    .Where(row => row.Order.ShippedDate != null)
    .Select(row => row.Customer)
    .Distinct()
    .ToListAsync();

Order matters here. Where() runs against the joined shape, so it can reference both entities. Once Select() narrows the result to Customer, the order columns are gone.

Project Only the Columns You Need

Returning full entities pulls every column across the wire. When the result feeds an API response or a view, project into a DTO instead:

public class CustomerOrderDto
{
    public string CustomerName { get; set; } = string.Empty;
    public int OrderId { get; set; }
    public decimal Total { get; set; }
}

var results = await _context.Customers
    .Join(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new CustomerOrderDto
        {
            CustomerName = customer.Name,
            OrderId = order.OrderId,
            Total = order.Total
        })
    .ToListAsync();

EF Core now selects only three columns, and no change tracking entries are created for the results.

Join on More Than One Column

SQL joins on composite keys with an AND. In lambda syntax you return an anonymous type from both key selectors:

var results = await _context.Customers
    .Join(
        _context.Orders,
        customer => new { customer.CustomerId, customer.Region },
        order => new { order.CustomerId, order.Region },
        (customer, order) => new
        {
            Customer = customer,
            Order = order
        })
    .ToListAsync();

The property names, types, and order must match on both sides. new { customer.CustomerId, customer.Region } and new { order.Region, order.CustomerId } are different types and will not compile.

Perform a Left Join

Join() cannot express a LEFT JOIN. Use GroupJoin() followed by SelectMany() with DefaultIfEmpty():

SELECT c.*, o.*
FROM Customers AS c
LEFT JOIN Orders AS o ON c.CustomerId = o.CustomerId;

var results = await _context.Customers
    .GroupJoin(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, orders) => new { customer, orders })
    .SelectMany(
        grouping => grouping.orders.DefaultIfEmpty(),
        (grouping, order) => new
        {
            Customer = grouping.customer,
            Order = order
        })
    .ToListAsync();

Order is null for customers with no matching rows, so guard against that when reading values:

var total = row.Order?.Total ?? 0m;

warning

EF Core recognises this as a left join only when the grouping is flattened in the step immediately following GroupJoin(). Split the two calls apart, or use GroupJoin() on its own, and the query will not be translated — EF Core does not translate GroupJoin by itself.

.NET 10 also adds a dedicated LeftJoin() operator to LINQ, along with RightJoin(), whose result selector receives a nullable inner element:

var results = await _context.Customers
    .LeftJoin(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new
        {
            Customer = customer,
            Order = order
        })
    .ToListAsync();

It is far easier to read than the three-operator pattern. Since translation support depends on your EF Core provider, print ToQueryString() once to confirm you get a LEFT JOIN before relying on it.

Chain a Third Entity

Joins compose. Assuming an OrderLine entity exposed as _context.OrderLines, treat the result of the first join as the outer sequence of the second:

var results = await _context.Customers
    .Join(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new { customer, order })
    .Join(
        _context.OrderLines,
        row => row.order.OrderId,
        line => line.OrderId,
        (row, line) => new
        {
            Customer = row.customer,
            Order = row.order,
            Line = line
        })
    .ToListAsync();

Consider Navigation Properties Instead

If the two entities are related in your model, you rarely need Join() at all. Add a navigation property:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Region { get; set; } = string.Empty;
    public List<Order> Orders { get; set; } = new();
}

Then let EF Core write the join for you:

var customers = await _context.Customers
    .Include(customer => customer.Orders)
    .ToListAsync();

Or project through the relationship without loading everything:

var results = await _context.Customers
    .SelectMany(customer => customer.Orders, (customer, order) => new
    {
        customer.Name,
        order.OrderId,
        order.Total
    })
    .ToListAsync();

Reach for the explicit Join() when there is no relationship configured, when you are joining on non-key columns, or when you need a shape that navigation properties cannot express.

Query Syntax Equivalent

Some teams find the query syntax easier to read for multi-table work. It compiles to the same thing:

var results = await (
    from customer in _context.Customers
    join order in _context.Orders
        on customer.CustomerId equals order.CustomerId
    where order.ShippedDate != null
    select new { Customer = customer, Order = order })
    .ToListAsync();

Inspect the Generated SQL

Before blaming EF Core for slow queries, look at what it actually sends:

var query = _context.Customers
    .Join(
        _context.Orders,
        customer => customer.CustomerId,
        order => order.CustomerId,
        (customer, order) => new { customer, order });

Console.WriteLine(query.ToQueryString());

ToQueryString() works on any IQueryable that has not been executed yet. For continuous visibility, enable logging in OnConfiguring with optionsBuilder.LogTo(Console.WriteLine).

Troubleshooting Common Issues

• The type arguments cannot be inferred from the usage — The two key selectors return different types. Compare them carefully, including nullability: int and int? are not the same. Cast one side, for example customer => (int?)customer.CustomerId.
• A lambda parameter is not recognised — Each lambda declares its own parameter. If you write order => customer.CustomerId, the body references a variable that lambda never declared. Keep the parameter and the body consistent.
• ToListAsync does not exist — Add using Microsoft.EntityFrameworkCore;. The async operators are extension methods from that namespace.
• Duplicate rows in the result — Expected behaviour for a one-to-many join. Add .Distinct(), or use Any() to filter instead of joining.
• Cannot convert lambda expression to type 'string' — You called an overload that takes a string, usually because a Select() or Where() was chained in the wrong position. Check that the projection comes after the filter.
• The LINQ expression could not be translated — Something in the query has no SQL equivalent, such as a call to a local method. Move that logic after ToListAsync() so it runs in memory.
• Everything is slow — You are probably materialising full entities. Project into a DTO and add AsNoTracking() for read-only queries.

Frequently Asked Questions

Does Join() produce an inner join?

Yes. The lambda Join() method translates to a SQL INNER JOIN, so rows without a match on either side are excluded.

How do I write a left join in Entity Framework Core?

Use GroupJoin() followed immediately by SelectMany() with DefaultIfEmpty(), which EF Core recognises and translates to LEFT JOIN. On .NET 10 you can use the new LeftJoin() operator instead, which expresses the same thing in one call.

Should I use Join() or Include()?

Use Include() when a navigation property already models the relationship and you want the related entities loaded. Use Join() when there is no relationship in the model, when you join on non-key columns, or when you need a custom projection.

Can I join more than two entities?

Yes. Chain another Join() onto the result of the first one, using the anonymous type from the previous step as the outer sequence.

Which .NET and EF Core versions does this apply to?

.NET 10 and EF Core 10, both released in November 2025 as Long Term Support versions. EF Core 10 requires the .NET 10 SDK to build and the .NET 10 runtime to run.

Does the join run in the database or in memory?

In the database, as long as the whole query is translatable. EF Core throws an exception rather than silently evaluating a join on the client, so an error here means part of the expression has no SQL equivalent.

Why do I get the same customer several times?

Because the customer has several matching orders. A join returns one row per match, exactly like SQL. Apply Distinct() or restructure the query if you want one row per customer.

How can I see the SQL that EF Core generates?

Call ToQueryString() on the IQueryable, or configure logging with LogTo() on the DbContextOptionsBuilder.

Is AsNoTracking() worth adding?

For read-only queries, yes. It skips creating change-tracking entries, which reduces memory use and speeds up large result sets.

Conclusion

Joining two entities in .NET 10 with Entity Framework Core 10 comes down to the four arguments of Join(): the inner sequence, the two key selectors, and the result selector. From that single pattern you can add a Where() for conditions, a Select() to keep one side or project into a DTO, and anonymous types for composite keys. For left joins, use GroupJoin() flattened immediately with SelectMany() and DefaultIfEmpty(), or the LeftJoin() operator introduced in .NET 10.

Before writing an explicit join, check whether a navigation property already models the relationship — Include() or SelectMany() is usually clearer. And whenever a query feels slow, print ToQueryString() and read the SQL that EF Core actually sends.

For the full reference, see the official documentation on complex query operators in EF Core, the standard LINQ Join operator, the Queryable.LeftJoin API, and what's new in EF Core 10.

---

Video Tutorial

Coming soon!