EF Core 无法通过 IQueryable.Where 动态实现 OR 转换

Question

using (var ctx = new StockContext())
{
var syms = new string[] { "foo", "bar" };
return ctx.Assets.Select(x => x.Symbol)
.Where(symbol => syms.Any(sym => symbol.Contains(sym)))
.ToArray();  // EXCEPTION: InvalidOperationException: The LINQ expression 'DbSet<Asset>() .Where(a => __syms_0 .Any(sym => a.Symbol.Contains(sym)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
}

using (var ctx = new StockContext())
{
var syms = new string[] { "foo", "bar" };
return ctx.Assets.Select(x => x.Symbol)
.Where(symbol => s.Contains(syms[0]))
.ToArray();  // WORKS FINE
}

请参阅上面的代码。我认为这是一种很常见的做法，但我什至无法使第一个示例起作用。

我想要实现的是第一个示例中的代码可以转换为：SELECT Symbol FROM Assets WHERE Symbol LIKE '%foo%' OR Symbol LIKE '%bar%'.为什么它会异常运行？

附言我知道PredicateBuilder可以解决这个问题。但是，我可以通过本机 EF 代码实现如此简单的操作吗？提前感谢！

<小时 />

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.0-preview.4.20220.10" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.0-preview.4.20220.10" />

Answer 1

最终，编写表达式树解析器是很困难的，有时您需要手动展开它才能帮助它。考虑：

var syms = new string[] { "foo", "bar" };
var p = Expression.Parameter(typeof(Asset), "x");
var symbol = Expression.PropertyOrField(p, nameof(Asset.Symbol));
Expression body = null;
var contains = typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) });
foreach (var sym in syms)
{
var test = Expression.Call(symbol, contains, Expression.Constant(sym, typeof(string)));
if (body == null) // first
{
body = test;
}
else
{
body = Expression.OrElse(body, test);
}
}
var lambda = Expression.Lambda<Func<Asset, bool>>(body, p);
var results = ctx.Assets.Where(lambda).Select(x => x.Symbol).ToArray();

这样做是根据输入手动构建表达式树;它相当于：

x => (x.Symbol.Contains("foo") || x.Symbol.Contains("bar"))

在这种情况下，但应扩展到任意数量的输入。

Answer 2

请测试一下...

var syms = new string[] { "foo", "bar" };
return ctx.Assets.Select(x => x.Symbol)
.Where(symbol => syms.Any(x => EF.Functions.Like(symbol, x)))
.ToArray(); 

var syms = new string[] { "foo", "bar" };
var select = ctx.Assets.Select(x => x.Symbol);
foreach (var item in syms)
select = select.Where(x => EF.Functions.Like(x, item));
var result = select.ToArray();