Why use LINQ Join on a simple one-many relationship?

Question

I've been using LINQ to SQL and Entity Framework for a few years and I've always mapped my database relationships to generate the relevant navigation properties. And I always use the navigation properties.

Am I missing something?

If I have a Category->Products one-many type relationship, I would use

var redProducts = context.Category.Single(c => c.Name = "red").Products;

I regularly see people doing manual joins, all over this site, in projects online, and various other websites.

var employer = from category in context.Categories
               join product in context.Products
               on category.CategoryId equals product.CategoryId
               where category.Name == "red"
               select product;

So - why? What are the benefits of using this Join syntax?

Answer 1

It's usually a mistake.

@George is correct that your two examples are functionally different in a way which has nothing to do with join vs non-join, however. But you could just as easily write:

var redProducts = context.Category
                         .Where(c => c.Name == "red")
                         .SelectMany(c => c.Products);

...which is functionally identical (but superior from a readability and maintainability POV) to your join example.