Join and join clause - .NET Language Integrated Query (LINQ)

The join Clause

Use the join clause to join two sources into one. In this example, an inner join is created which eliminates countries without students.

using System;
using System.Collections.Generic;
using System.Linq;

namespace LinqExample
{
	[Serializable]
	public class Person : IComparable<Person>, IFormattable
	{
		public string LastName { get; set; }
		public string FirstName { get; set; }
		public string Country { get; set; }
		public string[] Cars { get; private set; }

		public Person(string LastName = null, string FirstName = null, string Country = null)
		{
			this.LastName = LastName;
			this.FirstName = FirstName;
			this.Country = Country;
		}
		public int CompareTo(Person person)
		{
			if (person == null)
				throw new ArgumentNullException("person");
			return LastName.CompareTo(person.LastName);
		}
		public override string ToString()
		{
			return LastName + ", " + FirstName;
		}
		public string ToString(string format)
		{
			return ToString(format, null);
		}
		public string ToString(string format, IFormatProvider FormatProvider)
		{
			switch (format)
			{
				case null:
				case "N": // return full name
					return ToString();
				case "L": // return last name
					return LastName;
				case "F": // return first name
					return FirstName;
				case "A": // return all information
					return ToString() + " - " + Country.ToString();
				default:
					throw new FormatException("Format " + format + " unsupported");
			}
		}
	}

	public static class Classroom
	{
		private static List<Person> students;

		public static IList<Person> GetAttendance()
		{
			if (students == null)
			{
				students = new List<Person>();
				students.Add(new Person("Holman", "Kaylee", "Holland"));
				students.Add(new Person("Crabtree", "Glenn", "France"));
				students.Add(new Person("Kellerman", "Calandra", "United States"));
				students.Add(new Person("Chairez", "Judy", "France"));
				students.Add(new Person("Santore", "Cari", "Germany"));
				students.Add(new Person("Barnaby", "Emelda", "Germany"));
				students.Add(new Person("Beeler", "Rufina", "Britain"));
				students.Add(new Person("Barreiro", "Jessia", "United States"));
				students.Add(new Person("Kummer", "Bebe", "Brazil"));
				students.Add(new Person("Sexson", "Aleida", "Italy"));
				students.Add(new Person("Ansell", "Freida", "United States"));
				students.Add(new Person("Eriksson", "Jae", "Belgium"));
			}
			return students;
		}
	}

	[Serializable]
	public class Country : IComparable<Country>, IFormattable
	{
		public string Name { get; set; }
		public int Population { get; set; }

		public Country(string Name = null, int Population = 0)
		{
			this.Name = Name;
			this.Population = Population;
		}
		public int CompareTo(Country country)
		{
			if (country == null)
				throw new ArgumentNullException("country");
			return Name.CompareTo(country.Name);
		}
		public override string ToString()
		{
			return Name;
		}
		public string ToString(string format)
		{
			return ToString(format, null);
		}
		public string ToString(string format, IFormatProvider FormatProvider)
		{
			switch (format)
			{
				case null:
				case "N": // return name
					return ToString();
				case "A": // return all information
					return ToString() + " - " + Population;
				default:
					throw new FormatException("Format " + format + " unsupported");
			}
		}
	}

	public static class World
	{
		private static List<Country> countries;

		public static IList<Country> GetCountries()
		{
			if (countries == null)
			{
				countries = new List<Country>();
				countries.Add(new Country("United States", 350000000));
				countries.Add(new Country("Germany", 50000000));
				countries.Add(new Country("Holland", 30000000));
				countries.Add(new Country("Britain", 50000000));
				countries.Add(new Country("Ethiopia", 60000000));
			}
			return countries;
		}
	}

	class Program
	{
		static void Main()
		{
			var coQuery = from c in World.GetCountries()
							select c;

			var stQuery = from p in Classroom.GetAttendance()
							group p by p.Country into g
							where g.Count() > 0
							select new
							{
								Country = g.Key,
								Count = g.Count(),
								Students = from p2 in g
										 orderby p2.LastName
										 select p2.LastName + ", " + p2.FirstName
							};
			var queryJoin = from c in coQuery // coQuery is the data to Inner Join
							join p in stQuery on c.Name equals p.Country
							orderby p.Count descending, c.Name
							select new
							{
								Country = c.Name,
								Population = c.Population,
								Count = p.Count,
								Students = p.Students
							};
			foreach (var obj in queryJoin)
			{
				Console.WriteLine("{0} ({1:0,0}) - {2} student(s)", obj.Country, obj.Population, obj.Count);
				foreach (string s in obj.Students)
				{
					Console.WriteLine("\t" + s);
				}
			}
		}
	}
}
United States (350,000,000) - 3 student(s)
        Ansell, Freida
        Barreiro, Jessia
        Kellerman, Calandra
Germany (50,000,000) - 2 student(s)
        Barnaby, Emelda
        Santore, Cari
Britain (50,000,000) - 1 student(s)
        Beeler, Rufina
Holland (30,000,000) - 1 student(s)
        Holman, Kaylee

An inner join was created eliminating Ethiopia.

To create an outer join (which includes all the countries even if there are no students) use DefaultIfEmpty() and join ... into as in this modified snippet of the above code. DefaultIfEmpty() returns null when there are no results.


static void Main()
{
	var coQuery = from c in World.GetCountries()
					select c;

	var stQuery = from p in Classroom.GetAttendance()
					group p by p.Country into g
					where g.Count() > 0
					select new
					{
						Country = g.Key,
						Count = g.Count(),
						Students = from p2 in g
									orderby p2.LastName
									select p2.LastName + ", " + p2.FirstName
					};
	var queryOuterJoin = from c in coQuery
					join p in stQuery on c.Name equals p.Country into pc
					from p in pc.DefaultIfEmpty() // This makes it an outer join
					orderby (p == null ? 0 : p.Count) descending, c.Name
					select new
					{
						Country = c.Name,
						Population = c.Population,
						Count = (p == null ? 0 : p.Count), // when p is null there is no person (student) in that country
						Students = (p == null ? null : p.Students)
					};
	foreach (var obj in queryOuterJoin)
	{
		Console.WriteLine("{0} ({1:0,0}) - {2} student(s)", obj.Country, obj.Population, obj.Count);
		if(obj.Students != null)
			foreach (string s in obj.Students)
			{
				Console.WriteLine("\t" + s);
			}
	}
}
United States (350,000,000) - 3 student(s)
        Ansell, Freida
        Barreiro, Jessia
        Kellerman, Calandra
Germany (50,000,000) - 2 student(s)
        Barnaby, Emelda
        Santore, Cari
Britain (50,000,000) - 1 student(s)
        Beeler, Rufina
Holland (30,000,000) - 1 student(s)
        Holman, Kaylee
Ethiopia (60,000,000) - 0 student(s)

Now, all countries are included even when there are no students.

Using Lambda with LINQ Method Join() and then GroupJoin()

The methods seem to make this harder than it needs to be...

using System;
using System.Collections.Generic;
using System.Linq;

namespace LinqExample
{
	[Serializable]
	public class Person : IComparable<Person>, IFormattable
	{
		public string LastName { get; set; }
		public string FirstName { get; set; }
		public string Country { get; set; }
		public string[] Cars { get; private set; }

		public Person(string LastName = null, string FirstName = null, string Country = null)
		{
			this.LastName = LastName;
			this.FirstName = FirstName;
			this.Country = Country;
		}
		public int CompareTo(Person person)
		{
			if (person == null)
				throw new ArgumentNullException("person");
			return LastName.CompareTo(person.LastName);
		}
		public override string ToString()
		{
			return LastName + ", " + FirstName;
		}
		public string ToString(string format)
		{
			return ToString(format, null);
		}
		public string ToString(string format, IFormatProvider FormatProvider)
		{
			switch (format)
			{
				case null:
				case "N": // return full name
					return ToString();
				case "L": // return last name
					return LastName;
				case "F": // return first name
					return FirstName;
				case "A": // return all information
					return ToString() + " - " + Country.ToString();
				default:
					throw new FormatException("Format " + format + " unsupported");
			}
		}
	}

	public static class Classroom
	{
		private static List<Person> students;

		public static IList<Person> GetAttendance()
		{
			if (students == null)
			{
				students = new List<Person>();
				students.Add(new Person("Holman", "Kaylee", "Holland"));
				students.Add(new Person("Crabtree", "Glenn", "France"));
				students.Add(new Person("Kellerman", "Calandra", "United States"));
				students.Add(new Person("Chairez", "Judy", "France"));
				students.Add(new Person("Santore", "Cari", "Germany"));
				students.Add(new Person("Barnaby", "Emelda", "Germany"));
				students.Add(new Person("Beeler", "Rufina", "Britain"));
				students.Add(new Person("Barreiro", "Jessia", "United States"));
				students.Add(new Person("Kummer", "Bebe", "Brazil"));
				students.Add(new Person("Sexson", "Aleida", "Italy"));
				students.Add(new Person("Ansell", "Freida", "United States"));
				students.Add(new Person("Eriksson", "Jae", "Belgium"));
			}
			return students;
		}
	}

	[Serializable]
	public class Country : IComparable<Country>, IFormattable
	{
		public string Name { get; set; }
		public int Population { get; set; }

		public Country(string Name = null, int Population = 0)
		{
			this.Name = Name;
			this.Population = Population;
		}
		public int CompareTo(Country country)
		{
			if (country == null)
				throw new ArgumentNullException("country");
			return Name.CompareTo(country.Name);
		}
		public override string ToString()
		{
			return Name;
		}
		public string ToString(string format)
		{
			return ToString(format, null);
		}
		public string ToString(string format, IFormatProvider FormatProvider)
		{
			switch (format)
			{
				case null:
				case "N": // return name
					return ToString();
				case "A": // return all information
					return ToString() + " - " + Population;
				default:
					throw new FormatException("Format " + format + " unsupported");
			}
		}
	}

	public static class World
	{
		private static List<Country> countries;

		public static IList<Country> GetCountries()
		{
			if (countries == null)
			{
				countries = new List<Country>();
				countries.Add(new Country("United States", 350000000));
				countries.Add(new Country("Germany", 50000000));
				countries.Add(new Country("Holland", 30000000));
				countries.Add(new Country("Britain", 50000000));
				countries.Add(new Country("Ethiopia", 60000000));
			}
			return countries;
		}
	}

	class Program
	{
		static void Main()
		{
			var coQuery = World.GetCountries().Select(c => c); // this is not really necessary

			var stQuery = Classroom.GetAttendance()
						.GroupBy(g => g.Country)
						.Where(g => g.Count() > 0)
						.Select(g => new { Country = g.Key, Count = g.Count(), Students = g.OrderBy(p => p.LastName).Select(p => p.LastName + ", " + p.FirstName) });

			var queryJoin = coQuery.Join(stQuery, country => country.Name, person => person.Country, (country, person) => new
						{
							Country = country.Name,
							Population = country.Population,
							Count = person.Count,
							Students = person.Students
						}).OrderByDescending(j => j.Count).ThenBy(j => j.Country);

			foreach (var obj in queryJoin)
			{
				Console.WriteLine("{0} ({1:0,0}) - {2} student(s)", obj.Country, obj.Population, obj.Count);
				foreach (string s in obj.Students)
				{
					Console.WriteLine("\t" + s);
				}
			}
		}
	}
}

Now the above Lambda example in an outer join to include all countries even when there are no students. This is just a snippet of the code that has changed.

static void Main()
{
	var coQuery = World.GetCountries().Select(c => c); // this is not really necessary

	var stQuery = Classroom.GetAttendance()
				.GroupBy(g => g.Country)
				.Where(g => g.Count() > 0)
				.Select(g => new { Country = g.Key, Count = g.Count(), Students = g.OrderBy(p => p.LastName).Select(p => p.LastName + ", " + p.FirstName) });

	var queryOuterJoin = coQuery.GroupJoin(stQuery, country => country.Name, person => person.Country, (country, person) => new
	{
		Country = country,
		Person = person
	}).SelectMany(c => c.Person.DefaultIfEmpty(), (c, p) => new
	{
		Country = c.Country.Name,
		Population = c.Country.Population,
		Count = (p == null ? 0 : p.Students.Count()), // when p is null there is no person (student) in that country
		Students = (p == null ? null : p.Students)
	}).OrderByDescending(j => j.Count).ThenBy(j => j.Country);

	foreach (var obj in queryOuterJoin)
	{
		Console.WriteLine("{0} ({1:0,0}) - {2} student(s)", obj.Country, obj.Population, obj.Count);
		if(obj.Students != null)
			foreach (string s in obj.Students)
			{
				Console.WriteLine("\t" + s);
			}
	}
}
<<<[Page 6 of 7]>>>

Cookies are simple text files stored on the user's computer. They are used for adding features and security to this site.
OK