CODEWITHSUNDEEP
c#sqllinq
Sergio Ramirez
Sergio Ramirez

Reputation: 340

LINQ Logical join VS inner join

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

Upvotes: 0

Views: 558

Answers (3)

Francois Beaussier
Francois Beaussier

Reputation: 568

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.

Upvotes: 0

Justin Niessner
Justin Niessner

Reputation: 245399

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

Upvotes: 1

dodexahedron
dodexahedron

Reputation: 4657

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.

Upvotes: 0

Related Questions