CODEWITHSUNDEEP
linqlinq-to-sql
Nick Kahn
Nick Kahn

Reputation: 20078

LINQ to SQL - WHERE clause filtered

Why is the where condition not applied in the following code? When I search, the results are not getting filtered. When I debug, searchString does have a value and execution is going through the _data.Where clause but it's not returning the filtered result.

What I'm doing wrong here?

IQueryable<UserViewModel> _data = (
    from a in context.aspnet_Users
    join b in context.aspnet_Membership on a.UserId equals b.UserId
    where b.IsApproved == true
    select new UserViewModel
    {                                 
        UserId = a.UserId,
        UserName = a.UserName,
        FirstName = a.FirstName,
        LastName = a.LastName,
        EmailAddress = b.Email                                 
    })
    .OrderBy(a => a.FirstName)
    .ThenBy(b => b.LastName);

if (!String.IsNullOrEmpty(searchString))
{                  
   _data = _data
        .Where(x => x.FirstName.Contains(searchString))
        .Where(y => y.UserName.Contains(searchString))
        .Where(y => y.LastName.Contains(searchString))
        .Where(y => y.EmailAddress.Contains(searchString));
}

Upvotes: 0

Views: 42

Answers (2)

David
David

Reputation: 219047

I suspect this is doing a lot more "filtering" than you expect:

_data = _data.Where(x => x.FirstName.Contains(searchString))
             .Where(y => y.UserName.Contains(searchString))
             .Where(y => y.LastName.Contains(searchString))
             .Where(y => y.EmailAddress.Contains(searchString));

This requires that searchString exist in all four of those fields. So if you search for, say, "David" then it's going to fail to find my record because my LastName doesn't contain that string.

Perhaps you wanted to use a logical or instead of a logical and? Something like this:

_data = _data.Where(x => x.FirstName.Contains(searchString) ||
                         x.UserName.Contains(searchString) ||
                         x.LastName.Contains(searchString) ||
                         x.EmailAddress.Contains(searchString));

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131712

The way the second query is written checks for records where all filtered fields start with the search string, ie for users whose FirstName and LastName and UserName and Email all start with the same string.

I think what you wanted was a query that check whether any of these fields starts with the search string, eg:

_data = _data.Where(x => x.FirstName.Contains(searchString) ||
                         x.UserName.Contains(searchString)  ||
                         x.LastName.Contains(searchString)  ||
                         x.EmailAddress.Contains(searchString));

Upvotes: 1

Related Questions