where vs join. which one is better to use

Question

Currently I'm running some code and got some question about this. Below are the code listings of two LINQ to Entites queries.

Code listing A:

IQueryable list = 
    from tableProject in db.Project 
    select new {StaffInCharge = (
        from tableStaff in db.Staff
        where tableStaff.StaffId == tableProject.StaffInChargeId 
        select tableStaff.StaffName)};

Code listing B:

IQueryable list = 
    from tableProjectin db.Project 
    join tableStaff in db.Staff
        on tableProject.StaffInChargeId
        equal tableStaff.StaffId
    select new {StaffInCharge = tableStaff.StaffName};

What I want to figure out is which one will be better and faster if I have to select many column from many others table.

Thanks.

Your code wouldn't compile for either of them at the moment: `select new StaffInCharge = ...` isn't valid. Please update your code appropriately (ideally keeping it in a nicely formatted layout). — Jon Skeet, Jan 12 '12 at 08:16
See my own question: http://stackoverflow.com/questions/5551264/why-is-linq-join-so-much-faster-than-linking-with-where — Tim Schmelter, Jan 12 '12 at 08:21
thanks Steven for doing the layout.but who can teach me how to do the format layout? thanks a lot — 456qwe123asd, Jan 12 '12 at 08:43
thanks Tim Schmelter, based on the article. it is mean that Join is better than where clause right? but from the article that i see it is join before the select. what if it is join or where clause in the select statement? — 456qwe123asd, Jan 12 '12 at 08:48
@OysterLee: The article(actually my SO-Question) relates to LINQ-To-DataSet what is based on LINQ-To-Objects. Linq to SQL or Linq to Entities might be optimized by the DBMS in that way that a `where` clause has the same performance as a `join`. — Tim Schmelter, Jan 12 '12 at 09:39

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

1

this is the comment from @Tim Schmelter

"The article(actually my SO-Question) relates to LINQ-To-DataSet what is based on LINQ-To-Objects. Linq to SQL or Linq to Entities might be optimized by the DBMS in that way that a where clause has the same performance as a join."

and the link is Why is LINQ JOIN so much faster than linking with WHERE? i think it is very useful.

edited May 23 '17 at 11:48

Community

1
1

answered Jan 12 '12 at 16:17

456qwe123asd

191
4
17

where vs join. which one is better to use

1 Answers1