Proper Linq Where Clauses

Proper LINQ where clauses

The second one would be more efficient as it just has one predicate to evaluate against each item in the collection where as in the first one, it's applying the first predicate to all items first and the result (which is narrowed down at this point) is used for the second predicate and so on. The results get narrowed down every pass but still it involves multiple passes.

Also the chaining (first method) will work only if you are ANDing your predicates. Something like this x.Age == 10 || x.Fat == true will not work with your first method.

LINQ: One where clause versus multiple chained where clauses

I set up a test project using the model and context described in this article and logged the SQL for two different queries, following the pattern in your question. The queries I made were:

db.Blogs
    .Where(b => b.BlogId == 0)
    .Where(b => b.Name == "Foo");

and

db.Blogs
    .Where(b => b.BlogId == 0 && b.Name == "Foo");

The generated SQL for both queries is identical:

SELECT 
    [Extent1].[BlogId] AS [BlogId], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Blogs] AS [Extent1]
    WHERE (0 = [Extent1].[BlogId]) AND (N'Foo' = [Extent1].[Name])

So it seems (at least for simple cases like this), there is no significant performance difference one way or the other. I guess you could argue it takes the expression visitor a bit longer to examine your tree if you use the multiple Where approach, but this is negligible in the long run.

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

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.

adding where clauses to linq query

.Where() takes an expression:

if (!String.IsNullOrEmpty(sc.Message))
    query = query.Where(elog => elog.Message.Contains(sc.Message));

Multiple WHERE Clauses with LINQ extension methods

Two ways:

results = results.Where(o => (o.OrderStatus == OrderStatus.Open) &&
                             (o.CustomerID == customerID));

or:

results = results.Where(o => (o.OrderStatus == OrderStatus.Open))
                 .Where(o => (o.CustomerID == customerID));

I usually prefer the latter. But it's worth profiling the SQL server to check the query execution and see which one performs better for your data (if there's any difference at all).

A note about chaining the .Where() methods: You can chain together all the LINQ methods you want. Methods like .Where() don't actually execute against the database (yet). They defer execution until the actual results are calculated (such as with a .Count() or a .ToList()). So, as you chain together multiple methods (more calls to .Where(), maybe an .OrderBy() or something to that effect, etc.) they build up what's called an expression tree. This entire tree is what gets executed against the data source when the time comes to evaluate it.

Linq query and multiple where clauses with OR only first condition gets evaluated

Your linq query looks ok to me

public class Users
    {
      public string Username {get;set;}
      public string Userid {get;set;}
    }

void Main()
{
    var users = new List<Users>{new Users {Username="johndoe",Userid="123"},
                                new Users {Username="stevejobs",Userid="456"}
                                };

    var filter = users.Where(a => (a.Username.Equals("123") || a.Userid.Equals("123"))).ToList();
    filter.Dump();
    var filter2 = users.Where(a => (a.Username.Equals("456") || a.Userid.Equals("456"))).ToList();
    filter2.Dump();
}

Linq Query with a Where clause in an Include statement

You cant have a Where inside the Where, but you can use Any which will return a boolean

var result = ctx.Offenders
    .Include(o => o.Fees)
    .Include(o => o.ViolationOffenders)
    .Include(o => o.ViolationOffenders.Select(of => of.Violation))
    .Where(o => o.YouthNumber != "" && o.FirstName != "" 
        && o.Fees.Any(f=> f.Amount != null)) // here
    .ToList();

LINQ Conditional Where Clauses not working

Wow, I can't believe what the problem/solution was to my issue. Because I was using the same variable (vbVal) in the .WHERE clause, when it was time to get the data, the query was using the last value of vbVal and thus not returning any data back. I'm guessing that LINQ uses ByRef for variables so my query would end up as:

vbVal = form["filter1"]; {"North America"}
qryResults = qryResults.Where (w=>w.col1 == vbVal);
vbVal = form["filter2"]; {"USA"}
qryResults = qryResults.Where (w=>w.col2 == vbVal);
vbVal = form["filter3"]; {"New York"}
qryResults = qryResults.Where (w=>w.col2 == vbVal);

The query back to SQL would be:

Select col1, col2, col3 From dbTable 
Where col1 = "New York" and col2 = "New York" and col3 = "New York"

Assigning each filter option to a unique variable solved my problem.

Thank you all for providing solutions.

Applying multiple variable where clauses to linq query

The solution in this case which worked for me was to use the Dynamic Expression API to form up a query in the form of a string (akin to an SQL query), and run this directly on the query in the form query.Where("Status=hidden and BatchAgeDays>10");.

Although expression trees would be a usable solution, this form is less verbose and more readable, and overall a great deal less work to implement, and allows multiple clauses to be concatenated into a single call to Where.

https://github.com/kahanu/System.Linq.Dynamic/wiki/Dynamic-Expressions

---

Related Topics