Performance for using 2 where clauses in LINQ

Question

In LINQ-to-Entities you can query entities by doing:

var students = SchoolContext.Students.Where(s => s.Name == "Foo" && s.Id == 1);

I know that behind the scenes it will be translated to SQL to something similar to:

SELECT *
FROM Students
WHERE Name = 'Foo' AND Id = 1

However is there a difference (with respect to performance) if I write:

var students = 
    SchoolContext.Students
        .Where(s => s.Name == "Foo")
        .Where(s => s.Id == 1);

Will it be translated to the same SQL query? From my understanding .Where() will return IEnumerable<T> so the second .Where() will filter the entities in-memory instead of translating the IQueryable<T> to SQL, is that correct?

score 19 · Accepted Answer · edited Oct 15 '13 at 12:09

19

The first .Where() clause will still return an IQueryable<T>. As long as you are operating on an IQueryable<T> it will continue building up the SQL query and execute it when the collection needs to be brought into memory (eg: as @anaximander stated when used in a foreach loop or ToList() operation.

Therefore:

SchoolContext.Students.Where(s => s.Name == "Foo").Where(s => s.Id == 1);

Still translates into:

SELECT *
FROM Students
WHERE Name = 'Foo' AND Id = 1

Whilst the below 2 statements will translate into the same query:

SchoolContext.Students.Where(s => s.Name == "Foo").Where(s => s.Id == 1);
SchoolContext.Students.Where(s => s.Name == "Foo" && s.Id == 1);

edited Oct 15 '13 at 12:09

Jean Hominal

15,333
4
51
85

answered Oct 15 '13 at 09:21

Luke Merrett

5,394
6
36
66

4

A good way to debug this without even using a profiler is to breakpoint your code and hover over the variable within Visual Studio, the way you'd normally do to see the value of something. It will show you the generated SQL when you expand it. – NibblyPig Oct 15 '13 at 09:39
3

@SLC: Note that what you say works only when using the modern Entity Framework's `DbContext` API, and the associated `DbQuery`. If he is using `ObjectContext` and the associated `ObjectQuery`, then a call to `ToTraceString` is required instead. (Reason: `DbQuery`'s implementation of `ToString` calls `ObjectQuery.ToTraceString`). – Jean Hominal Oct 15 '13 at 12:08
1

@SLC Is that a new feature for Entity Framework? When I used EF (circa EF 3.5), that definitely did not work - the only way to see the generated SQL was with an SQL profiler attached to the database *(or add some [convoluted code](http://stackoverflow.com/a/1412902/238419))* – BlueRaja - Danny Pflughoeft Oct 15 '13 at 15:01
Could be a new feature, or could be what @Jean mentioned above I guess. I'm using .NET 4.5 so I can't really tell you. – NibblyPig Oct 15 '13 at 16:06
@Danny: [`ObjectQuery.ToTraceString`](http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring.aspx) has existed since .NET 3.5 SP1. But it looks like that is what you are calling "convoluted code". (Well, I guess that is why they put that on the `ToString` call afterwards.) – Jean Hominal Oct 15 '13 at 20:32
Accepting this answer as it contains the most details – rexcfnghk Oct 16 '13 at 00:48

score 7 · Answer 2 · answered Oct 15 '13 at 09:19

7

First Where returns IQueryable<T> so there will be no performance difference.

answered Oct 15 '13 at 09:19

empi

15,115
7
57
75

score 2 · Answer 3 · edited May 23 '17 at 12:34

No, it will be the same query.

You can compose a more and more complex query by fluently chaining the IQueryables returned by each Linq operation. The statement is not generated and sent to the server until you evaluate the results.

You can check that the actual query generated by debugging and hovering over the query or doing a ToTraceString() or using a tool like SQLProfiler to monitor the database server.

score 1 · Answer 4 · answered Oct 15 '13 at 09:20

The two should produce the same SQL; IQueryable is smart in that it doesn't actually evaluate until it needs to. The second .Where() should add to the first, and then whenever you use .ToList(), .Count(), foreach or anything that needs to know what's in the IQueryable, it'll generate the SQL, query the database and give you the result.

score 1 · Answer 5 · answered Oct 15 '13 at 09:24

1

Get Linqpad, and try the different queries there. You can Add a connection to your entities directly, run the queries, and see which SQL is generated in each case. Excellent way to experiment with Linq.

answered Oct 15 '13 at 09:24

Kjartan

17,127
14
67
84

Performance for using 2 where clauses in LINQ

5 Answers5