C# LINQ Keywords - The join Clause

Following the from clause, you might have a join clause used to correlate data from two separate sources. Join operations are not typically needed in environments where objects are linked via hierarchies and other associative relationships. However, in the relational database world, there typically are no hard links between items in two separate collections, or tables, other than the equality between items within each record. That equality operation is defined by you when you create a join clause. Consider the following example:
<pre class="brush:csharp">
using System;
using System.Linq;
using System.Collections.Generic;

public class EmployeeId
{
public string Id { get; set; }
public string Name { get; set; }
}

public class EmployeeNationality
{
public string Id { get; set; }
public string Nationality { get; set; }
}

public class JoinExample
{
static void Main() {
// Build employee collection
var employees = new List<EmployeeId>() {
new EmployeeId{ Id = "111-11-1111",
Name = "Ed Glasser" },
new EmployeeId{ Id = "222-22-2222",
Name = "Spaulding Smails" },
new EmployeeId{ Id = "333-33-3333",
Name = "Ivan Ivanov" },
new EmployeeId{ Id = "444-44-4444",
Name = "Vasya Pupkin" }
};

// Build nationality collection.
var empNationalities = new List<EmployeeNationality>() {
new EmployeeNationality{ Id = "111-11-1111",
Nationality = "American" },
new EmployeeNationality{ Id = "333-33-3333",
Nationality = "Russian" },
new EmployeeNationality{ Id = "222-22-2222",
Nationality = "Irish" },
new EmployeeNationality{ Id = "444-44-4444",
Nationality = "Russian" }
};

// Build query.
var query = from emp in employees
join n in empNationalities
on emp.Id equals n.Id
orderby n.Nationality descending
select new {
Id = emp.Id,
Name = emp.Name,
Nationality = n.Nationality
};

foreach( var person in query ) {
Console.WriteLine( "{0}, {1}, \t{2}",
person.Id,
person.Name,
person.Nationality );
}
}
}
</pre>
In this example, I have two collections. The first one contains just a collection of employees and their employee identification numbers. The second contains a collection of employee nationalities in which each employee is identified only by employee ID. To keep the example simple, every piece of data is a string. Now, I want a list of all employee names and their nationalities and I want to sort the list by their nationality but in descending order. A join clause comes in handy here because there is no single data source that contains this information. But join lets us meld the information from the two data sources, and LINQ makes this a snap! In the query expression, I have highlighted the join clause. For each item that the range variable emp references (that is, for each item in employees), it finds the item in the collection empNationalities (represented by the range variable n) where the Id is equivalent to the Id referenced by emp. Then, my projector clause, the select clause, takes data from both collections when building the result and projects that data into an anonymous type. Thus, the result of the query is a single collection where each item from both employees and empNationalities is melded into one. If you execute this example, the results are as shown here:
<pre class="brush:text">
333-33-3333, Ivan Ivanov, Russian

444-44-4444, Vasya Pupkin, Russian
222-22-2222, Spaulding Smails, Irish

111-11-1111, Ed Glasser, American
</pre>
When your query contains a join operation, the compiler converts it to a Join extension method call under the covers unless it is followed by an into clause. If the into clause is present, the compiler uses the GroupJoin extension method which also groups the results. For more information on the more
esoteric things you can do with join and into clauses, reference the MSDN documentation on LINQ or see Pro LINQ: Language Integrated Query in C# 2008 by Joseph C. Rattz, Jr. (Apress, 2007).

There’s no reason you cannot have multiple join clauses within the query to meld data from multiple different collections all at once. In the previous example, you might have a collection that represents languages spoken by each nation, and you could join each item from the empNationalities collection with the items in that language’s spoken collection. To do that, you would simply have one join clause following another.

Source Of Information : Apress Accelerated C Sharp 2010