Perform inner joins
In relational database terms, an inner join produces a result set in which each element of the first collection appears one time for every matching element in the second collection. If an element in the first collection has no matching elements, it does not appear in the result set. The Join method, which is called by the join
clause in C#, implements an inner join.
This article shows you how to perform four variations of an inner join:
A simple inner join that correlates elements from two data sources based on a simple key.
An inner join that correlates elements from two data sources based on a composite key. A composite key, which is a key that consists of more than one value, enables you to correlate elements based on more than one property.
A multiple join in which successive join operations are appended to each other.
An inner join that is implemented by using a group join.
Note
The examples in this topic use the following data classes:
record Person(string FirstName, string LastName);
record Pet(string Name, Person Owner);
record Employee(string FirstName, string LastName, int EmployeeID);
record Cat(string Name, Person Owner) : Pet(Name, Owner);
record Dog(string Name, Person Owner) : Pet(Name, Owner);
as well as the Student
class from Query a collection of objects.
Example - Simple key join
The following example creates two collections that contain objects of two user-defined types, Person
and Pet
. The query uses the join
clause in C# to match Person
objects with Pet
objects whose Owner
is that Person
. The select
clause in C# defines how the resulting objects will look. In this example the resulting objects are anonymous types that consist of the owner's first name and the pet's name.
Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");
Person terry = new("Terry", "Adams");
Person charlotte = new("Charlotte", "Weiss");
Person arlene = new("Arlene", "Huff");
Person rui = new("Rui", "Raposo");
List<Person> people = new() { magnus, terry, charlotte, arlene, rui };
List<Pet> pets = new()
{
new(Name: "Barley", Owner: terry),
new("Boots", terry),
new("Whiskers", charlotte),
new("Blue Moon", rui),
new("Daisy", magnus),
};
// Create a collection of person-pet pairs. Each element in the collection
// is an anonymous type containing both the person's name and their pet's name.
var query =
from person in people
join pet in pets on person equals pet.Owner
select new
{
OwnerName = person.FirstName,
PetName = pet.Name
};
foreach (var ownerAndPet in query)
{
Console.WriteLine($"\"{ownerAndPet.PetName}\" is owned by {ownerAndPet.OwnerName}");
}
/* Output:
"Daisy" is owned by Magnus
"Barley" is owned by Terry
"Boots" is owned by Terry
"Whiskers" is owned by Charlotte
"Blue Moon" is owned by Rui
*/
Note that the Person
object whose LastName
is "Huff" does not appear in the result set because there is no Pet
object that has Pet.Owner
equal to that Person
.
Example - Composite key join
Instead of correlating elements based on just one property, you can use a composite key to compare elements based on multiple properties. To do this, specify the key selector function for each collection to return an anonymous type that consists of the properties you want to compare. If you label the properties, they must have the same label in each key's anonymous type. The properties must also appear in the same order.
The following example uses a list of Employee
objects and a list of Student
objects to determine which employees are also students. Both of these types have a FirstName
and a LastName
property of type String. The functions that create the join keys from each list's elements return an anonymous type that consists of the FirstName
and LastName
properties of each element. The join operation compares these composite keys for equality and returns pairs of objects from each list where both the first name and the last name match.
List<Employee> employees = new()
{
new(FirstName: "Terry", LastName: "Adams", EmployeeID: 522459),
new("Charlotte", "Weiss", 204467),
new("Magnus", "Hedland", 866200),
new("Vernette", "Price", 437139)
};
List<Student> students = new()
{
new(FirstName: "Vernette", LastName: "Price", StudentID: 9562),
new("Terry", "Earls", 9870),
new("Terry", "Adams", 9913)
};
// Join the two data sources based on a composite key consisting of first and last name,
// to determine which employees are also students.
var query =
from employee in employees
join student in students on new
{
employee.FirstName,
employee.LastName
} equals new
{
student.FirstName,
student.LastName
}
select employee.FirstName + " " + employee.LastName;
Console.WriteLine("The following people are both employees and students:");
foreach (string name in query)
{
Console.WriteLine(name);
}
/* Output:
The following people are both employees and students:
Terry Adams
Vernette Price
*/
Example - Multiple join
Any number of join operations can be appended to each other to perform a multiple join. Each join
clause in C# correlates a specified data source with the results of the previous join.
The following example creates three collections: a list of Person
objects, a list of Cat
objects, and a list of Dog
objects.
The first join
clause in C# matches people and cats based on a Person
object matching Cat.Owner
. It returns a sequence of anonymous types that contain the Person
object and Cat.Name
.
The second join
clause in C# correlates the anonymous types returned by the first join with Dog
objects in the supplied list of dogs, based on a composite key that consists of the Owner
property of type Person
, and the first letter of the animal's name. It returns a sequence of anonymous types that contain the Cat.Name
and Dog.Name
properties from each matching pair. Because this is an inner join, only those objects from the first data source that have a match in the second data source are returned.
Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");
Person terry = new("Terry", "Adams");
Person charlotte = new("Charlotte", "Weiss");
Person arlene = new("Arlene", "Huff");
Person rui = new("Rui", "Raposo");
Person phyllis = new("Phyllis", "Harris");
List<Person> people = new() { magnus, terry, charlotte, arlene, rui, phyllis };
List<Cat> cats = new()
{
new(Name: "Barley", Owner: terry),
new("Boots", terry),
new("Whiskers", charlotte),
new("Blue Moon", rui),
new("Daisy", magnus),
};
List<Dog> dogs = new()
{
new(Name: "Four Wheel Drive", Owner: phyllis),
new("Duke", magnus),
new("Denim", terry),
new("Wiley", charlotte),
new("Snoopy", rui),
new("Snickers", arlene),
};
// The first join matches Person and Cat.Owner from the list of people and
// cats, based on a common Person. The second join matches dogs whose names start
// with the same letter as the cats that have the same owner.
var query =
from person in people
join cat in cats on person equals cat.Owner
join dog in dogs on new
{
Owner = person,
Letter = cat.Name.Substring(0, 1)
} equals new
{
dog.Owner,
Letter = dog.Name.Substring(0, 1)
}
select new
{
CatName = cat.Name,
DogName = dog.Name
};
foreach (var obj in query)
{
Console.WriteLine(
$"The cat \"{obj.CatName}\" shares a house, and the first letter of their name, with \"{obj.DogName}\"."
);
}
/* Output:
The cat "Daisy" shares a house, and the first letter of their name, with "Duke".
The cat "Whiskers" shares a house, and the first letter of their name, with "Wiley".
*/
Example - Inner join by using grouped join
The following example shows you how to implement an inner join by using a group join.
In query1
, the list of Person
objects is group-joined to the list of Pet
objects based on the Person
matching the Pet.Owner
property. The group join creates a collection of intermediate groups, where each group consists of a Person
object and a sequence of matching Pet
objects.
By adding a second from
clause to the query, this sequence of sequences is combined (or flattened) into one longer sequence. The type of the elements of the final sequence is specified by the select
clause. In this example, that type is an anonymous type that consists of the Person.FirstName
and Pet.Name
properties for each matching pair.
The result of query1
is equivalent to the result set that would have been obtained by using the join
clause without the into
clause to perform an inner join. The query2
variable demonstrates this equivalent query.
Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");
Person terry = new("Terry", "Adams");
Person charlotte = new("Charlotte", "Weiss");
Person arlene = new("Arlene", "Huff");
List<Person> people = new() { magnus, terry, charlotte, arlene };
List<Pet> pets = new()
{
new(Name: "Barley", Owner: terry),
new("Boots", terry),
new("Whiskers", charlotte),
new("Blue Moon", terry),
new("Daisy", magnus),
};
var query1 =
from person in people
join pet in pets on person equals pet.Owner into gj
from subpet in gj
select new
{
OwnerName = person.FirstName,
PetName = subpet.Name
};
Console.WriteLine("Inner join using GroupJoin():");
foreach (var v in query1)
{
Console.WriteLine($"{v.OwnerName} - {v.PetName}");
}
var query2 =
from person in people
join pet in pets on person equals pet.Owner
select new
{
OwnerName = person.FirstName,
PetName = pet.Name
};
Console.WriteLine();
Console.WriteLine("The equivalent operation using Join():");
foreach (var v in query2)
{
Console.WriteLine($"{v.OwnerName} - {v.PetName}");
}
/* Output:
Inner join using GroupJoin():
Magnus - Daisy
Terry - Barley
Terry - Boots
Terry - Blue Moon
Charlotte - Whiskers
The equivalent operation using Join():
Magnus - Daisy
Terry - Barley
Terry - Boots
Terry - Blue Moon
Charlotte - Whiskers
*/
See also
Feedback
Submit and view feedback for