Conventions for joins in Entity Framework 6.1.0 Co

I believe my question is simple (not sure if the answer is as well):

Does anybody know how to force Entity Framework to use "INNER JOIN" as default convention, instead of "LEFT OUTER JOIN"?

回答1:

If you want to enforce inner or outer joins explicitly you can always use the Join or GroupJoin methods, respectively. (Or the comprehensive equivalents join and join ... into).

But, generally speaking, in LINQ statements you should avoid using explicit join statements. Use navigation properties instead. Navigation properties are the associations that have been defined between entities. Querying by navigation properties is querying by joins without hand-coding them. But when will these joins be inner or outer?

Take these three simple classes:

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }

    public int? PhotoId { get; set; }
    public Photo Photo { get; set; }
}

class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Product> Products { get; set; }
}

class Photo
{
    public int Id { get; set; }
    public byte[] Image { get; set; }
}

What will happen if we query products including their categories and photos?

context.Products.Include(p => p.Category).Include(p => p.Photo)

Entity Framework looks at the cardinality of an association to determine whether it can generate the preferable inner join, or else an outer join:

A required association generates an inner join.
An optional association generates an outer join.

So in this case it will generate an inner join between Product and Category and an outer join between Product and Photo.

I think this is a sensible choice. It would be unexpected behaviour if including photos in the result set would suddenly reduce the number of fetched items.

The same applies to other query shapes, like

context.Products.Select(p => new { p.Name, Cat = p.Category.Name })

This generates an inner join.

context.Products.Select(p => new { p.Name, Cat = p.Photo.Image })

This generates an outer join.

EF isn't smart enough (yet?) to see that it could generate an inner join if in the latter example you'd only query products where Photo != null. That could be a case where you'd want to write an explicit LINQ join.

Conclusion

So this focus on navigation properties shifts your attention from...

how to force Entity Framework to use "INNER JOIN"

...to more business-logic related decisions whether associations should be required or optional.

回答2:

The join clause produces Inner join by default.

from x in table1
join y in table2 on x.id equals y.id

To make it act as outer join, you'd use DefaultIfEmpty method like this:

from x in table1
join y in table2 on x.id equals y.id into jointable
from z in jointable.DefaultIfEmpty()

If you add the query you are executing to your question, we can figure out why does Left Outer Join gets generated.

Here's nice resource explaining things: http://msdn.microsoft.com/en-us/library/bb311040.aspx