Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Should I use two "where" clauses or "&&" in my LINQ query?

Tags:

c#

.net

linq

When writing a LINQ query with multiple "and" conditions, should I write a single where clause containing && or multiple where clauses, one for each conditon?

static void Main(string[] args) {     var ints = new List<int>(Enumerable.Range(-10, 20));      var positiveEvensA = from i in ints                          where (i > 0) && ((i % 2) == 0)                          select i;      var positiveEvensB = from i in ints                          where i > 0                          where (i % 2) == 0                          select i;      System.Diagnostics.Debug.Assert(positiveEvensA.Count() ==                                           positiveEvensB.Count()); } 

Is there any difference other than personal preference or coding style (long lines, readability, etc.) between positiveEvensA and positiveEvensB?

One possible difference that comes to mind is that different LINQ providers may be able to better cope with multiple wheres rather than a more complex expression; is this true?

like image 209
Ðаn Avatar asked Mar 20 '09 01:03

Ðаn


People also ask

Can we use 2 conditions in WHERE clause?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition.

Can we use WHERE clause two times in SQL?

But yes, you can use two WHERE.

Can you use both WHERE HAVING?

You can create a WHERE clause and HAVING clause involving the same column. To do so, you must add the column twice to the Criteria pane, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.

Can we use OR in WHERE clause?

The WHERE clause can be combined with AND , OR , and NOT operators.


1 Answers

I personally would always go with the && vs. two where clauses whenever it doesn't make the statement unintelligible.

In your case, it probably won't be noticeble at all, but having 2 where clauses definitely will have a performance impact if you have a large collection, and if you use all of the results from this query. For example, if you call .Count() on the results, or iterate through the entire list, the first where clause will run, creating a new IEnumerable<T> that will be completely enumerated again, with a second delegate.

Chaining the 2 clauses together causes the query to form a single delegate that gets run as the collection is enumerated. This results in one enumeration through the collection and one call to the delegate each time a result is returned.

If you split them, things change. As your first where clause enumerates through the original collection, the second where clause enumerates its results. This causes, potentially (worst case), 2 full enumerations through your collection and 2 delegates called per member, which could mean this statement (theoretically) could take 2x the runtime speed.

If you do decide to use 2 where clauses, placing the more restrictive clause first will help quite a bit, since the second where clause is only run on the elements that pass the first one.

Now, in your case, this won't matter. On a large collection, it could. As a general rule of thumb, I go for:

  1. Readability and maintainability

  2. Performance

In this case, I think both options are equally maintainable, so I'd go for the more performant option.

like image 184
Reed Copsey Avatar answered Sep 22 '22 06:09

Reed Copsey