Performing joins across two sequences with the LINQ Join operator

With the Join operator in LINQ you can perform joins similar to using the JOIN keyword in SQL: the result will be a join on two sequences based on some common key.

We’ll use the following data structures:

public class Singer
{
	public int Id { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
}

public class Concert
{
	public int SingerId { get; set; }
	public int ConcertCount { get; set; }
	public int Year { get; set; }
}

public static IEnumerable<Singer> GetSingers()
{
	return new List<Singer>() 
	{
		new Singer(){Id = 1, FirstName = "Freddie", LastName = "Mercury"} 
		, new Singer(){Id = 2, FirstName = "Elvis", LastName = "Presley"}
		, new Singer(){Id = 3, FirstName = "Chuck", LastName = "Berry"}
		, new Singer(){Id = 4, FirstName = "Ray", LastName = "Charles"}
		, new Singer(){Id = 5, FirstName = "David", LastName = "Bowie"}
	};
}

public static IEnumerable<Concert> GetConcerts()
{
	return new List<Concert>()
	{
		new Concert(){SingerId = 1, ConcertCount = 53, Year = 1979}
		, new Concert(){SingerId = 1, ConcertCount = 74, Year = 1980}
		, new Concert(){SingerId = 1, ConcertCount = 38, Year = 1981}
		, new Concert(){SingerId = 2, ConcertCount = 43, Year = 1970}
		, new Concert(){SingerId = 2, ConcertCount = 64, Year = 1968}
		, new Concert(){SingerId = 3, ConcertCount = 32, Year = 1960}
		, new Concert(){SingerId = 3, ConcertCount = 51, Year = 1961}
		, new Concert(){SingerId = 3, ConcertCount = 95, Year = 1962}
		, new Concert(){SingerId = 4, ConcertCount = 42, Year = 1950}
		, new Concert(){SingerId = 4, ConcertCount = 12, Year = 1951}
		, new Concert(){SingerId = 5, ConcertCount = 53, Year = 1983}
	};
}

I think most LINQ operators are quite straightforward to use. Join is probably one of the more complex ones with its Func delegates. The signature of the operator looks as follows:

IEnumerable<TResult> result = IEnumerable<TOuter>.Join<TOuter, TInner, TKey, TResult>(IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector);

Let’s see an example of how the singer and concert objects can be joined using the singer ids:

var singerConcerts = singers.Join(concerts, s => s.Id, c => c.SingerId, (s, c) => new
{
	Id = s.Id,
	SingerName = string.Concat(s.FirstName, " ", s.LastName),
	ConcertCount = c.ConcertCount,
	Year = c.Year
});

foreach (var res in singerConcerts)
{
	Console.WriteLine(string.Concat(res.Id, ": ", res.SingerName, ", ", res.Year, ", ", res.ConcertCount));
}

• ‘singers’ is the outer sequence of the two sequences
• ‘concerts’ will be the inner sequence
• s – s.Id: the outer key selector, in this case the singer’s ID
• c – c.SingerId: the inner key selector, in this case the SingerId secondary key of a Concert object
• The last element is the result selector where we declare what type of object we want returned from the operation

The Join operator has an overload which accepts an EqualityComparer of TKey. In our case it’s not necessary as the TKey will be the singer ID, i.e. an integer, and .NET can easily compare those. However, if the comparison key is an object, then you can implement the IEqualityComparer interface. Here’s an example how to do that.

You can view all LINQ-related posts on this blog here.