CODEWITHSUNDEEP
c#linqlinq-to-entities
wotney
wotney

Reputation: 1046

LINQ to Entities - parse / filter object collection

I am trying to filter a list of users from my database.

When I run this LINQ to Entity command, it works as I had hoped. It returns all users, filtered by whatever is in txtFilterBy_UserName :

(notice that I am accessing the db table directly. There are also several fields to filter on, but I only include username in this example)

users = db.Users
          .Where(u => u.IsActive == true &&
                      u.UserName.ToLower()
                       .Contains((txtFilterBy_UserName.Value.Length > 0) ? 
                                  u.UserName.ToLower() : 
                                  txtFilterBy_UserName.Value.ToLower()))
          .ToList();

But... in some circumstances I have already populated my User collection (_users) before filtering, and I expected to be able to do this:

users = _users.Where(u => u.IsActive == true && 
                          u.UserName.ToLower()
                           .Contains((txtFilterBy_UserName.Value.Length > 0) ?
                                      u.UserName.ToLower() :
                                      txtFilterBy_UserName.Value.ToLower()))
              .ToList();

But this method always returns 0 users, can anyone tell me what I'm doing wrong, or why this method of filtering the user collection doesn't work ?

Let's say my DB has 100 users in it and 6 of those users have the characters "john" in their username. When I query the DB directly - I return 6 users. If the users collection object is already populated (with 100 users) and I try filtering the collection - 0 users are returned

Upvotes: 3

Views: 864

Answers (3)

Erik Schierboom
Erik Schierboom

Reputation: 16646

It looks like you got your query a bit mixed up. Looking at your code I suspect you want to do this:

  1. If txtFilterBy_UserName.Value.Length > 0 is true then you only want to return users which username contains txtFilterBy_UserName.Value. Essentially you will be doing filtering.
  2. If txtFilterBy_UserName.Value.Length > 0 is false then you want to return all users. Here you don't apply any filter.

If my assumption is correct, you could rewrite your query as follows:

users = db.Users.Where(u => u.IsActive == true);

if (txtFilterBy_UserName.Value.Length > 0))
{
    users = users.Where(u => u.UserName.ToLower().Contains(txtFilterBy_UserName.Value.ToLower()));
}

users = users.ToList();

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236268

Instead of using ternary operator for filtering, simply compose your query by conditionally adding Where filters:

var query = _users.Where(u => u.IsActive); // don't compare boolean with true
if (txtFilterBy_UserName.Value.Length > 0)
    query = query.Where(u => u.UserName.ToLower().Contains(txtFilterBy_UserName.Value.ToLower()));

var users = query.ToList();

And yes, I agree with @Abbas - your query looks fines. Probably you don't have any user matching your condition in local _users collection.

Upvotes: 2

Abbas
Abbas

Reputation: 14432

Does the collection _users contain any users at all (before filtering)? Also, your query looks weird to me, please correct me if I'm wrong:

u.UserName.ToLower().Contains((txtFilterBy_UserName.Value.Length > 0)
                              ? u.UserName.ToLower()
                              : txtFilterBy_UserName.Value.ToLower())

I read this as:

If the filter-textbox has a value (Length > 0) give me all the users where the lowercase username of any user contains the lowercase username of that user, otherwise (if no filter-value is provided) give me all the users where the lowercase username contains an empty value (string).

Upvotes: 1

Related Questions