Home » C# LINQ » LINQ Inner Join

LINQ Inner Join

Summary: in this tutorial, you’ll learn how to use the LINQ Join() method to perform an inner join of two sequences based on one or more keys.

Introduction to the LINQ Inner Join

To join two sequences based on one or more keys, you use the LINQ Join() extension method. Here’s the syntax of the Join() method:

public static IEnumerable<TResult> Join<TOuter,TInner,TKey,TResult> (
    this IEnumerable<TOuter> outer, 
    IEnumerable<TInner> inner, 
    Func<TOuter,TKey> outerKeySelector, 
    Func<TInner,TKey> innerKeySelector, 
    Func<TOuter,TInner,TResult> resultSelector
);Code language: C# (cs)

In this syntax:

  • outer is the first sequence to join.
  • inner is the sequence that joins to the first sequence.
  • outerKeySelector is a function that extracts the join key from each element of the first sequence.
  • innerKeySelector is a function that extracts the join key from each element of the second sequence.
  • resultSelector is a function that creates the result element from two matching elements.

The Join() method returns an IEnumerable<TResult> that has elements as the result of an inner join on the two sequences.

LINQ Inner Join examples

Let’s take some examples of using the LINQ Join() method to perform an inner join.

1) Using LINQ Join() method to perform an inner join based on a single key

The following program demonstrates how to use the Join() method to join the products list to the item list based on the product id:

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Cost { get; set; }

    public Product(int id, string name, decimal cost)
    {
        Id = id;
        Name = name;
        Cost = cost;
    }
}

class Item
{
    public int ProductId { get; set; }
    public int Qty { get; set; }
    public decimal Price { get; set; }

    public Item(int productId, int qty, decimal price)
    {
        ProductId = productId;
        Qty = qty;
        Price = price;
    }
}
class Program
{
    public static void Main(string[] args)
    {
        var products = new List<Product>()
        {
            new (id: 1, name: "A", cost: 10),
            new (id: 2, name: "B", cost: 20),
            new (id: 3, name: "C", cost: 30)
        };

        var items = new List<Item>()
        {
            new (productId:1, qty: 2, price: 12),
            new (productId:2, qty: 2, price: 25),
            new (productId:3, qty: 4, price: 35)
        };

        var results = items.Join(
           products,
           item => item.ProductId,
           product => product.Id,
           (item, product) => new
           {
               product.Name,
               product.Cost,
               item.Price,
               item.Qty,
               Margin = (item.Price - product.Cost) * item.Qty
           }
       );

        foreach (var result in results)
        {
            Console.WriteLine(result);
        }

    }
}Code language: C# (cs)

How it works.

First, define a Product class that has three properties Id, Name, and Cost.

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Cost { get; set; }

    public Product(int id, string name, decimal cost)
    {
        Id = id;
        Name = name;
        Cost = cost;
    }
}Code language: C# (cs)

Second, define the Item class that has three properties ProductId, Qty, and Price:

class Item
{
    public int ProductId { get; set; }
    public int Qty { get; set; }
    public decimal Price { get; set; }

    public Item(int productId, int qty, decimal price)
    {
        ProductId = productId;
        Qty = qty;
        Price = price;
    }
}Code language: C# (cs)

The ProductId of the Item class matches the Id of the Product class.

Third, create a list of three Product objects in the Main() method of the Program class:

var products = new List<Product>()
{
    new (id: 1, name: "A", cost: 10),
    new (id: 2, name: "B", cost: 20),
    new (id: 3, name: "C", cost: 30)
};Code language: C# (cs)

Fourth, create a list of three Items:

var items = new List<Item>()
{
    new (productId:1, qty: 2, price: 12),
    new (productId:2, qty: 2, price: 25),
    new (productId:3, qty: 4, price: 35)
};Code language: C# (cs)

Fifth, join the product list with the items list based on the product id:

var results = items.Join(
    products,
    item => item.ProductId,
    product => product.Id,
    (item, product) => new
    {
        product.Name,
        product.Cost,
        item.Price,
        item.Qty,
        Margin = (item.Price - product.Cost) * item.Qty
    }
);Code language: C# (cs)

The Join() method joins the products and items list based on the common property:

  • ProductId in Item in the items list.
  • Id of Product in the products list.

The result of the inner join is a new sequence of anonymous objects. Each object contains the data from matching Item and Product objects in both sequences.

The Join() method takes four arguments:

  1. The first argument is the products list which is the sequence to join with.
  2. The second argument is a lambda expression that defines the matching property in the items list – item=> item.ProductId.
  3. The third argument is a lambda expression that defines the matching property in the products list – product => product.Id.
  4. The fourth argument is a lambda expression that defines the shape of the result objects. Each anonymous object has four properties: Name, Cost, Price, Qty, and Margin. The Margin property represents the profit margin of each item based on the cost of the product and the price of the item.

Finally, display the result of the join to the console using a foreach loop and WriteLine() method.

The following shows the query syntax that is equivalent to the above method syntax:

var results = from product in products
              join item in items on product.Id equals item.ProductId
              select new
              {
                  product.Name,
                  product.Cost,
                  item.Price,
                  item.Qty,
                  Margin = (item.Price - product.Cost) * item.Qty
              };Code language: C# (cs)

2) Using LINQ Join() method to perform an inner join based on a composite key

The following program demonstrates how to use the Join() method to join the candidates and employees lists based on first name and last name:

class Candidate
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public DateTime InterviewDate { get; set; }

    public Candidate(string firstName, string lastName, DateTime interviewDate)
    {
        FirstName = firstName;
        LastName = lastName;
        InterviewDate = interviewDate;
    }
}
class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public DateTime JoinedDate { get; set; }

    public Employee(string firstName, string lastName, DateTime joinedDate)
    {
        FirstName = firstName;
        LastName = lastName;
        JoinedDate = joinedDate;
    }
}

class Program
{
    static void Main(string[] args)
    {
        // Create a list of candidates
        List<Candidate> candidates = new()
        {
            new ("John", "Doe", new DateTime(2010, 1, 20)),
            new ("Jane", "Doe", new DateTime(2010, 1, 28)),
            new ("Bob", "Smith",new DateTime(2010, 1, 15))
        };

        // Create a list of employees
        List<Employee> employees = new()
        {
            new ("John", "Doe", new DateTime(2010, 2, 1)),
            new ("Jane", "Doe", new DateTime(2010, 2, 2)),
            new ("Alice", "Johnson", new DateTime(2010, 2, 3))
        };

        var query = candidates.Join(
            employees,
            candidate => new { candidate.FirstName, candidate.LastName },
            employee => new { employee.FirstName, employee.LastName },
            (candidate, employee) => new
            {
                Name = $"{candidate.FirstName} {candidate.LastName}",
                candidate.InterviewDate,
                employee.JoinedDate
            });


        foreach (var result in query)
        {
            Console.WriteLine($"{result.Name} was interviewd on {result.InterviewDate.ToShortDateString()} and joined on {result.JoinedDate.ToShortDateString()}");
        }
    }
}Code language: C# (cs)

Output:

John Doe was interviewd on 1/20/2010 and joined on 2/1/2010
Jane Doe was interviewd on 1/28/2010 and joined on 2/2/2010Code language: C# (cs)

In this example:

First, define two classes: Candidate and Employee.

Second, create two lists of candidates and employees that contain the Candidate and Employee objects respectively.

Third, join the two lists based on the matching of the FirstName and LastName properties. The Join() method is called on the candidates list and takes four arguments:

  • The employees to join with.
  • A lambda expression that selects the key of the Candidate object to join (FirstName and LastName)
  • A lambda expression that selects the key of the Employee object to join (FirstName and LastName).
  • A lambda expression that creates a new anonymous object for the result list. The anonymous object contains the Name, InterviewDate, and JoinedDate properties.

Finally, iterate over the result and write each object to the console.

The following shows the query syntax that is equivalent to the above method syntax:

var query = from candidate in candidates
            join employee in employees
            on new { candidate.FirstName, candidate.LastName }
            equals new { employee.FirstName, employee.LastName }
            select new
            {
                Name = $"{candidate.FirstName} {candidate.LastName}",
                candidate.InterviewDate,
                employee.JoinedDate
            };Code language: C# (cs)

Summary

  • Use LINQ Join() method to join two sequences based on one or more keys.
Was this tutorial helpful ?
LINQ ForEach
Previously
LINQ ForEach
Up Next
LINQ Range
LINQ Range