Joining two tables

Although associations are a kind of join in LINQ we can also explicitly join two tables using the keyword, Join, as shown in the following code:

    static void TestJoin()
    {
        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);
        }

        NWEntities.Dispose();
    }

This is not so useful in the above example because the tables, Products and Categories, are associated with a foreign key relationship. If there is no foreign key association...