PROWAREtech
.NET: Language Integrated Query (LINQ) - Page 6
Join and join clause.
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);
}
}
}
Comment
