How to apply Contains( ) on a string array using LINQ to SQL c#

Question

Good Day!

string[] keywords = toolStripTextBoxSearch.Text.Split(' ');
IQueryable<employee> query = db.employees;
foreach (string keyword in keywords)
{
    query = query.Where(data => data.empName.Contains(keyword) 
                        || data.bank.bankCode.Contains(keyword) 
                        || data.bank.bankName.Contains(keyword) 
                        || data.department.deptName.Contains(keyword)
                        || data.department.deptCode.ToString().Contains(keyword) 
                        || data.designation.desigText.Contains(keyword) 
                        || data.empBankAccount.Contains(keyword) 
                        || data.empBasicSalary.Value.ToString().Contains(keyword) 
                        || data.empIncomeTax.ToString().Contains(keyword) 
                        || data.empName.Contains(keyword) 
                        || data.empNTN.Contains(keyword) 
                        || data.empTicketNumber.Contains(keyword) 
                        || data.grade.gradeText.Contains(keyword));
}

dataGridViewEmployee.DataSource = query;

Now if I write "a b" in the textbox, it shows no result while on writing "a" only or "b" only the gridview is showing multiple results.

you overwrite query every iteration with a new query, is the last keyword "b" or an empty character? at a quick guess you want `query.Where(d => keywords.Any(kw => data.empName.Contains(kw));` — Sayse, Aug 04 '14 at 06:31
The problem is that foreach keyword you are applying `AND`, so the result will be `(empName.Contains("a") OR empNTN.Contains("a") ...) AND (empName.Contains("b") OR empNTN.Contains("b") ...)` (because you are applying `Where` in a loop). — , Aug 04 '14 at 06:33
Can you better elaborate about the context and what you are trying to accomplish? For example you reference a gridview. Are you talking about an ASP.NET GridView or a winforms DataGridView. Can you show us some code that relates to it? — Alexander Ryan Baggett, Aug 04 '14 at 06:34
@Sayse your code encountered an exception An unhandled exception of type 'System.NotSupportedException' occurred in System.Data.Linq.dll Additional information: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. — Farhan Aslam, Aug 04 '14 at 06:37
@AlexanderRyanBaggett I'm working on winforms and having datagridview — Farhan Aslam, Aug 04 '14 at 06:38
@pwas yes, that's exactly what I am doing, now can you please let me know how can I change "AND" to OR? — Farhan Aslam, Aug 04 '14 at 06:38
Farhan, I didn't test it so its possible.. what does `keywords` contain? what should `a b` be returning? if its just those results that contain both then this should work. — Sayse, Aug 04 '14 at 06:40
I think I am unclear, Well.. I need to search a column from a table in the database that contains "a" or "b" not "a b" space is just a separator — Farhan Aslam, Aug 04 '14 at 06:42
Hmm the fatest way I think is you use DynamicLinq. I would build where predicate in a string builder (build with some loop), and then pass it to `Where` that accepts predicate as string. — , Aug 04 '14 at 06:45
@pwas can you please suggest any good article in understanding DynamicLINQ? this is a new term for me. Thanks — Farhan Aslam, Aug 04 '14 at 06:47

score 1 · Accepted Answer · answered Aug 04 '14 at 07:18

The reason why writing "a b" does not show any result is because the generatedSQL Query is probably not like how you wanted it to be.

When there's only one keyword, such as "a" or "b" only, the SQL query would be like:

SELECT empName, bankCode, bankName, ...
FROM Employees
WHERE (empName LIKE '%a%') OR (bankCode LIKE '%a%') OR (bankName LIKE '%a%') ...

But when there's more than one keywords, such as "a b", then the generated SQL query would be like:

SELECT empName, bankCode, bankName, ...
FROM Employees
WHERE ((empName LIKE '%a%') OR (bankCode LIKE '%a%') OR (bankName LIKE '%a%') ...) 
      AND ((empName LIKE '%b%') OR (bankCode LIKE '%b%') OR (bankName LIKE '%b%') ...) ...

Notice the AND operator there, what you wanted here is probably an ALL OR condition...

One way to easily achieve what you wanted is by using a PredicateBuilder. Your updated example using PredicateBuilder would be something like this:

string[] keywords = toolStripTextBoxSearch.Text.Split(' ');
IQueryable<employee> query = db.employees;
Expression<Func<employee, bool>> predicate = PredicateBuilder.False<employee>();

foreach (string keyword in keywords)
{
  predicate = predicate.Or(data => data.empName.Contains(keyword) || data.bank.bankCode.Contains(keyword) || data.bank.bankName.Contains(keyword) || data.department.deptName.Contains(keyword) || data.department.deptCode.ToString().Contains(keyword) || data.designation.desigText.Contains(keyword) || data.empBankAccount.Contains(keyword) || data.empBasicSalary.Value.ToString().Contains(keyword) || data.empIncomeTax.ToString().Contains(keyword) || data.empName.Contains(keyword) || data.empNTN.Contains(keyword) || data.empTicketNumber.Contains(keyword) || data.grade.gradeText.Contains(keyword));
}

dataGridViewEmployee.DataSource = query.Where(predicate);

score 0 · Answer 2 · answered Aug 04 '14 at 06:36

string[] keywords = toolStripTextBoxSearch.Text.Split(' ');
IQueryable<employee> query = db.employees;

query = query.Where(data => keywords.Contains(data.empName) ||  
keywords.Contains(data.bank.bankCode) || 
keywords.Contains(data.bank.bankName) ||  
keywords.Contains(data.department.deptName) ||  
keywords.Contains(data.department.deptCode.ToString()) ||  
keywords.Contains(data.designation.desigText) ||  
keywords.Contains(data.empBankAccount) ||  
keywords.Contains(data.empBasicSalary.Value.ToString()) ||  
keywords.Contains(data.empIncomeTax.ToString()) ||  
keywords.Contains(data.empName) ||  
keywords.Contains(data.empNTN) ||  
keywords.Contains(data.empTicketNumber) ||  
keywords.Contains(data.grade.gradeText));

score 0 · Answer 3 · answered Aug 04 '14 at 06:54

On of the way is to use DynamicLinq:

using System.Linq.Dynamic;
// ....

var predicateBuilder = new StringBuilder();

for (var i = 0; i < keywords.Length; i++)
{
    predicateBuilder.AppendFormat("empName.Contains(@{0}) 
                                    OR data.bank.bankCode.Contains(@{0})
                                    OR data.bank.bankName.Contains(@{0})
                                    OR ... and so on", i);

    if (i < keywords.Length - 1)
    {
        predicateBuilder.Append(" OR ");
    }
}

query = query.Where(predicateBuilder.ToString(), 
                    keywords.Cast<object>().ToArray());

var result = (IEnumerable<YourClass>)query.ToList();

Cons

Not strongly typed.
You can make mistake in Prop name,
Refactor-not-friendly - you have to remember that prop name has changed and do it manually in string.. for example

So use it, when nobody will gave you better solution. More on this blog.

How to apply Contains( ) on a string array using LINQ to SQL c#

3 Answers3