LINQ Logical join VS inner join

Question

I want to know which one is better for performance:

//Logical
var query = from i in db.Item
            from c in db.Category
            where i.FK_IdCategory == c.IdCategory
            Select new{i.name, c.name};

or

//Join
var query2 = from i in db.Item
             join c in db.Category
             on c.ID equals i.FK_IdCategory
             Select new{i.name, c.name};

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Performance of the two queries really depends on which LINQ provider and which RDBMS you're using. Assuming SQL Server, the first would generate the following query:

select i.name, c.name
from Item i, Category c
where i.FK_idCategory = c.IdCategory

Whereas the second would generate:

select i.name, c.name
from Item i
inner join Category c
    on i.FK_idCategory = c.IdCategory

Which operate exactly the same in SQL Server as is explained in: Explicit vs implicit SQL joins

score 0 · Answer 2 · answered Apr 17 '14 at 00:04

This depends on the ORM you're using and how intelligent it is at optimizing your queries for your backend. Entity Framework can generate some pretty awful SQL if you don't do your linq perfectly, so I'd assume query2 is better.

The only way for you to know for sure would be to inspect the SQL being generated by the two queries. Eyeballing it, it looks like query1 would result in both tables being pulled in their entirety and then being filtered against each other in your application, while query2 will for sure generate an INNER JOIN in the query, which will let SQL Server do what it does best - set logic.

Is that FK_IdCategory field a member of an actual foreign key index on that table? If not, make it so (and include the name column as an included column in the index) and your query will be very highly performant.

score 0 · Answer 3 · answered Apr 17 '14 at 00:15

With linq2Sql or EntityFramework, you would probably do something like this:

var query = from i in db.Item
            select new {i.name, i.Category.Name}

This will generate a proper SQL inner join.

I do assume that there is a foreign key relation between Item and Category defined.

LINQ Logical join VS inner join

3 Answers3