Entity Framework Core: Joining Tables with LINQ

I often have difficulty remembering the exact syntax for joins using LINQ. I’m going to document some examples here for my own reference. I hope others also find value in this as well. This is a post that I will be expanding over time with more examples as I have time to document them.

For this example, I’ll be using my sample Customers and CustomerInvoices tables again.

Here’s a screenshot of the CustomerInvoices table structure:

And here’s the SQL DDL for the table:

USE [appdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerInvoices](
	[InvoiceId] [bigint] IDENTITY(1,1) NOT NULL,
	[CustomerId] [int] NOT NULL,
	[InvoiceAmount] [decimal](18, 2) NOT NULL,
	[InvoiceMessage] [varchar](1000) NULL,
 CONSTRAINT [PK_CustomerInvoices] PRIMARY KEY CLUSTERED 
(
	[InvoiceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerInvoices]  WITH CHECK ADD  CONSTRAINT [FK_CustomerInvoices_Customers] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customers] ([Id])
GO

ALTER TABLE [dbo].[CustomerInvoices] CHECK CONSTRAINT [FK_CustomerInvoices_Customers]
GO

Here’s a screenshot of the Customers table structure:

And here’s the SQL DDL for the Customers table:

USE [appdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customers](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Address] [varchar](250) NULL,
	[City] [varchar](50) NULL,
	[Region] [varchar](50) NULL,
	[PostalCode] [varchar](50) NULL,
	[Country] [varchar](3) NULL,
	[PhoneNumber] [varchar](20) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

And here are the model classes for Customers and CustomerInvoice:

public class Customer
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public string? Address { get; set; }
    public string? City { get; set; }
    public string? Region { get; set; }
    public string? PostalCode { get; set; }
    public string? Country { get; set; }
    public string? PhoneNumber { get; set; }
}

public class CustomerInvoice
{
    public long InvoiceId { get; set; }
    public int CustomerId { get; set; }
    public decimal InvoiceAmount { get; set; }
    public string? InvoiceMessage { get; set; }
    public Customer? Customer {get; set;}
}

For these examples, I’ve also created a map for the CustomerInvoice class:

using LinqExamples.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class CustomerInvoiceMap : IEntityTypeConfiguration<CustomerInvoice>
{
    public void Configure(EntityTypeBuilder<CustomerInvoice> builder)
    {
        builder.ToTable("CustomerInvoices");
        builder.HasKey(c => c.InvoiceId);
        builder.Property(p => p.InvoiceAmount)
               .HasPrecision(18, 2);
    }
}

The map tells Entity Framework Core what the primary key of the table is, as “InvoiceId” does not follow the standard EF Core convention of using just Id or <TypeName>Id as the primary key name.

The map also lets EF Core know the precision of the InvoiceAmount column.

Here is the console Program.cs file containing the join examples:

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using LinqExamples;


var builder = Host.CreateApplicationBuilder(args);

builder.Configuration.AddJsonFile("appsettings.json");

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
if (connectionString == null)
{
    Console.WriteLine("Connection string cannot be null.");
    return;
}

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

var app = builder.Build();

using var scope = app.Services.CreateScope();
var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

var query = context.CustomerInvoices.Join(context.Customers,
    invoice => invoice.CustomerId, customer => customer.Id,
    (invoice, customer) => new
    {
        customer.Id,
        customer.Name,
        customer.Address,
        invoice.InvoiceId,
        invoice.InvoiceAmount,
        invoice.InvoiceMessage
    });

var customerInvoices = query.Where(x => x.Id == 1).ToList();

Console.WriteLine(customerInvoices[0]);
Console.WriteLine();
Console.ReadKey();

var query2 = context.CustomerInvoices.Join(context.Customers,
    invoice => invoice.CustomerId, customer => customer.Id,
    (invoice, customer) => new
    {
        CustomerId = customer.Id,
        CustomerName = customer.Name,
        CustomerAddress = customer.Address,
        invoice.InvoiceId,
        invoice.InvoiceAmount,
        invoice.InvoiceMessage
    });

var customerInvoices2 = query2.Where(x => x.CustomerId == 2).ToList();

Console.WriteLine(customerInvoices2[0]);
Console.ReadKey();

This code snippet from the above Program.cs file shows the first join example:

var query = context.CustomerInvoices.Join(context.Customers,
    invoice => invoice.CustomerId, customer => customer.Id,
    (invoice, customer) => new
    {
        customer.Id,
        customer.Name,
        customer.Address,
        invoice.InvoiceId,
        invoice.InvoiceAmount,
        invoice.InvoiceMessage
    });

var customerInvoices = query.Where(x => x.Id == 1).ToList();

The above code is doing a simple inner join, joining all matching records in CustomerInvoices and Customers where the records match on invoice.CustomerId and customer.Id. Then it uses the .Where to query the database using the join.

When we run the Program.cs code up to the first WriteLine, we see this output:

This code snippet from the above Program.cs file shows the second join example:

var query2 = context.CustomerInvoices.Join(context.Customers,
    invoice => invoice.CustomerId, customer => customer.Id,
    (invoice, customer) => new
    {
        CustomerId = customer.Id,
        CustomerName = customer.Name,
        CustomerAddress = customer.Address,
        invoice.InvoiceId,
        invoice.InvoiceAmount,
        invoice.InvoiceMessage
    });

var customerInvoices2 = query2.Where(x => x.CustomerId == 2).ToList();

This is still doing a simple inner join using the same type of join logic as before. The difference is that this example shows how we can rename the fields in the object returned by EF Core.

When the run the Program.cs code up to the second WriteLine, we see this output:

The difference in the second example is that the columns are renamed. “Id” becomes “CustomerId”, “Name” becomes “CustomerName”, etc.

NOTE: The tables used in these examples have a proper foreign key relationship, so using navigations would work and be preferred here. However, this post is about join syntax, rather than about the idiomatic way of querying this type of relationship. Here’s an example of what using navigations to accomplish the same join would look like:

var results = context.CustomerInvoices
    .Where(ci => ci.CustomerId == 2)
    .Select(ci => new {
        CustomerId = ci.Customer.Id,
        CustomerName = ci.Customer.Name,
        CustomerAddress = ci.Customer.Address,
        ci.InvoiceId,
        ci.InvoiceAmount,
        ci.InvoiceMessage
    })
    .ToList();

This is just a reference post I can refer back to when I need a quick refresher on the syntax for joins using LINQ. As I mentioned at the beginning of the post, I will gradually expand this with more examples. I’ll add left outer joins next. Right/full joins may require workarounds or raw SQL, but I plan to address those as well.