To create a join, you must use some lists, like those we have created so far. The primary formula to join records is: join JoinName in ChildList on Condition The join condition is created before the select statement. You start with the join keyword followed by a name. The name can be used later or you can ignore it. 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. The ChildList specifies the list created from the child class. This can be the list created by the previous in statement. Here is an example: var people = from lstTeachers
in teachers
join std in students . . .
select Something;
Of course you can write the in statement on its own line. The Condition holds a logical expression that specifies the relationship by which the records will be joined. To support joins, the C# language provides a context-sensitive keyword named equals. Therefore, to create a join in LINQ, the values used to establish the link in the join statement should use the object of the from statement and that of the join clause. Having those values, you can access the joining property from each object and apply the equals operator on them. The formula would be: ChildField equals ParentField When you create a condition, the compiler (or rather the LINQ interpreter) would check each record of the child list that has a value for the corresponding property of the parent list. A cross join is a set that includes all records from two lists as follows: the first record from the first list is associated to each record from the second list. Then the second record from the first list is associated to each record from the second list, and so on. There are two ways you create a cross join in LINQ. For a cross join, you can use one list of numbers and another list of names. Or, you can use one list for videos and another list of music albums. You can then create a common list that includes items from each list. 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 . . .; Then, in the select statement, provide a way to retrieve the members from each list. This can be done by using the new operator and create a list of fields inside the curly brackets. This can be done as follows: 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 . . . Notice that each record from one list is associated to each record of the other list. To put a restriction in the list of values, you can add a where condition. Here is an example: var people = from lstStudents
in students
from staffMembers
in staff
where lstStudents.FirstName.StartsWith("C")
select new
{
lstStudents.StudentNumber,
lstStudents.FirstName,
lstStudents.LastName,
staffMembers.FullName,
staffMembers.HourlySalary
};
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 | +-------+------------+-----------+-----------------+--------+ | 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 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Charles Nanze | 14.95 | +=======+============+===========+=================+========+ Press any key to continue . . . In the same way, you can create a where statement for each from list. Here is an example: var people = from lstStudents
in students
from staffMembers
in staff
where lstStudents.FirstName.StartsWith("C")
where staffMembers.HourlySalary > 15.00
select new
{
lstStudents.StudentNumber,
lstStudents.FirstName,
lstStudents.LastName,
staffMembers.FullName,
staffMembers.HourlySalary
};
This can also be written as follows: var people = from lstStudents
in students
where lstStudents.FirstName.StartsWith("C")
from staffMembers
in staff
where staffMembers.HourlySalary > 15.00
select new
{
lstStudents.StudentNumber,
lstStudents.FirstName,
lstStudents.LastName,
staffMembers.FullName,
staffMembers.HourlySalary
};
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 | +-------+------------+-----------+-----------------+--------+ | 20935 | Charlotte | O'Keefe | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 20935 | Charlotte | O'Keefe | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 79274 | Christine | Burns | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 79274 | Christine | Burns | Gregory Larson | 18.05 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Sandrine Hanson | 16.65 | +-------+------------+-----------+-----------------+--------+ | 92804 | Charles | Pressmann | Gregory Larson | 18.05 | +=======+============+===========+=================+========+ Press any key to continue . . . The second technique of creating a cross join involves two lists that have a relationship. You start with a class that can be used as the basis of a list. Here is an example of a class that would be used to create a list of movie (or video) directors: 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;
}
}
Before creating the join, you should (must) have a member from each class so that these two members will be used to join the lists. These members should hold unique values. In the above class, that would be the role of the TeacherID property, where we would make sure that each record has a unique TeacherID value. When creating the other class, you should (must) create a property (or field) that represents the unique-value provider of the first class. In our example, when creating a class that will be used to create a list of students, we must create a property (or field) that represents the TeacherID of the first class. The class can be created as follows: 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;
}
}
To create a cross join, apply the square brackets to the from and the join statements to indicate the index by which the records would be established (you can use the square brackets if the lists were created as arrays or as List<>). Here is an example of creating a cross join: using System;
using System.Linq;
using System.Collections.Generic;
public class Exercise
{
[STAThread]
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 teachers[1].TeacherID equals students[1].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 |");
foreach (var person in people)
{
Console.WriteLine("+-------+------------+-----------+-----------------+");
Console.WriteLine("| {0,5} | {1,-10} | {2,-9} | {3,-15} |",
person.StdNbr, person.FirstName, person.LastName,
person.TeacherName);
}
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: +=======+============+===========+=================+ | Std # | First Name | Last Name | Teacher Name | +-------+------------+-----------+-----------------+ | 82495 | Carlton | Blanchard | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 20857 | Jerrie | Sachs | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 20935 | Charlotte | O'Keefe | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 79274 | Christine | Burns | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 79204 | Bobbie | Swanson | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 14815 | Marianne | Swanson | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 24958 | Jeannette | Perkins | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 24759 | Pierrette | Perkins | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 92804 | Charles | Pressmann | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 80074 | Alain | Goodson | Sandrine Hanson | +-------+------------+-----------+-----------------+ | 82495 | Carlton | Blanchard | Gregory Larson | +-------+------------+-----------+-----------------+ | 20857 | Jerrie | Sachs | Gregory Larson | +-------+------------+-----------+-----------------+ | 20935 | Charlotte | O'Keefe | Gregory Larson | +-------+------------+-----------+-----------------+ | 79274 | Christine | Burns | Gregory Larson | +-------+------------+-----------+-----------------+ | 79204 | Bobbie | Swanson | Gregory Larson | +-------+------------+-----------+-----------------+ | 14815 | Marianne | Swanson | Gregory Larson | +-------+------------+-----------+-----------------+ | 24958 | Jeannette | Perkins | Gregory Larson | +-------+------------+-----------+-----------------+ | 24759 | Pierrette | Perkins | Gregory Larson | +-------+------------+-----------+-----------------+ | 92804 | Charles | Pressmann | Gregory Larson | +-------+------------+-----------+-----------------+ | 80074 | Alain | Goodson | Gregory Larson | +-------+------------+-----------+-----------------+ | 82495 | Carlton | Blanchard | Charles Nanze | +-------+------------+-----------+-----------------+ | 20857 | Jerrie | Sachs | Charles Nanze | +-------+------------+-----------+-----------------+ | 20935 | Charlotte | O'Keefe | Charles Nanze | +-------+------------+-----------+-----------------+ | 79274 | Christine | Burns | Charles Nanze | +-------+------------+-----------+-----------------+ | 79204 | Bobbie | Swanson | Charles Nanze | +-------+------------+-----------+-----------------+ | 14815 | Marianne | Swanson | Charles Nanze | +-------+------------+-----------+-----------------+ | 24958 | Jeannette | Perkins | Charles Nanze | +-------+------------+-----------+-----------------+ | 24759 | Pierrette | Perkins | Charles Nanze | +-------+------------+-----------+-----------------+ | 92804 | Charles | Pressmann | Charles Nanze | +-------+------------+-----------+-----------------+ | 80074 | Alain | Goodson | Charles Nanze | +=======+============+===========+=================+ Press any key to continue . . . Notice that each record of the parent class (the teacher) is associted to each record of the child class (the student).
You may have a list where some records don't have a value for some fields. You can make your final list include only records that have a value for the child. This is called an inner join. To create an inner join, you start with a normal join as done for a cross join. For the join formula, instead of joining the fields using an index, access the key from the value of the join clause and assign it to the corresponding key from the value of the from statement. 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 . . .
Instead of showing only records that have entries in the child list, you may want your resulting list to include all records, including those that have a 0 value or are empty. To get this, you would create an outer join. To suport outer joins, the Enumerable class is equipped with a method named DefaultIfEmpty. Its syntax is: public static IEnumerable The source is the list that calls this method. 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 . . . To restrict the result, you can add a where condition to a join. Here an example: var people = from lstTeachers
in teachers
join std in students on lstTeachers.TeacherID equals std.TeacherID into TeacherGroup
from teaching in TeacherGroup.DefaultIfEmpty()
where teaching.TeacherID == 2
select new
{
TeacherNbr = lstTeachers.TeacherID,
TeacherName = lstTeachers.FullName,
StudentNbr = teaching.StudentNumber,
FirstName = teaching.FirstName,
LastName = teaching.LastName,
};
This would produce: +===========+=================+=======+===========+===========+ | Teacher # | Teacher Name | Std # | First Name| Last Name | +===========+=================+=======+===========+===========+ | 2 | Gregory Larson | 82495 | Carlton | Blanchard | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 20857 | Jerrie | Sachs | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 79204 | Bobbie | Swanson | +-----------+-----------------+-------+-----------+-----------+ | 2 | Gregory Larson | 80074 | Alain | Goodson | +-----------+-----------------+-------+-----------+-----------+ Press any key to continue . . . In this case, when showing the result, since you know the category it includes, you can omit that category in the select statement. 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 into TeacherGroup
from teaching in TeacherGroup.DefaultIfEmpty()
where teaching.TeacherID == 2
select new
{
// TeacherNbr = lstTeachers.TeacherID,
// TeacherName = lstTeachers.FullName,
StudentNbr = teaching.StudentNumber,
FirstName = teaching.FirstName,
LastName = teaching.LastName,
};
Console.WriteLine("+=======+===========+===========+");
Console.WriteLine("| Std # | First Name| Last Name |");
Console.WriteLine("+=======+===========+===========+");
foreach (var person in people)
{
Console.WriteLine("| {0,4} | {1,-9} | {2,-9} | ",
person.StudentNbr, person.FirstName, person.LastName);
Console.WriteLine("+-------+-----------+-----------+");
}
Console.WriteLine();
return 0;
}
}
This would produce: +=======+===========+===========+ | Std # | First Name| Last Name | +=======+===========+===========+ | 82495 | Carlton | Blanchard | +-------+-----------+-----------+ | 20857 | Jerrie | Sachs | +-------+-----------+-----------+ | 79204 | Bobbie | Swanson | +-------+-----------+-----------+ | 80074 | Alain | Goodson | +-------+-----------+-----------+ Press any key to continue . . .
|
|||||||||||||||||||||||||
