Dynamically filtering a column/property in to an EF 4.1 query using C#
I'm trying to create a generic "search engine" in C# using linq. I have a simple search engine that functions and look like the following.
var query = "joh smi";
var searchTerms = query.Split(new char[] { 开发者_如何学Go' ' });
var numberOfTerms = searchTerms.Length;
var matches = from p in this.context.People
from t in searchTerms
where p.FirstName.Contains(t) ||
p.LastName.Contains(t)
group p by p into g
where g.Count() == numberOfTerms
select g.Key;
I want it to be more generic so I can call it like this:
var matches = Search<Person>(dataset, query, p => p.FirstName, p => p.LastName);
I've gotten as far as the following, but it fails with a "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities." System.NotSupportedException.
static IEnumerable<T> Find<T>(IQueryable<T> items, string query,
params Func<T, string>[] properties)
{
var terms = query.Split(' ');
var numberOfParts = terms.Length;
foreach (var prop in properties)
{
var transformed = items.SelectMany(item => terms,
(item, term) => new { term, item });
// crashes due to this method call
var filtered = transformed.Where(p => prop(p.item).Contains(p.term));
items = filtered.Select(p => p.item);
}
return from i in items
group i by i into g
where g.Count() == numberOfParts
select g.Key;
}
I'm certain it's doable, there just has to be a way to compile i => i.FirstName
to an Expression<Func<T, bool>>
, but that's where my LINQ expertise ends. Does anyone have any ideas?
You should use a Predicate Builder to construct your Or
query, something like:
var predicate = PredicateBuilder.False<T>();
foreach (var prop in properties)
{
Func<T, string> currentProp = prop;
predicate = predicate.Or (p => currentProp(p.item).Contains(p.term));
}
var result = items.Where(predicate );
Look into using a Specification Pattern. Check out this blog. Specifically, look at the spec pattern he developed. This is a similar thought to @Variant where you can build a dynamic specification and pass it to your context or repository.
It turns out the content of the queries just needed to be 'Expanded'. I used a library I found here to expand the expressions. I think that allows Linq to Entities to translate it in to sql. You'll notice Expand gets called over and over again; I think all of them are necessary. It works, anyway. Code to follow:
using System.Linq.Expressions;
public static class SearchEngine<T>
{
class IandT<T>
{
public string Term { get; set; }
public T Item { get; set; }
}
public static IEnumerable<T> Find(
IQueryable<T> items,
string query,
params Expression<Func<T, string>>[] properties)
{
var terms = query.Split(new char[] { ' ' },
StringSplitOptions.RemoveEmptyEntries);
var numberOfParts = terms.Length;
Expression<Func<IandT<T>, bool>> falseCond = a => false;
Func<Expression<Func<IandT<T>, bool>>,
Expression<Func<IandT<T>, bool>>,
Expression<Func<IandT<T>, bool>>> combineOr =
(f, g) => (b) => f.Expand(b) || g.Expand(b);
var criteria = falseCond;
foreach (var prop in properties)
{
var currentprop = prop;
Expression<Func<IandT<T>, bool>> current = c =>
currentprop.Expand(c.Item).IndexOf(c.Term) != -1;
criteria = combineOr(criteria.Expand(), current.Expand());
}
return from p in items.ToExpandable()
from t in terms
where criteria.Expand(new IandT<T> { Item = p, Term = t })
group p by p into g
where g.Count() == numberOfParts
select g.Key;
}
}
It can be called via the following code:
var matches = Search<Person>(dataset, query, p => p.FirstName, p => p.LastName);
精彩评论