LINQ ignoring where clause with method syntax

Question

I need to add where clauses to my query if the user provides input for those values. So far I am trying to use the follow, but the where clause is ignored. By that I mean that only student with the last name starting with Lewis should be returned, but I am not getting that as a result.

            var query = db.STUDENT
            .Where(x => x.STUDENT_CHANGE_IND == null);

        if (!string.IsNullOrEmpty(lastName)) query.Where(x => x.STUDENT_LAST_NAME.StartsWith("Lewis"));
        if (!string.IsNullOrEmpty(firstName)) query.Where(x => x.STUDENT_FIRST_NAME.StartsWith(firstName));
        if (!string.IsNullOrEmpty(spridenId)) query.Where(x => x.STUDENT_ID.Contains(spridenId));

        var y = query.Take(10).ToList();

The results I get are:

Student ID LastName

002 GUAR - HEMAR    
021 GUAR - Citibank SLC 
08092017    DO NOT USE 92 number mess up    
099 GUAR - ChaseJPM 
161 GUAR - WELLSG   
191 GUAR - TERI

It is well known fact that nothing really works in C# - try https://www.bing.com/search?q=c%23+linq+where+does+not+work or even simple stuff like [C# string replace does not work](https://stackoverflow.com/questions/13277667/c-sharp-string-replace-does-not-work). :) — Alexei Levenkov, Nov 21 '17 at 16:38

score 7 · Accepted Answer · answered Nov 21 '17 at 16:06

7

You need to say

query = query.Where(x => x.STUDENT_LAST_NAME.StartsWith("Lewis"));

instead of just

query.Where(x => x.STUDENT_LAST_NAME.StartsWith("Lewis"));

answered Nov 21 '17 at 16:06

Dennis_E

8,751
23
29

1

Oh, man. Forehead palm. Of course I have to capture the modified value. Thank you! – Cindy Conway Nov 21 '17 at 16:36
1

@CindyConway Happens to everyone on occasion :) – Dennis_E Nov 21 '17 at 21:02

Matt Hensley · Answer 2 · 2017-11-21T16:44:48.777

Where clauses don't stack if they are called in separate operations. You will need to save the new results each time a Where() statement is executed to ensure that you are working with the filtered list.

Your updated logic would look like this:

var query = db.STUDENT.Where(x => x.STUDENT_CHANGE_IND == null);

if (!string.IsNullOrEmpty(lastName))
{
    query = query.Where(x => x.STUDENT_LAST_NAME.StartsWith("Lewis"));
}
if (!string.IsNullOrEmpty(firstName))
{
    query = query.Where(x => x.STUDENT_FIRST_NAME.StartsWith(firstName));
}
if (!string.IsNullOrEmpty(spridenId))
{
    query = query.Where(x => x.STUDENT_ID.Contains(spridenId));
}

var y = query.Take(10).ToList();

edited, thanks @EdPlunkett – Matt Hensley Nov 21 '17 at 16:45 — Matt Hensley, Nov 21 '17 at 16:45

LINQ ignoring where clause with method syntax

2 Answers2