Simulating Left Outer Join using LINQ

Simulating Left Outer Join using LINQ

linq

23 Articles

Improve

In this article, let's learn about how to do Left Outer Join in LINQ in .NET.

Note: If you have not done so already, I recommend you read the article on Using LINQ Group Join to combine data.

Table of Contents

  1. Introduction
  2. Simulating Left Outer Join using LINQ
  3. Summary

Introduction

A LEFT OUTER JOIN in SQL returns all the rows from the left table, and the matching rows from the right table. If there is no match, the result will contain NULL values for the right table's columns.

It is needed when you want to return all the records from one table the left table and any matching records from another table the right table. This is useful when you want to include records from one table that do not have matching records in the other table.

Simulating Left Outer Join using LINQ

Simulating Left Outer Join can be done using both Method Syntax and Query Syntax.

With Method Syntax we can use,

  1. SelectMany() method to select the 'right' collection.
  2. Where() method to filter the 'right' collection.
  3. DefaultIfEmpty() for 'right' collection.

With Query Syntax we can use,

  1. inner join using into and a second from statement.
  2. null object may be returned in 'right' collection.
  3. DefaultIfEmpty() for 'right' collection.

Code Sample - LINQ Left Outer Join Using Group Join

Demo - LINQ Left Outer Join Using Group Join

Let's try Left Outer Join using LINQ GroupJoin

  • We have Product class with following properties - Id, Name
  • We have Sale class with following properties - Id, Price
  • The tables are loaded with product and sale collection.
  • The Key Expression here is the Product ID.
  • Click on Left Outer Join Button to view the result.
  • Click on reset to try other combination
Products
Id Name
1 Shirt
2 Shirt
3 Shirt
4 Shirt
5 Shirt
6 Shirt
Sales
Sale Id Product Id Price Color
123 1 1000 Black
789 1 1000 Red
456 2 1000 White
Product Id Name Sale Id Price Color

Summary

In this article we learn't how to combine data between collections by doing a Left Outer Join using LINQ. This allows you to group elements from one collection with elements from another collection based on a specified key representing one-to-many relationship. All these can be used with any IEnumerable or IQueryable types.

  • Linq
  • Combine
  • into
  • Left Outer Join
  • Left Join