Is multiple .Where() statements in LINQ a performance issue?

Question

I am wondering if there are performance implications of multiple .Where() statements. For example I could write:

var contracts =  Context.Contract
    .Where(
        c1 =>
            c1.EmployeeId == employeeId
        )
    .Where(
        c1 =>
            !Context.Contract.Any(
                c2 =>
                    c2.EmployeeId == employeeId
                    && c1.StoreId == c2.StoreId
                    && SqlFunctions.DateDiff("day", c2.TerminationDate.Value, c1.DateOfHire.Value) == 1
                )
        )
    .Where(
        c1 =>
            !Context.EmployeeTask.Any(
                t =>
                    t.ContractId == c1.Id
                )
        );

Or alternatively I could combine them all into the one Where() clause, like so:

var contracts =  Context.Contract
    .Where(
        c1 =>
            c1.EmployeeId == employeeId
            && !Context.Contract.Any(
                c2 =>
                    c2.EmployeeId == employeeId
                    && c1.StoreId == c2.StoreId
                    && SqlFunctions.DateDiff("day", c2.TerminationDate.Value, c1.DateOfHire.Value) == 1
                )
            && !Context.Employee_Task.Any(
                t =>
                    t.ContractId == c1.Id
                )
        );

Does the chain of Where() clauses hurt performance or are they equivalent?

I think LINQ compiles both to a similar expression, regardless of how you build it (as long as you don't call ToList() or the sort somewhere in between 'Where's) — sinelaw, Jan 19 '11 at 13:06

Jon Skeet · Accepted Answer · 2015-03-02T06:45:12.897

27

In LINQ to Objects, there will be a very small performance hit, because basically the iterator chain will be longer - fetching the next element means going up a long chain of MoveNext() calls.

In LINQ to SQL and similar providers, I'd expect the same SQL to be generated either way, so it wouldn't impact performance there.

EDIT: Since writing this I've found out a bit more about the LINQ to Objects implementation - it's a little more complicated...

edited Mar 02 '15 at 06:45

answered Jan 19 '11 at 13:05

Jon Skeet

1,421,763
867
9,128
9,194

1

Allright. Thank you very much. I don't know if the example is the best one but sometimes I think I will find it easier to read and understand some queries if I chain several where statements that has nothing to do with each other. /John – John Jan 19 '11 at 13:32
@Jon, if I understand you correctly then, for Objects there's the chance for optimization as per the order of the where clauses (which in SQL doesn't exist since the optimiser does that for you)? I imagine in LINQ-to-Entities that optimisation is also handled by the back-end? – ekkis Sep 02 '11 at 20:00
3

@ekkis: Yes; if you put a cheap filter which gets rid of most candidate elements first, that will speed things up a lot. – Jon Skeet Sep 02 '11 at 20:04
@Jon, for those of use more used to having an optimiser, that's really useful to know. thanks! (you get an uptick) – ekkis Sep 06 '11 at 23:10

Is multiple .Where() statements in LINQ a performance issue?

1 Answers1

Linked