应用基于多维数组的LINQ过滤器 [英] Applying LINQ filters based on a multi-dimensional array
问题描述
给出一个实体框架查询,例如
Given an entity framework query, such as
var query = (from property in _dbContext.Properties
join location in _db.Locations
on property.Id equals location.PropertyId
select new PropertyDetail
{
Url = property.Url,
Type = property.Type,
Title = property.Title,
Continent = location.Continent,
Country = location.Country,
State = location.State,
});
我应用了以下过滤器:
if (!string.IsNullOrWhitespace(searchFilters.Type))
{
query = query.Where(model => model.Type == searchFilters.Type);
}
if (!string.IsNullOrWhitespace(searchFilters.Title))
{
query = query.Where(model => model.Title.Contains(searchFilters.Title));
}
给出以下多维数组
var locations = new[]
{
new[] {"Africa", "Algeria", ""},
new[] {"Asia", "Hong Kong", ""},
new[] {"Asia", "Singapore", ""},
new[] {"Oceania", "Australia", "New South Wales"},
new[] {"North America", "United States", "California"}
};
如何进一步限制查询",使其仅包括与指定位置{Continent,Country,State(可选)}匹配的那些条目?
How can the "query" be further restricted to only include those entries that match the specified locations {Continent, Country, State(optional)} ?
推荐答案
不幸的是,LINQ to Entities当前不支持联接到内存集合,也不支持Contains
用于非原始内存集合.我看到的唯一方法(实际上这里还有另一种方法
Unfortunately LINQ to Entities currently does not support joins to inmemory collection, nor Contains
for non primitive inmemory collection. The only way I see (actually there is another one described here Entity Framework LINQ Get all items part of another collection, but now I think this is more appropriate) is to construct OR
filter using some expression build helper.
For instance, using the PredicateUtils
class from Establish a link between two lists in linq to entities where clause, it could be like this:
首先,添加一些辅助方法
First, add a little helper method
static Expression<Func<PropertyDetail, bool>> LocationFilter(string value, int index)
{
if (!string.IsNullOrEmpty(value))
{
if (index == 0) return d => d.Continent == value;
if (index == 1) return d => d.Country == value;
if (index == 2) return d => d.State == value;
}
return null;
}
然后使用
var locationsFilter = locations.Select(location => location.Select(LocationFilter)
.Aggregate(PredicateUtils.And)).Aggregate(PredicateUtils.Or);
if (locationsFilter != null)
query = query.Where(locationsFilter);
为完整起见,以下是使用的帮助程序类:
For completeness, here is the helper class used:
public static class PredicateUtils
{
sealed class Predicate<T>
{
public static readonly Expression<Func<T, bool>> True = item => true;
public static readonly Expression<Func<T, bool>> False = item => false;
}
public static Expression<Func<T, bool>> Null<T>() { return null; }
public static Expression<Func<T, bool>> True<T>() { return Predicate<T>.True; }
public static Expression<Func<T, bool>> False<T>() { return Predicate<T>.False; }
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
{
if (Equals(left, right)) return left;
if (left == null || Equals(left, True<T>())) return right;
if (right == null || Equals(right, True<T>())) return left;
if (Equals(left, False<T>()) || Equals(right, False<T>())) return False<T>();
var body = Expression.AndAlso(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));
return Expression.Lambda<Func<T, bool>>(body, left.Parameters);
}
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
{
if (Equals(left, right)) return left;
if (left == null || Equals(left, False<T>())) return right;
if (right == null || Equals(right, False<T>())) return left;
if (Equals(left, True<T>()) || Equals(right, True<T>())) return True<T>();
var body = Expression.OrElse(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));
return Expression.Lambda<Func<T, bool>>(body, left.Parameters);
}
static Expression Replace(this Expression expression, Expression source, Expression target)
{
return new ExpressionReplacer { Source = source, Target = target }.Visit(expression);
}
class ExpressionReplacer : ExpressionVisitor
{
public Expression Source;
public Expression Target;
public override Expression Visit(Expression node)
{
return node == Source ? Target : base.Visit(node);
}
}
}
更新:按照注释中的要求,这是locations
是List<Location>
的解决方案:
UPDATE: As requested in the comments, here is the solution for locations
being List<Location>
:
var locationsFilter = locations.Select(location =>
{
var filter = PredicateUtils.Null<PropertyDetail>();
if (!string.IsNullOrEmpty(location.Continent))
filter = filter.And(d => d.Continent == location.Continent);
if (!string.IsNullOrEmpty(location.Country))
filter = filter.And(d => d.Country == location.Country);
if (!string.IsNullOrEmpty(location.State))
filter = filter.And(d => d.State == location.State);
return filter;
}).Aggregate(PredicateUtils.Or);
这篇关于应用基于多维数组的LINQ过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!