Introduction
|
|
|
One of the most common operations you can perform on
records consists of joining them based on a condition. Joining records is
equivalent to creating a list of records from more that one set of records.
In order to do this, you specify the lists, you specify a value they have in
common, and you define the joining relationship.
|
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:
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:
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).
Inner Joins |
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 . . .
Outer Joins
|
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 DefaultIfEmpty(this IEnumerable source);
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 . . .
No comments :
Post a Comment