Queryable WHERE Contains String, Int, DateTime

Question

I am working this line of code:

query = query.Where(p => 
  p.ChckNumber.ToString().Contains(globalSearch.ToString()) || 
  p.BankAccount.ToString().Contains(globalSearch.ToString()) || 
  p.Description.ToString().Contains(globalSearch.ToString()) || 
  p.CheckAmount.ToString().Contains(globalSearch) || 
  p.ClearedDate.ToString().Contains(globalSearch.ToString()) || 
  p.SentDate.ToString().Contains(globalSearch.ToString()));

With this line of code I am basically doing a search, it seems to work fine when globalSearch is a string and the column like BankAccount and Description are varchar, however, when globalSearch is an Int or DateTime (-2233 or 4/9/2013) for the columns CheckAmount (int), ClearedDate (DateTime), SentDate (DateTime), it returns 0 rows, and if globalSearch matches an int in ChckNumber it works!

What Am I doing wrong?

I ran this query in SQL Server:

SELECT CONVERT(VARCHAR(MAX), ClearedDate) FROM myTable

and all my dates return Sep 9 2017 If globalSearch is Sep 10 2017 it works, but it does not work for the format I need as: 9/9/2017

Answer 1

You should not care if globalSearch is an Int because you should always convert it to a string, unless a DateTime and you should not care if a column is a varchar or an Int because you are always converting columns to strings, unless DateTime.

Note, I make the assumption, based on your comments that you are comparing just month,day and year and are not concerned about the time for ClearedDate

I see three potential issues with your code:

1. You do not convert globalSearch to a string for column CheckAmount
2. You do not check for nulls for any of your columns before calling .ToString, perhaps all of your columns do NOT Allow Nulls
3. Your code is not very readable it is hard for human eyes to spot inconsistencies.

Here is more readable code that converts globalSearch to a string once and every time because again, you do not convert globalSearch to a string for CheckAmount:

var gsStr = globalSearch.ToString();
var gsDate = DateTime.MinDate;
if(globalSearch.GetType() == typeof(DateTime))
{
    gsDate = globalSearch;
}
query = query.Where(p => p.ChckNumber.ToString().Contains(gsStr) 
|| p.BankAccount.ToString().Contains(gsStr) 
|| p.Description.ToString().Contains(gsStr) 
|| p.CheckAmount.ToString().Contains(gsStr) 
|| p.ClearedDate.Date == gsDate.Date
|| p.SentDate.ToString().Contains(gsStr));

If you are using C# 6.0 you can use the new Null Propagation Operator to protect from errors if any of your columns do allow nulls now or in the future:

var gsStr = globalSearch.ToString();
var gsDate = DateTime.MinDate;
if(globalSearch.GetType() == typeof(DateTime))
{
    gsDate = globalSearch;
}
query = query.Where(p => p.ChckNumber?.ToString().Contains(gsStr) 
|| p.BankAccount?.ToString().Contains(gsStr) 
|| p.Description?.ToString().Contains(gsStr) 
|| p.CheckAmount?.ToString().Contains(gsStr) 
|| p.ClearedDate?.Date == gsDate.Date
|| p.SentDate?.ToString().Contains(gsStr));

Converting globalSearch ToString for CheckAmount Contains may fix your issues, making your code more readable and protect against nulls will make it easier to debug if it does not fix bug.