I want to loop through a array of string values and build a linq expression
Where each item in the list is OR'ed together.
string[] search = new string[]{"A", "B", "C"};
foreach (string item in filterValues)
{
searchQuery = searchQuery.Where(s => s.Name.Contains(item));
}
The code above searched for "A" AND "B" AND "C"
I want to search for "A" OR "B" OR "C".
I know how to do this with Linq but I want to accomplish the same thing using extension methods.
I am late to the party but...
I recently created a blog around creating an IQueryable search extension method that enables the following syntax:
string[] search = new string[]{"A", "B", "C"};
var searchQuery = context.Users.Search(u => u.Name, search);
Update : START
I have since updated search extensions which has affected the way a search is performed to use a new fluent API. This means the above should now be written as
var searchQuery = context.Users.Search(u => u.Name)
.Containing("A", "B", "C");
More on the new api can be seen here: http://jnye.co/Posts/2030/searchextensions-search-strings-with-the-new-fluent-api
Update : END
http://jnye.co/Posts/8/generic-iqueryable-or-search-for-multiple-search-terms-using-expression-trees
https://github.com/ninjanye/SearchExtensions
I also have a nuget package that you can install from here:
http://www.nuget.org/packages/NinjaNye.SearchExtensions/
However, if you want to do this yourself you will need to do the following: Firstly you need to create the extension method
public static class QueryableExtensions
{
public static IQueryable<T> Search<T>(this IQueryable<T> source, Expression<Func<T, string>> stringProperty, params string[] searchTerms)
{
if (!searchTerms.Any())
{
return source;
}
Expression orExpression = null;
foreach (var searchTerm in searchTerms)
{
//Create expression to represent x.[property].Contains(searchTerm)
var searchTermExpression = Expression.Constant(searchTerm);
var containsExpression = BuildContainsExpression(stringProperty, searchTermExpression);
orExpression = BuildOrExpression(orExpression, containsExpression);
}
var completeExpression = Expression.Lambda<Func<T, bool>>(orExpression, stringProperty.Parameters);
return source.Where(completeExpression);
}
private static Expression BuildOrExpression(Expression existingExpression, Expression expressionToAdd)
{
if (existingExpression == null)
{
return expressionToAdd;
}
//Build 'OR' expression for each property
return Expression.OrElse(existingExpression, expressionToAdd);
}
}
This can then be used as follows:
string[] search = new string[]{"A", "B", "C"};
var searchQuery = context.Users.Search(u => u.Name, search);
This will create the or query you are after.
Hope this answer is still relevant to you.
var filterValues = new[] { "A", "B", "C" };
var values =
(from item in filterValues
from value in searchQuery
where value.Name.Contains(item)
select value)
.Distinct();
One approach for cases where you have a known number of where choices is simply gather the list of those you want to exclude, then apply the where method.
dim BASEQUERY = FROM blah in blahblahblah _
WHERE ....
select blah
For Each item In EXCLUDELIST
Dim sitem As String = item
BASEQUERY = BASEQUERY.Where(Function(py) py.YOURCOLUMN <> sitem)
Next
This will leave only those you want which is equivalent to a compound "or where" condition. Note: A high number of excluded items could be unreasonably slow.
I struggled with this today. Then I finally realized that if I made the array into a List of Strings I could do a join. A queryextender control (.NET 4) is calling the function below. Dont forget the order by... its important. you'll get an error if you don't use it.
Protected Sub FilterTestType(ByVal sender As Object, ByVal e As CustomExpressionEventArgs)
Dim _codes As List(Of String) = GetTestCodes()
e.Query = From _tests In e.Query.Cast(Of LabTest)()
Join _code In _codes On _tests.TestCode Equals _code
Order By _tests.LoadDate Descending
Select _tests
End Sub
I like this solution :
string[] search = new string[]{"A", "B", "C"};
foreach (string item in filterValues)
{
searchQuery = searchQuery.Where(s => s.Name.Contains(item)).Concat(searchQuery);
}
working good on my MVC3 WebApp
