LINQ-to-SQL Search on dynamic columns?

Question

Using the System.Linq.Dynamic namespace I am able to construct a generic column list to search based on the columns present in the current usercontrol (a searchable grid we use in various places). The process is simple, take the column list visible to the current user, append the columns into a dynamic query expression in a where clause, see if the entire concatenated sequence contains the specified string.

This achieves 2 things, letting the user search with a single search box (google style) that works across all grids the user sees in the same way, as well as offloading the search to the database.

Here's how it currently works (result = IQueryable<T> or IEnumerable<T>):

var se = GetGridSearchExpression(grid);
if (se != null) result = result.Where(se, grid.SearchText.ToLower());

private static string GetGridSearchExpression(Grid grid)
{
  if (grid.SearchText.IsNullOrEmpty()) return null;
  var sb = new StringBuilder();
  foreach (var s in grid.ColumnNames)
  {
    sb.AppendFormat("{0} {1} ",
      sb.Length == 0 ? string.Empty : "+\"|^|\"+", s);
  }
  return string.Format("({0}).ToLower().Contains(@0)", sb);
}

The printed "|^|" string is something random to prevent a search on a single column from matching across to the next, e.g. columns "Bo" "Bryant" from matching the search "Bob", the result searched against is "Bo |^| Bryant" preventing the match.

Nullables are where the problems come in, having a DateTime? or Nullable type for example results in the following error:

Expression of type 'System.Nullable`1[System.DateTime]' cannot be used for 
parameter of type 'System.Object' of method 
'System.String Concat(System.Object, System.Object)'

Here's the part of DynamicQueryable that's blowing up:

Expression GenerateStringConcat(Expression left, Expression right) {
  return Expression.Call(null,
    typeof (string).GetMethod("Concat", new[] {typeof (object), typeof (object)}),
    new[] {left, right});
}

The only way I've found to eliminate this thus far is to replace the append in the expression builder to:

  foreach (var s in grid.ColumnNames)
  {
    sb.AppendFormat("{0}({1} != null ? {1}.ToString() : string.Empty)", 
      sb.Length == 0 ? string.Empty : "+\"|^|\"+", s);
  }

Since we're in LINQ to SQL, this results in a bloated case statement. Given the alternative of loading every object from the DB to then search against, a case statement for 8-10 columns at most is acceptable.

Is there a cleaner or easier way to do all or any part of this?

Edited: Thanks Marc...I never use GetEnumerator anywhere in my code, always foreach or .ForEach()...but for some reason at the time it made debugging this easier, though I can't remember now why. Cleared up the question to the current code.

Answer 1

I wonder if you could test for Nullable<T> and use a conditional? But I actually wonder if moving away from the Dynamic LINQ Library would be better; consider (untested):

string[] columnNames = { "Name", "DoB" }; string query = "2008";

        var row = Expression.Parameter(typeof(Data), "row");
        Expression body = null;
        Expression testVal = Expression.Constant(query, typeof(string));
        foreach (string columnName in columnNames)
        {
            Expression col = Expression.PropertyOrField(row, columnName);
            Expression colString = col.Type == typeof(string)
                ? col : Expression.Call(col, "ToString", null, null);
            Expression colTest = Expression.Call(
                colString, "Contains", null, testVal);

            if (col.Type.IsClass)
            {
                colTest = Expression.AndAlso(
                    Expression.NotEqual(
                        col,
                        Expression.Constant(null, typeof(string))
                    ),
                    colTest
                );
            }
            else if (Nullable.GetUnderlyingType(col.Type) != null)
            { // Nullable<T>
                colTest = Expression.AndAlso(
                    Expression.Property(col, "HasValue"),
                    colTest
                );
            }
            body = body == null ? colTest : Expression.OrElse(body, colTest);
        }
        Expression<Func<Data, bool>> predicate
            = body == null ? x => false : Expression.Lambda<Func<Data, bool>>(
                  body, row);


        var data = new[] {
            new Data { Name = "fred2008", DoB = null},
            new Data { Name = "barney", DoB = null},
            new Data { Name = null, DoB = DateTime.Today},
            new Data { Name = null, DoB = new DateTime(2008,1,2)}
        };
        foreach (Data x in data.AsQueryable().Where(predicate))
        {
            Console.WriteLine(x.Name + " / " + x.DoB);
        }

Then you should be able to use Where(predicate) in regular LINQ; note that this won't work with LINQ-to-Objects (IEnumerable<T>) due to nulls, but will probably be OK in LINQ-to-SQL; if you need it to work in LINQ-to-Objects too that is fine - just need to add a few more details to the above (let me know).