The shape of the output of a join clause depends on the specific type of join you are performing. The following are three most common join types:
Inner Join
The following example shows a simple inner equijoin. This query produces a flat sequence of "product name / category" pairs. The same category string will appear in multiple elements. If an element from categories has no matching products, that category will not appear in the results.
C#
var innerJoinQuery =
from category in categories
join prod in products on category.ID equalsprod.CategoryID
select new { ProductName = prod.Name, Category = category.Name }; //produces flat sequence
Group Join
A join clause with an into expression is called a group join.
C#
var innerGroupJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
select new { CategoryName = category.Name, Products = prodGroup };
A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.
If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.
If you just select the results of a group join, you can access the items, but you cannot identify the key that they match on. Therefore, it is generally more useful to select the results of the group join into a new type that also has the key name, as shown in the previous example.
You can also, of course, use the result of a group join as the generator of another subquery:
C#
var innerGroupJoinQuery2 =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
from prod2 in prodGroup
where prod2.UnitPrice > 2.50M
select prod2;
Left Outer Join
In a left outer join, all the elements in the left source sequence are returned, even if no matching elements are in the right sequence. To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. You can use null as the default value for any reference type, or you can specify a user-defined default type. In the following example, a user-defined default type is shown:
C#
var leftOuterJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
from item inprodGroup.DefaultIfEmpty(new Product { Name = String.Empty, CategoryID = 0 })
select new { CatName = category.Name, ProdName = item.Name };
The equals operator
A join clause performs an equijoin. In other words, you can only base matches on the equality of two keys. Other types of comparisons such as "greater than" or "not equals" are not supported. To make clear that all joins are equijoins, the join clause uses the equals keyword instead of the ==operator. The equals keyword can only be used in a join clause and it differs from the == operator in one important way. With equals, the left key consumes the outer source sequence, and the right key consumes the inner source. The outer source is only in scope on the left side of equals and the inner source sequence is only in scope on the right side.
Non-Equijoins
You can perform non-equijoins, cross joins, and other custom join operations by using multiple from clauses to introduce new sequences independently into a query. For more information, see How to: Perform Custom Join Operations.
Joins on object collections vs. relational tables
In a LINQ query expression, join operations are performed on object collections. Object collections cannot be "joined" in exactly the same way as two relational tables. In LINQ, explicit join clauses are only required when two source sequences are not tied by any relationship. When working with LINQ to SQL, foreign key tables are represented in the object model as properties of the primary table. For example, in the Northwind database, the Customer table has a foreign key relationship with the Orders table. When you map the tables to the object model, the Customer class has an Orders property that contains the collection of Orders associated with that Customer. In effect, the join has already been done for you.
Composite Keys
You can test for equality of multiple values by using a composite key. For more information, see How to: Join by Using Composite Keys. Composite keys can be also used in a group clause.
Example
The following example compares the results of an inner join, a group join, and a left outer join on the same data sources by using the same matching keys. Some extra code is added to these examples to clarify the results in the console display.
C#
classJoinDemonstration
{
#region Data
class Product
{
public string Name { get; set; }
public int CategoryID { get; set; }
}
class Category
{
public string Name { get; set; }
public int ID { get; set; }
}
// Specify the first data source.
List<Category> categories = newList<Category>()
{
new Category(){Name="Beverages", ID=001},
new Category(){ Name="Condiments", ID=002},
new Category(){ Name="Vegetables", ID=003},
new Category() { Name="Grains", ID=004},
new Category() { Name="Fruit", ID=005}
};
// Specify the second data source.
List<Product> products = newList<Product>()
{
new Product{Name="Cola", CategoryID=001},
new Product{Name="Tea", CategoryID=001},
new Product{Name="Mustard", CategoryID=002},
new Product{Name="Pickles", CategoryID=002},
new Product{Name="Carrots", CategoryID=003},
new Product{Name="Bok Choy", CategoryID=003},
new Product{Name="Peaches", CategoryID=005},
new Product{Name="Melons", CategoryID=005},
};
#endregion
static void Main(string[] args)
{
JoinDemonstration app = new JoinDemonstration();
app.InnerJoin();
app.GroupJoin();
app.GroupInnerJoin();
app.GroupJoin3();
app.LeftOuterJoin();
app.LeftOuterJoin2();
// Keep the console window open in debug mode.
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
void InnerJoin()
{
// Create the query that selects
// a property from each element.
var innerJoinQuery =
from category in categories
join prod in products on category.ID equalsprod.CategoryID
select new { Category = category.ID, Product = prod.Name };
Console.WriteLine("InnerJoin:");
// Execute the query. Access results
// with a simple foreach statement.
foreach (var item ininnerJoinQuery)
{
Console.WriteLine("{0,-10}{1}", item.Product, item.Category);
}
Console.WriteLine("InnerJoin: {0} items in 1 group.", innerJoinQuery.Count());
Console.WriteLine(System.Environment.NewLine);
}
void GroupJoin()
{
// This is a demonstration query to show the output
// of a "raw" group join. A more typical group join
// is shown in the GroupInnerJoin method.
var groupJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
select prodGroup;
// Store the count of total items (for demonstration only).
int totalItems = 0;
Console.WriteLine("Simple GroupJoin:");
// A nested foreach statement is required to access group items.
foreach (var prodGrouping ingroupJoinQuery)
{
Console.WriteLine("Group:");
foreach (var item in prodGrouping)
{
totalItems++;
Console.WriteLine(" {0,-10}{1}", item.Name, item.CategoryID);
}
}
Console.WriteLine("Unshaped GroupJoin: {0} items in {1} unnamed groups", totalItems, groupJoinQuery.Count());
Console.WriteLine(System.Environment.NewLine);
}
voidGroupInnerJoin()
{
var groupJoinQuery2 =
from category in categories
orderbycategory.ID
join prod in products on category.ID equals prod.CategoryID into prodGroup
select new
{
Category = category.Name,
Products = from prod2 in prodGroup
orderbyprod2.Name
select prod2
};
//Console.WriteLine("GroupInnerJoin:");
int totalItems = 0;
Console.WriteLine("GroupInnerJoin:");
foreach (var productGroup ingroupJoinQuery2)
{
Console.WriteLine(productGroup.Category);
foreach (var prodItem inproductGroup.Products)
{
totalItems++;
Console.WriteLine(" {0,-10} {1}", prodItem.Name, prodItem.CategoryID);
}
}
Console.WriteLine("GroupInnerJoin: {0} items in {1} named groups", totalItems, groupJoinQuery2.Count());
Console.WriteLine(System.Environment.NewLine);
}
voidGroupJoin3()
{
var groupJoinQuery3 =
from category in categories
join product in products on category.ID equals product.CategoryID into prodGroup
from prod in prodGroup
orderbyprod.CategoryID
select new { Category = prod.CategoryID, ProductName = prod.Name };
//Console.WriteLine("GroupInnerJoin:");
int totalItems = 0;
Console.WriteLine("GroupJoin3:");
foreach (var item ingroupJoinQuery3)
{
totalItems++;
Console.WriteLine(" {0}:{1}", item.ProductName, item.Category);
}
Console.WriteLine("GroupJoin3: {0} items in 1 group", totalItems, groupJoinQuery3.Count());
Console.WriteLine(System.Environment.NewLine);
}
voidLeftOuterJoin()
{
// Create the query.
var leftOuterQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
selectprodGroup.DefaultIfEmpty(new Product() { Name = "Nothing!", CategoryID = category.ID });
// Store the count of total items (for demonstration only).
int totalItems = 0;
Console.WriteLine("Left Outer Join:");
// A nested foreach statement is required to access group items
foreach (var prodGrouping inleftOuterQuery)
{
Console.WriteLine("Group:", prodGrouping.Count());
foreach (var item in prodGrouping)
{
totalItems++;
Console.WriteLine(" {0,-10}{1}", item.Name, item.CategoryID);
}
}
Console.WriteLine("LeftOuterJoin: {0} items in {1} groups", totalItems, leftOuterQuery.Count());
Console.WriteLine(System.Environment.NewLine);
}
voidLeftOuterJoin2()
{
// Create the query.
var leftOuterQuery2 =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
from item inprodGroup.DefaultIfEmpty()
select new { Name = item == null ? "Nothing!" : item.Name, CategoryID = category.ID };
Console.WriteLine("LeftOuterJoin2: {0} items in 1 group", leftOuterQuery2.Count());
// Store the count of total items
int totalItems = 0;
Console.WriteLine("Left Outer Join 2:");
// Groups have been flattened.
foreach (var item inleftOuterQuery2)
{
totalItems++;
Console.WriteLine("{0,-10}{1}", item.Name, item.CategoryID);
}
Console.WriteLine("LeftOuterJoin2: {0} items in 1 group", totalItems);
}
}
/*Output:
InnerJoin:
Cola 1
Tea 1
Mustard 2
Pickles 2
Carrots 3
Bok Choy 3
Peaches 5
Melons 5
InnerJoin: 8 items in 1 group.
Unshaped GroupJoin:
Group:
Cola 1
Tea 1
Group:
Mustard 2
Pickles 2
Group:
Carrots 3
Bok Choy 3
Group:
Group:
Peaches 5
Melons 5
Unshaped GroupJoin: 8 items in 5 unnamed groups
GroupInnerJoin:
Beverages
Cola 1
Tea 1
Condiments
Mustard 2
Pickles 2
Vegetables
Bok Choy 3
Carrots 3
Grains
Fruit
Melons 5
Peaches 5
GroupInnerJoin: 8 items in 5 named groups
GroupJoin3:
Cola:1
Tea:1
Mustard:2
Pickles:2
Carrots:3
Bok Choy:3
Peaches:5
Melons:5
GroupJoin3: 8 items in 1 group
Left Outer Join:
Group:
Cola 1
Tea 1
Group:
Mustard 2
Pickles 2
Group:
Carrots 3
Bok Choy 3
Group:
Nothing! 4
Group:
Peaches 5
Melons 5
LeftOuterJoin: 9 items in 5 groups
LeftOuterJoin2: 9 items in 1 group
Left Outer Join 2:
Cola 1
Tea 1
Mustard 2
Pickles 2
Carrots 3
Bok Choy 3
Nothing! 4
Peaches 5
Melons 5
LeftOuterJoin2: 9 items in 1 group
Press any key to exit.
*/
Remarks
Quelle::
