LINQ过滤器结合了完全匹配项，例如SQL IN和StartsWith匹配项 [英] LINQ filter combining exact matches like SQL IN and StartsWith matches
问题描述
我拥有产品实体:
public class Product : DomainBase
{
public virtual string Name { get; set; }
}
应该有按过滤器选择产品的选项，该过滤器包含名称数组，例如:
And there should be option to select products by filter, which contains an array of names, like:
public static IEnumerable<Product> SearchArrayQueryLinq(IEnumerable<string> names)
{
using (var session = Database.OpenSession())
{
var products = session.Query<Product>();
var result = products.Where(product => names.Any(name => product.Name.Contains(name)));
return result.ToList();
}
}
但是会抛出
System.NotSupportedException:不支持指定的方法.
System.NotSupportedException: Specified method is not supported.
完成这种过滤的正确方法是什么?
What is right approach, to accomplish such filtering?
推荐答案
在不了解您要连接到哪个数据库或哪个库的更多信息(是RavenDB ..已经做了一个快速的Google吗?)下，很难完全做到这一点确定问题出在哪里.
Without knowing more about what database you're connecting to or what library (is it RavenDB.. having done a quick Google?) then it's hard to be completely sure what the problem is.
但是，我认为正在发生的事情是您正在向IQueryable"Where"扩展方法提供一个表达式，并且库正在尝试将其转换为搜索条件以针对db运行..并失败了，因为"Any"是像这样的嵌套条件中不支持(再次，我猜是这样).
However, what I think is happening is that you are giving an expression to the IQueryable "Where" extension method and the library is trying to turn that into search criteria to run against the db.. and failing because "Any" is not supported in nested criteria like that (again, I'm guessing).
可能或可能不会翻译成数据库语言(例如SQL)的LINQ表达式因执行翻译的库而异，并且与所交谈的数据库也有所不同.
The LINQ expressions that may or may not be translated into the database language (eg. SQL) vary by the library that performs the translation and vary by the database being talked to.
例如，以下(基本上就是您想要做的)在Entity Framework上可以正常工作:
For example, the following (which is basically what you want to do) works fine with Entity Framework:
private static void Test(IEnumerable<string> names)
{
using (var context = new NORTHWNDEntities())
{
foreach (var product in context.Products.Where(product => names.Any(name => product.ProductName.Contains(name))))
{
Console.WriteLine(product.ProductName);
}
}
Console.ReadLine();
}
一个简单的选择是将代码更改为
One easy option for you is to change your code to
public static IEnumerable<Product> SearchArrayQueryLinq(IEnumerable<string> names)
{
using (var session = Database.OpenSession())
{
var products = session.Query<Product>();
return result = products.ToList().Where(product => names.Any(name => product.Name.Contains(name)));
}
}
这应该工作..但是，它将从数据库中获取 all 个产品并在内存中执行过滤.这比让数据库执行搜索效率低.
This should work.. however, it will get all Products from the database and perform the filtering in-memory. This is less efficient than getting the database to perform the search.
一种替代方法是生成"Expression< Func<乘积，bool>".过滤自己，这对于您要翻译的库来说更容易.如果不是嵌套的任何"条件，而是可以生成一组简单的或"名称检查，则可以对其进行更好的更改.以下将实现这一点-但其中有很多代码.如果您需要在多个地方执行此操作，则可以使某些代码更通用并重用.
An alternative would be to generate an "Expression<Func<Product, bool>>" filter yourself that is easier for the library that you're using to translate. If, instead, of a nested "Any" criteria, you could generate a simple set of "OR" name checks then there is a better change of it working. The following will achieve that - but it's quite a lot of code. If this is something that you need to do in several places then some of the code could be made more general and reused.
private static IEnumerable<Product> SearchArrayQueryLinq(IEnumerable<string> names)
{
using (var context = new NORTHWNDEntities())
{
return context.Products.Where(GetCombinedOrFilter(names)).ToList();
}
}
private static Expression<Func<Product, bool>> GetCombinedOrFilter(IEnumerable<string> names)
{
var filter = GetNameFilter(names.First());
foreach (var name in names.Skip(1))
filter = CombineFiltersAsOr(filter, GetNameFilter(name));
return filter;
}
private static Expression<Func<Product, bool>> GetNameFilter(string name)
{
return product => product.ProductName.Contains(name);
}
private static Expression<Func<Product, bool>> CombineFiltersAsOr(Expression<Func<Product, bool>> x, Expression<Func<Product, bool>> y)
{
// Combine two separate expressions into one by combining as "Or". In order for this to work, instead of there being a parameter
// for each expression, the parameter from the first expression must be shared between them both (otherwise things will go awry
// when this is translated into a database query) - this is why ParameterRebinder.ReplaceParameters is required.
var expressionParameter = x.Parameters.Single();
return Expression.Lambda<Func<Product, bool>>(
Expression.Or(x.Body, ParameterRebinder.ReplaceParameters(y.Body, toReplace: y.Parameters.Single(), replaceWith: expressionParameter)),
expressionParameter
);
}
// Borrowed and tweaked from https://blogs.msdn.microsoft.com/meek/2008/05/02/linq-to-entities-combining-predicates/
public sealed class ParameterRebinder : ExpressionVisitor
{
public static Expression ReplaceParameters(Expression expression, ParameterExpression toReplace, ParameterExpression replaceWith)
{
return new ParameterRebinder(toReplace, replaceWith).Visit(expression);
}
private readonly ParameterExpression _toReplace, _replaceWith;
private ParameterRebinder(ParameterExpression toReplace, ParameterExpression replaceWith)
{
_toReplace = toReplace;
_replaceWith = replaceWith;
}
protected override Expression VisitParameter(ParameterExpression p)
{
if (p == _toReplace)
p = _replaceWith;
return base.VisitParameter(p);
}
}
更新:我没有注意到您的nhibernate标签-糟糕！使用nhibernate具有的标准组合方法可能比这更容易..::)我本来会对您的答案发表评论，而不是更新自己的答案，但我还没有必要的50个代表..
Update: I didn't notice your nhibernate tag - whoops! Using the criteria combining methods that nhibernate has is probably easier than all this.. :) I would have commented on your answer rather than updating my own but I haven't got the requisite 50 rep yet..
这篇关于LINQ过滤器结合了完全匹配项，例如SQL IN和StartsWith匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
