CODEWITHSUNDEEP
c#sqllinqentity-framework
rexcfnghk
rexcfnghk

Reputation: 15452

Performance for using 2 where clauses in LINQ

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?

Upvotes: 20

Views: 3600

Answers (5)

Luke Merrett
Luke Merrett

Reputation: 5824

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

Upvotes: 20

Jodrell
Jodrell

Reputation: 35716

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.

Upvotes: 2

Kjartan
Kjartan

Reputation: 19111

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.

Upvotes: 1

anaximander
anaximander

Reputation: 7140

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.

Upvotes: 1

empi
empi

Reputation: 15881

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

Upvotes: 7

Related Questions