stay Query Syntax for LINQ--from clause In this section, we describe how to join operations through multiple from clauses. Here we describe the keywords specifically used for join operations:

Join is used to determine equality based on an element from different data sources in order to join the two data sources together.

In the join clause, the equals keyword must be used to compare whether the specified keys are equal.

There are three main types of join in a join clause:

1) Internal joins, similar to SQL inner join s

2) Grouped joins, with an into expression join clause

3) Left outer join, similar to SQL left join

 

1) Let's look at an example of internal joins:

 

var productList = from category in categories
                  join product in products on category.Id equals product.CategoryId 
                  select new { category.Name, product.ProductName };

 

 

Compare the differences in how we join with multiple from clauses:

 

var productList = from category in categories
                  from product in products
                  where product.CategoryId == category.Id
                  select new {category.Name, product.ProductName};

 

 

It is important to note that equals is case sensitive, such as apple and Apple are not equal.

 

2) Group joins:

Grouping joins use an into clause, which means that the contents of a join are temporarily saved and can be used for subsequent operations or returned directly as a select.

Let's look at two examples:

 

List<Category> categories = new List<Category>
                                            {
                                                new Category{Id = 1,Name = "Food"},
                                                new Category{Id = 2,Name = "Toys"},
                                                new Category{Id = 3,Name = "Fruit"}
                                            };
            List<Product> products = new List<Product>
                                         {
                                             new Product{ProductId = 201,ProductName = "Chuckit",CategoryId = 2},
                                             new Product{ProductId = 202,ProductName = "SafeMade",CategoryId = 2},
                                             new Product{ProductId = 101,ProductName = "Taste",CategoryId = 1},
                                             new Product{ProductId = 102,ProductName = "Canidae",CategoryId = 1},
                                             new Product{ProductId = 103,ProductName = "Flavor",CategoryId = 1}

                                         };

            var productList = from category in categories
                              join product in products on category.Id equals product.CategoryId
                              into categoryProducts
                              select new { category.Name, Products= categoryProducts };

            foreach (var product in productList)
            {
                foreach (var item in product.Products)
                {
                    Console.WriteLine("{0}----{1}", product.Name, item.ProductName);    
                }
            }

 

 

Focus on query expressions and execution.

From the second foreach operation we can see that the category Product s after into are actually the sequence of roduct objects returned from the join clause.

We can also change the above example to:

 

var productList = from category in categories
                  join product in products on category.Id equals product.CategoryId
                  into categoryProducts
                  from product1 in categoryProducts 
                  select new { category.Name, product1.ProductName };

            foreach (var product in productList)
            {
                Console.WriteLine("{0}----{1}", product.Name, product.ProductName);    
            }

 

 

3) Left outer join

A left outer join is similar to a left join, in that the data source on the left outputs everything regardless of whether a match is found for the data source on the right.

In the left outer join, we need to use the DefaultIfEmpty keyword to specify how to define the return value of the right data source when the right data source does not match the desired value.

Or take the example above:

 

var productList = from category in categories
                  join product in products on category.Id equals product.CategoryId
                  into categoryProducts
                  from item in categoryProducts.DefaultIfEmpty(new Product{ProductId = 0,ProductName = string.Empty,CategoryId = 0})
                  select new { category.Name, item.ProductName };

 

 

In this example, if category does not find a matching product, productreturns an object like ProductId=0, CategoryId=0, ProductName="" by default.

 

 

To Be Continue...

 

Reference: webcast "Learn Visual Studio 2008 Series with me"