LINQ Keywords: join
|
|
LINQ Keywords: join |
|
Introduction |
The join keyword is used to join records using a criterion. The basic formula to follow is:
join JoinName in ChildList on Condition
The join condition is created before the select statement. Here is an example of starting a join:
var people = from lstTeachers
in teachers
join std . . .
select Something;
The join operation is followed by the in keyword followed by a list variable. Here is an example:
var people = from lstTeachers
in teachers
join std in students . . .
select Something;
To support joins, the C# language provides a context-sensitive keyword named equals. The formula would be:
ChildField equals ParentField
|
Cross Joins |
A cross join is a set that includes all records from two lists. To create a cross join, you can use a combination of from...in... statement for each sub-list. Here is an example:
var people = from lstStudents in students from staffMembers in staff select . . .;
Here is an example:
using System;
using System.Linq;
using System.Collections.Generic;
public class Exercise
{
public static int Main()
{
var students = new Student[]
{
new Student(82495, "Carlton", "Blanchard"),
new Student(20857, "Jerrie", "Sachs"),
new Student(20935, "Charlotte", "O'Keefe"),
new Student(79274, "Christine", "Burns"),
new Student(79204, "Bobbie", "Swanson"),
new Student(14815, "Marianne", "Swanson"),
new Student(24958, "Jeannette", "Perkins"),
new Student(24759, "Pierrette", "Perkins"),
new Student(92804, "Charles", "Pressmann"),
new Student(80074, "Alain", "Goodson")
};
var staff = new Teacher[]
{
new Teacher("Sandrine Hanson", 16.65),
new Teacher("Gregory Larson", 18.05),
new Teacher("Charles Nanze", 14.95)
};
var people = from lstStudents
in students
from staffMembers
in staff
select new
{
lstStudents.StudentNumber,
lstStudents.FirstName,
lstStudents.LastName,
staffMembers.FullName,
staffMembers.HourlySalary
};
Console.WriteLine("+=======+============+===========+=================+========+");
Console.WriteLine("| Std # | First Name | Last Name | Full Name | Salary |");
foreach (var person in people)
{
Console.WriteLine("+-------+------------+-----------+-----------------+--------+");
Console.WriteLine("| {0,5} | {1,-10} | {2,-9} | {3,-15} | {4,6} |",
person.StudentNumber, person.FirstName, person.LastName,
person.FullName, person.HourlySalary);
}
Console.WriteLine("+=======+============+===========+=================+========+");
Console.WriteLine();
return 0;
}
}
public class Student
{
public int StudentNumber;
public string FirstName;
public string LastName;
public Student(int number = 0,
string firstName = "Leslie",
string lastName = "Doe")
{
StudentNumber = number;
FirstName = firstName;
LastName = lastName;
}
}
public class Teacher
{
public string FullName;
public double HourlySalary;
public Teacher(string name = "Leslie Doe",
double salary = 0.00D)
{
FullName = name;
HourlySalary = salary;
}
}
This would produce:
+=======+============+===========+=================+========+ | Std # | First Name | Last Name | Full Name | Salary | +-------+------------+-----------+-----------------+--------+ | 82495 | Carlton | Blanchard | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 82495 | Carlton | Blanchard | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 82495 | Carlton | Blanchard | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 20857 | Jerrie | Sachs | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 20857 | Jerrie | Sachs | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 20857 | Jerrie | Sachs | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 20935 | Charlotte | O'Keefe | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 20935 | Charlotte | O'Keefe | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 20935 | Charlotte | O'Keefe | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 79274 | Christine | Burns | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 79274 | Christine | Burns | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 79274 | Christine | Burns | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 79204 | Bobbie | Swanson | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 79204 | Bobbie | Swanson | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 79204 | Bobbie | Swanson | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 14815 | Marianne | Swanson | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 14815 | Marianne | Swanson | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 14815 | Marianne | Swanson | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 24958 | Jeannette | Perkins | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 24958 | Jeannette | Perkins | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 24958 | Jeannette | Perkins | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 24759 | Pierrette | Perkins | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 24759 | Pierrette | Perkins | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 24759 | Pierrette | Perkins | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Charles Nanze | 14.95 | +-------+------------+-----------+-----------------+--------+ | 80074 | Alain | Goodson | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 80074 | Alain | Goodson | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 80074 | Alain | Goodson | Charles Nanze | 14.95 | +=======+============+===========+=================+========+ Press any key to continue . . .
|
Inner Joins |
An inner join is a list where some records don't have a value for some fields. Here is an example:
public class Exercise
{
public static int Main()
{
var students = new Student[]
{
new Student(82495, 2, "Carlton", "Blanchard"),
new Student(20857, 2, "Jerrie", "Sachs"),
new Student(20935, 1, "Charlotte", "O'Keefe"),
new Student(79274, 3, "Christine", "Burns"),
new Student(79204, 2, "Bobbie", "Swanson"),
new Student(14815, 1, "Marianne", "Swanson"),
new Student(24958, 3, "Jeannette", "Perkins"),
new Student(24759, 3, "Pierrette", "Perkins"),
new Student(92804, 3, "Charles", "Pressmann"),
new Student(80074, 2, "Alain", "Goodson")
};
var teachers = new Teacher[]
{
new Teacher(1, "Sandrine Hanson", 16.65),
new Teacher(2, "Gregory Larson", 18.05),
new Teacher(3, "Charles Nanze", 14.95)
};
var people = from lstTeachers
in teachers
join std in students on lstTeachers.TeacherID equals std.TeacherID
select new
{
StdNbr = std.StudentNumber,
FirstName = std.FirstName,
LastName = std.LastName,
TeacherName = lstTeachers.FullName
};
Console.WriteLine("+=======+============+===========+=================+");
Console.WriteLine("| Std # | First Name | Last Name | Teacher Name |");
Console.WriteLine("+=======+============+===========+=================+");
foreach (var person in people)
{
Console.WriteLine("| {0,5} | {1,-10} | {2,-9} | {3,-15} |",
person.StdNbr, person.FirstName, person.LastName,
person.TeacherName);
Console.WriteLine("+-------+------------+-----------+-----------------+");
}
Console.WriteLine();
return 0;
}
}
This would produce:
+=======+============+===========+=================+ | Std # | First Name | Last Name | Teacher Name | +=======+============+===========+=================+ | 20935 | Charlotte | O'Keefe | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 14815 | Marianne | Swanson | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 82495 | Carlton | Blanchard | Gregory Larson | +-------+------------+-----------+-----------------+ | 20857 | Jerrie | Sachs | Gregory Larson | +-------+------------+-----------+-----------------+ | 79204 | Bobbie | Swanson | Gregory Larson | +-------+------------+-----------+-----------------+ | 80074 | Alain | Goodson | Gregory Larson | +-------+------------+-----------+-----------------+ | 79274 | Christine | Burns | Charles Nanze | +-------+------------+-----------+-----------------+ | 24958 | Jeannette | Perkins | Charles Nanze | +-------+------------+-----------+-----------------+ | 24759 | Pierrette | Perkins | Charles Nanze | +-------+------------+-----------+-----------------+ | 92804 | Charles | Pressmann | Charles Nanze | +-------+------------+-----------+-----------------+ Press any key to continue . . .
|
Outer Joins |
An outer join is a list that shows only records that have entries in the child list. To create it, you can use the DefaultIfEmpty() method of the Enumerable class. Here is an example:
using System;
using System.Linq;
using System.Collections.Generic;
public class Exercise
{
public static int Main()
{
var students = new Student[]
{
new Student(82495, 2, "Carlton", "Blanchard"),
new Student(20857, 2, "Jerrie", "Sachs"),
new Student(20935, 1, "Charlotte", "O'Keefe"),
new Student(79274, 3, "Christine", "Burns"),
new Student(79204, 2, "Bobbie", "Swanson"),
new Student(14815, 1, "Marianne", "Swanson"),
new Student(24958, 3, "Jeannette", "Perkins"),
new Student(24759, 3, "Pierrette", "Perkins"),
new Student(92804, 3, "Charles", "Pressmann"),
new Student(80074, 2, "Alain", "Goodson")
};
var teachers = new Teacher[]
{
new Teacher(1, "Sandrine Hanson", 16.65),
new Teacher(2, "Gregory Larson", 18.05),
new Teacher(3, "Charles Nanze", 14.95)
};
var people = from lstTeachers
in teachers
join std in students on lstTeachers.TeacherID equals std.TeacherID into TeacherGroup
from teaching in TeacherGroup.DefaultIfEmpty()
select new
{
TeacherNbr = lstTeachers.TeacherID,
TeacherName = lstTeachers.FullName,
StudentNbr = teaching.StudentNumber,
FirstName = teaching.FirstName,
LastName = teaching.LastName,
};
Console.WriteLine("+===========+=================+=======+===========+===========+");
Console.WriteLine("| Teacher # | Teacher Name | Std # | First Name| Last Name | ");
Console.WriteLine("+===========+=================+=======+===========+===========+");
foreach (var person in people)
{
Console.WriteLine("| {0,3} | {1,-15} | {2,4} | {3,-9} | {4,-9} | ",
person.TeacherNbr, person.TeacherName,
person.StudentNbr, person.FirstName, person.LastName);
Console.WriteLine("+-----------+-----------------+-------+-----------+-----------+");
}
Console.WriteLine();
return 0;
}
}
public class Student
{
public int StudentNumber;
public int TeacherID;
public string FirstName;
public string LastName;
public Student(int number = 0,
int teachID = 1,
string firstName = "Leslie",
string lastName = "Doe")
{
StudentNumber = number;
TeacherID = teachID;
FirstName = firstName;
LastName = lastName;
}
}
public class Teacher
{
public int TeacherID;
public string FullName;
public double HourlySalary;
public Teacher(int ID = 1, string name = "Leslie Doe",
double salary = 0.00D)
{
TeacherID = ID;
FullName = name;
HourlySalary = salary;
}
}
This would produce:
+===========+=================+=======+===========+===========+ | Teacher # | Teacher Name | Std # | First Name| Last Name | +===========+=================+=======+===========+===========+ | 1 | Sandrine Hanson | 20935 | Charlotte | O'Keefe | +-----------+-----------------+-------+-----------+-----------+ | 1 | Sandrine Hanson | 14815 | Marianne | Swanson | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 82495 | Carlton | Blanchard | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 20857 | Jerrie | Sachs | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 79204 | Bobbie | Swanson | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 80074 | Alain | Goodson | +-----------+-----------------+-------+-----------+-----------+ | 3 | Charles Nanze | 79274 | Christine | Burns | +-----------+-----------------+-------+-----------+-----------+ | 3 | Charles Nanze | 24958 | Jeannette | Perkins | +-----------+-----------------+-------+-----------+-----------+ | 3 | Charles Nanze | 24759 | Pierrette | Perkins | +-----------+-----------------+-------+-----------+-----------+ | 3 | Charles Nanze | 92804 | Charles | Pressmann | +-----------+-----------------+-------+-----------+-----------+ Press any key to continue . . .
|
|
||
| Home | Copyright © 2010 FunctionX, Inc. | |
|
|
||