How to: Perform Left Outer Joins (C# Programming Guide)
A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.
Example
The following example demonstrates how to use the DefaultIfEmpty method on the results of a group join to perform a left outer join.
The first step in producing a left outer join of two collections is to perform an inner join by using a group join. (See How to: Perform Inner Joins (C# Programming Guide) for an explanation of this process.) In this example, the list of Person objects is inner-joined to the list of Pet objects based on a Person object that matches Pet.Owner.
The second step is to include each element of the first (left) collection in the result set even if that element has no matches in the right collection. This is accomplished by calling DefaultIfEmpty on each sequence of matching elements from the group join. In this example, DefaultIfEmpty is called on each sequence of matching Pet objects. The method returns a collection that contains a single, default value if the sequence of matching Pet objects is empty for any Person object, thereby ensuring that each Person object is represented in the result collection.
Note
The default value for a reference type is null; therefore, the example checks for a null reference before accessing each element of each Pet collection.
class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
class Pet
{
public string Name { get; set; }
public Person Owner { get; set; }
}
public static void LeftOuterJoinExample()
{
Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
Pet barley = new Pet { Name = "Barley", Owner = terry };
Pet boots = new Pet { Name = "Boots", Owner = terry };
Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
Pet daisy = new Pet { Name = "Daisy", Owner = magnus };
// Create two lists.
List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };
var query = from person in people
join pet in pets on person equals pet.Owner into gj
from subpet in gj.DefaultIfEmpty()
select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };
foreach (var v in query)
{
Console.WriteLine("{0,-15}{1}", v.FirstName + ":", v.PetName);
}
}
// This code produces the following output:
//
// Magnus: Daisy
// Terry: Barley
// Terry: Boots
// Terry: Blue Moon
// Charlotte: Whiskers
// Arlene:
Compiling the Code
Create a new Console Application project in Visual Studio.
Add a reference to System.Core.dll if it is not already referenced.
Include the System.Linq namespace.
Copy and paste the code from the example into the program.cs file, below the Main method in the Program class. Add a line of code to the Main method to call the LeftOuterJoinExample method.
Run the program.
See Also
Tasks
How to: Perform Inner Joins (C# Programming Guide)
How to: Perform Grouped Joins (C# Programming Guide)
Reference
Anonymous Types (C# Programming Guide)