Search datatable using Linq to dataset

Question

I have a datatable of user info. I am trying to perform a search that can be using last name, user's ID or user's role(s). I have a search textbox for name and ID and a search dropdown for user's role. Not all can be blank but any combincation can be used. I am using the following but don;t think it is right since it always returns the entire datatable:

dtUsers.CaseSensitive = false;
var results = dtUsers.AsEnumerable()
    .Where(r => r.Field<String>("LASTNAME").Contains(tbName.Text.Trim())
           ||   r.Field<String>("USERID").Contains(tbUserID.Text.Trim())
           ||   r.Field<String>("USERROLELIST").Contains(ddlRoles.SelectedItem.Text));            

dtUsers = results.CopyToDataTable();

What am I doing wrong? I also need to be able to do partial search on name and ID.

score 3 · Accepted Answer · answered Dec 17 '14 at 17:13

3

Modify your condition to check for empty string first (Use String.IsNullOrWhiteSpace) and then apply your filter like:

var results = dtUsers.AsEnumerable()
    .Where(r =>(!String.IsNullOrWhiteSpace(tbName.Text) && r.Field<String>("LASTNAME").Contains(tbName.Text.Trim())
           || (!String.IsNullOrWhiteSpace(tbUserID.Text) &&r.Field<String>("USERID").Contains(tbUserID.Text.Trim())
           || r.Field<String>("USERROLELIST").Contains(ddlRoles.SelectedItem.Text));

Similarly you can do that with the last condition as well like:

(ddlRoles.SelectedItem != null && 
 !String.IsNullOrWhiteSpace(ddlRoles.SelectedItem.Text) && 
  r.Field<String>("USERROLELIST").Contains(ddlRoles.SelectedItem.Text)

answered Dec 17 '14 at 17:13

Habib

219,104
29
407
436

Thank you Habib, works. It expected a complete match so I changed .Contains(...) to .IndexOf(...) and I can search for partial name or ID. However, even though I sepcified dtUser.CaseSensitive=false it expects exact case match. If I type "smi" it won't return user "Smith" but "Smi" will. – NoBullMan Dec 17 '14 at 19:15
For Case Insensitive Contains, use `IndexOf(tbName.Text, StringComparison.InvariantCultureIgnoreCast) > -1)` See: http://stackoverflow.com/questions/444798/case-insensitive-containsstring – Habib Dec 17 '14 at 19:18
@NoBullMan, as far as `CaseSenstive` property is concerned it will be used when you `DataTable.Select` `(CaseSensitive property affects string comparisons in sorting, searching, and filtering)`, it is will not effect any query based on LINQ – Habib Dec 17 '14 at 19:20
Just another question: the users can have more than one role, like "User, Helpdesk". There is also a role called NonRegisteredUser. If I select the role "User" from dropdown, it lists NonRegisteredUsers as well since the word "User" is there. Any way to prevent this? I can always change the role name to just NonRegistered but not a solution really. – NoBullMan Dec 17 '14 at 20:03
Instead of `Contains` use `Equals`, *it* also has an overload to specify case insensitive comparison. – Habib Dec 17 '14 at 20:05

Search datatable using Linq to dataset

1 Answers1