Does place of Where clause matter in Linq query?

Question

I have this linq Query in a C# project

       var query = from p in db.Posts
                    join a in db.Author on p.Author equals a
                    join u in db.Users on p.PostedBy equals u
                    where p.IsActive.Equals(true)
                    orderby p.PostedDate descending
                    select new ViewModel
                    {
                        ...
                    };

If I move where p.IsActive.Equals(true) near from p in db.Posts, like

       var query = from p in db.Posts
                    where p.IsActive.Equals(true) //Moved
                    join a in db.Author on p.Author equals a
                    join u in db.Users on p.PostedBy equals u
                    orderby p.PostedDate descending
                    select new ViewModel
                    {
                        ...
                    };

will it make any difference to the performance of the query?

That would depend on the query provider. It would definitely affect LINQ to Objects since you would be joining to possibly less items than you would if you had filtered later in the query. — Jeff Mercado, Jul 22 '12 at 17:24
@MareInfinitus I'm using sql-server 2008 Database, so its Linq-to-SQL I guess... — Nalaka526, Jul 22 '12 at 17:28

score 1 · Accepted Answer · answered Jul 22 '12 at 17:20

1

I'm not sure that it will work if you'll do it, but if you are using it against a SQL database it should not make any difference. Both queries will be translated to the SQL like this one:

SELECT ...
FROM Posts INNER JOIN Author ON ... INNER JOIN Users ON ...
WHERE Posts.IsActive = true
...

answered Jul 22 '12 at 17:20

Sergey Rybalkin

3,004
22
26

It works even when it moved... anyway thanks for the answer..:) – Nalaka526 Jul 22 '12 at 17:32

score 1 · Answer 2 · edited May 23 '17 at 12:27

Concerning performance, I would strongly suggest to put the joins before any where.

The reason is, that the joins already make a smaller set (the p is selected from Posts and then only the matching rows from Author and Users are taken.

In opposite, if you have a where near the beginning a caresian (big) product is calculated and then filtered. (in your special case, only one table is affected, but the query may be altered sometime and nobody knows then why it is slow).

Please read this SO Why is LINQ join so much faster than LINQ where

And more on your special case: as this is affecting a database, the resulting SQL statements should be the same in both queries. Have a look at it!

Does place of Where clause matter in Linq query?

2 Answers2