Take the 2-minute tour ×

There is a LINQ query:

int criteria = GetCriteria();
var teams = Team.GetTeams()
    .Where(team=> criteria == 0 || team.Criteria == criteria)

Does it make sense from performance (or any other point of view) to convert it into the following?

var teams = Team.GetTeams();
if (criteria != 0)
    teams = teams.Where(team => team.Criteria == criteria);
teams = teams.ToList();

I have a solid set of criteria; should I separate them and apply each criteria only if necessary? Or just apply them in one LINQ query and leave up-to .NET to optimize the query?

Please advise. Any thoughts are welcome!

P.S. Guys, I don't use Linq2Sql, just LINQ, that is a pure C# code

share|improve this question
Why not to an iterative example that measures the time it takes? –  Jeff Halverson Apr 23 '12 at 5:28
The performance difference in this case would be minute. I would write for readability, which since you've got optional filters, I would optionally add them as per method 2 for clarity. –  Will Apr 23 '12 at 5:35
I could make an example, but it would be artificial. Would like to get theory first –  Budda Apr 23 '12 at 14:46
add comment

2 Answers

up vote 5 down vote accepted

First, it is important to understand that LINQ actually comes in several varieties, which can mostly be divided by whether they use expression trees or compiled code to execute the query.

Linq2Sql (as well as any Linq provider that converts the query into another form to perform the query) use expression trees so that the query can be analyzed and converted into another form (often SQL). In this case it is possible for the provider to modify the query, potenitally performing some level of optimization. I am not aware of any providers that currently do this.

Linq to Objects uses compiled code, and will always execute the query as written. There is no opportunity for the query to be optimized other than by the developer.

Second, all Linq queries are deferred. That means that the query is not actually executed until an attempt to get results. A side effect of this is that you can build a query in several steps, and only the final query will be executed. The second example in the question still results in only one query being executed.

If you are using Linq2Sql then it is likely that both queries have roughly similar performance. However, the first example extended to handle many criteria could potentially lead to a poor overly general execution plan which ultimately degrades performance. The trimmed query does not run this risk, and will generate an execution plan for each real permutation of criteria.

If you are using Linq to Objects, then the second query is definitely preferable, since the first query will execute the predicate passed to Where once for every input even when the condition always returns true.

share|improve this answer
add comment

I think the 2nd option is better because it is checking the criteria value only once. Whereas in the first query , criteria is being checked for every row.
But I also believe that you will not get any significant performance gain from it. It may give you better results on tables with a high number of records (theoretically)

share|improve this answer
Moving a lot of data through an application is probably more expensive than letting LINQ/SQL do all the checks in one big pass. –  Oleksi Apr 23 '12 at 5:12
@pst, I am not too sure, I think in the 2nd case SQL query would be select * from table where criteria = <some non zero value> and in 2nd Case the query would be select * from table where criteria <> 0 AND criteria = <some value> –  Habib Apr 23 '12 at 5:12
@pst Yes, you can incrementally build expressions for any LINQ provider, since execution is deferred. –  Chris Pitman Apr 23 '12 at 5:13
Guys, I don't use Linq2Sql, just linq - that is a pure C# code –  Budda Apr 23 '12 at 5:30
add comment

Your Answer


