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: