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:

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

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 between two tables, this will be particularly useful.

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

In addition to joining two tables, you can also: