Include method in LINQ is used for Left Join?

Question

I'm using Entity Framework 6, DotConnect for Oracle and i have these 2 queries:

First one, using a simple join (LINQ and Output SQL):

LINQ:

var joinQuery = Db.Products  
    .Join(Db.Product_Categories.AsEnumerable(), p => p.ProductID, 
        pc => pc.CategoryID, (pc, p) => new { pc, p })
    .ToList();

Output SQL:

SELECT * FROM Products

---

Second, using Include:

LINQ:

var includeQuery = Db.Products.Include("Product_Categories").ToList();

Output SQL:

SELECT * FROM Products 
    LEFT OUTER JOIN Product_Categories 
        ON Products.CategoryID = Product_Categories.CategoryID  

I am in doubt if i can always use "Include" method for left joins. This method is not clear for my.

Answer 1

In the first example the join should not have .AsEnumerable() on the end of it. By doing that you are causing EF to go and get all the records from Product_Categories and then doing the join in memory which can be very inefficient as it doesn't use any kind of index.

The second option you have isn't pure LINQ. Include is an EF-specific extension method that is not available in other providers.

So if you want common LINQ you could use with other DB providers go with option 1. If you want simpler syntax and okay with being EF specific option 2 might be better.