Why use LINQ Join on a simple one-many relationshi

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

Joins are probably more common for people coming from the Relational mindset rather than the object oriented one. If you think about your problem space from a object orientented perspective, it is much more natural to work with relationships where you can dot through the navigation: Employee.Customers.Orders.OrderItems.OrderItem than to deal with a bunch of joins. The original ADO.vNext whitepaper did a good job of discussing the advantages of using the model and associations rather than joins for dealing with your conceptual models. Unfortunately, I can't find that document at this point.

For me, joins are best used when you don't have natural associations between entities. For example, if you are trying to join items on unnatural keys (i.e. joining on the Customer.State and Shipping.State) you should use the join syntax (or SelectMany) rather than creating associations between your entities in this case.

One thing to be aware of is that the join syntax and using associations can cause differences in the kind of joins that are generated by the provider. Typically a Join translates into an Inner join and excludes items where there is no match on both sides of the join. To perform an (left) outer join, you use the DefaultIfEmpty extension.

Navigating through associations in a 1-0..* relationship on the other hand typically translate into Right Outer joins because the child collection could legitimately be empty and you would want to include the parent even if the child didn't exist. You would use !Any() to trap for cases were there aren't child records.

Answer 3

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);