3

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.

Rufus L
  • 36,127
  • 5
  • 30
  • 43
Daniel Moreira
  • 430
  • 4
  • 17
  • 3
    This only works when the included table is linked via a foreign key, if you want to join two unrelated tables then you must do a join. – Gusman Apr 29 '15 at 00:50
  • 1
    I strongly suspect that the first query is actually producing *two* SQL queries - one that selects everything from `Products`, and another that selects everything from `Product_Categories`. The `Join` is happening entirely in memory, rather than on the server. – BJ Myers Apr 29 '15 at 04:01

1 Answers1

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.

DamienG
  • 6,575
  • 27
  • 43