LINQ-to-SQL search on dynamic columns?
Using the namespace System.Linq.Dynamic
, I can build a general list of columns to search based on the columns present in the current usercontrol (the search grid we use in various places). The process is simple, take the list of columns visible to the current user, add the columns to the dynamic query expression in the where clause, see if the entire concatenated sequence contains the specified string.
This does 2 things, allowing the user to search with a single search box (google style) that works across all grids the user sees in the same way, and also to offload the search to the database.
Here's how it 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);
}
Imprinted "| ^ |" the string is something random to prevent searching in one column between matching to the next, eg. columns "Bo" "Bryant" matching the search "Bob", the search result is "Bo | ^ | Bryant", preventing the match.
Are nullbed issues the same problems that you have to face with DateTime? or Nullable, 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 is the DynamicQueryable part that explodes:
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 fix this so far is to replace the addition in the expression constructor with:
foreach (var s in grid.ColumnNames)
{
sb.AppendFormat("{0}({1} != null ? {1}.ToString() : string.Empty)",
sb.Length == 0 ? string.Empty : "+\"|^|\"+", s);
}
Since we are in LINQ to SQL, this leads to a bloated case statement. Considering the alternative of loading each object from the database for subsequent search, a case statement for 8-10 columns 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 he debugged this easier, although I can't remember why. Clear the question down to the current code.
source to share
I wonder if you can check Nullable<T>
and use the conditional? But I really wonder if it would be better to move away from Dynamic LINQ Library; 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)
LINQ in regular; note that this won't work with LINQ-to-Objects ( IEnumerable<T>
) due to zeros, but will probably be OK in LINQ-to-SQL; if you need it to work in LINQ-to-Objects too, that's ok - just need to add a few more details above (let me know).
source to share