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.

Answer 2

It might result from porting the old code to linq2sql.

However, the two code snippets are not functionally equal.

Single will throw exception, while join yields an empty collection in case of missing record.

So, an equal code without using joins would be:

from c in context.Categories where c.Name == "red" from p in c.Products select p;

or

context.Categories.Where(c=>c.Name == "red").SelectMany(c=>c.Products);