Joining two tables

Although an association is a kind of join in LINQ, we can also explicitly join two tables using the Join keyword, as shown in the following code snippet:

    static void TestJoin()
    {
        using(NorthwindEntities NWEntities = new NorthwindEntities())
       {
        var categoryProducts =
            from c in NWEntities.Categories
            join p in NWEntities.Products 
            on c.CategoryID equals p.CategoryID 
            into productsByCategory
            select new {
                c.CategoryName,
                productCount = productsByCategory.Count()
            };
            
        foreach (var cp in categoryProducts)
        {
          Console.WriteLine("There are {0} products in category {1}",
                cp.productCount, cp.CategoryName);
        }
       }
    }

This is not so useful in the previous example because the Products and Categories tables are associated with a foreign key relationship. If there is no foreign key association between two tables or if we had not added the associations between these two tables, this will be particularly useful.

From the following SQL statement, we can see that only one query is executed to get the results:

SELECT 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[CategoryName] AS [CategoryName], 
(SELECT 
  COUNT(1) AS [A1]
  FROM [dbo].[Products] AS [Extent2]
  WHERE [Extent1].[CategoryID] = [Extent2].[CategoryID]) AS [C1]
FROM [dbo].[Categories] AS [Extent1]

In addition to joining two tables, you can also:

  • Join three or more tables
  • Join a table to itself
  • Create left, right, and outer joins
  • Join using composite keys
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset