I am trying to write something that will do a "Contains" query on all properties of an entity framework model.
I am able to do the following for example with no issues:
var students = db.Students.AsQueryable();
var test = students.Where(x => x.ID.ToString().ToLower().Contains("1"));
I found a code sample online that does what I want, but it's old and I am getting an error that I can't seem to resolve.
LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.
I have read up on this error, but as you see above ToString is perfectly valid even when using an IQueryable (which is required in my case because I don't want to post filter the data).
I have the following method that runs without errors but then when you convert ToList() it fails:
private Expression<Func<T, bool>> getSearchExpression()
{
string search = _request.Search.Value;
var filterableColumnIds = new HashSet<string>(_request.Columns.Where(x => x.Searchable).Select(x => x.Data));
var filterProperties = _properties.Where(x => filterableColumnIds.Contains(x.Name)).ToList();
// When no filterProperties or search term(s), return a true expression
if (string.IsNullOrEmpty(search) || !filterProperties.Any())
{
return x => true;
}
var paramExpression = Expression.Parameter(typeof(T), "val");
Expression compoundOrExpression = Expression.Call(Expression.Property(paramExpression, filterProperties[0]),"ToString", null);
Expression compoundAndExpression = Expression.Call(Expression.Property(paramExpression, filterProperties[0]), "ToString", null);
MethodInfo convertToString = typeof(Convert).GetMethod("ToString", Type.EmptyTypes);
// Split search expression to handle multiple words
var searchTerms = search.Split(' ');
for (int i = 0; i < searchTerms.Length; i++)
{
var searchExpression = Expression.Constant(searchTerms[i].ToLower());
// For each property, create a contains expression
// column => column.ToLower().Contains(searchTerm)
var propertyQuery = (from property in filterProperties
let toStringMethod = Expression.Call(
Expression.Call(Expression.Property(paramExpression, property), convertToString, null),
typeof(string).GetMethod("ToLower", new Type[0]))
select Expression.Call(toStringMethod, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();
for (int j = 0; j < propertyQuery.Length; j++)
{
// Nothing to "or" to yet
if (j == 0)
{
compoundOrExpression = propertyQuery[0];
}
compoundOrExpression = Expression.Or(compoundOrExpression, propertyQuery[j]);
}
// First time around there is no And, only first set of or's
if (i == 0)
{
compoundAndExpression = compoundOrExpression;
}
else
{
compoundAndExpression = Expression.And(compoundAndExpression, compoundOrExpression);
}
}
// Create expression
return Expression.Lambda<Func<T, bool>>(compoundAndExpression, paramExpression);
}
So to clarify, I basically want the user to be able to type a single string in a textbox, and then show any rows that have a field that contains that string. My handler is generic so it needs to use reflection to get to the properties.
Aucun commentaire:
Enregistrer un commentaire