如何在EF Core中使用DbFunction转换? [英] How to use DbFunction translation in EF Core?
问题描述
我正在寻找在SQL Server中实现但使用MySQL的 MATCH ... AGAINST 语法的类似 EF.Functions.FreeText 的东西.
I'm looking for something like EF.Functions.FreeText that was implemented in SQL Server but using the MATCH...AGAINST syntax of MySQL.
这是我当前的工作流程:
AspNetCore 2.1.1
EntityFrameworkCore 2.1.4
Pomelo.EntityFrameworkCore.MySql 2.1.4
This is my current workflow:
AspNetCore 2.1.1
EntityFrameworkCore 2.1.4
Pomelo.EntityFrameworkCore.MySql 2.1.4
问题是MySQL使用两个函数，我不知道如何使用 DbFunction 来解释它，并为每个参数分开参数.有人知道如何实现吗?
The problem is that MySQL uses two functions and I don't know how to interpret that with DbFunction and separate the arguments for each one. Does anyone know how to implement this?
这应该是Linq语法:
This should be the Linq syntax:
query.Where(x => DbContext.FullText(new[] { x.Col1, x.Col2, x.Col3 }, "keywords"));
这应该是在SQL中生成的结果:
And this should be the result generated in SQL:
SELECT * FROM t WHERE MATCH(`Col1`, `Col2`, `Col3`) AGAINST('keywords');
我正在尝试使用 HasTranslation 函数遵循以下示例: https://github.com/aspnet/EntityFrameworkCore/issues/11295#issuecomment-511440395 https://github.com/aspnet/EntityFrameworkCore/issues/10241#issuecomment-342989770
I'm trying to follow the following examples using the HasTranslation function:
https://github.com/aspnet/EntityFrameworkCore/issues/11295#issuecomment-511440395
https://github.com/aspnet/EntityFrameworkCore/issues/10241#issuecomment-342989770
注意:我知道可以使用 FromSql 来解决，但这不是我想要的.
Note: I know it can be solved with FromSql, but it's not what I'm looking for.
推荐答案
Your use case is very similar to mine when I needed ROW_NUMBER support in EF Core.
示例:
// gets translated to
// ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY OrderId, Count)
DbContext.OrderItems.Select(o => new {
RowNumber = EF.Functions.RowNumber(o.ProductId, new {
o.OrderId,
o.Count
})
})
使用匿名类代替数组
您要做的第一件事是从使用数组切换到匿名类，即，您将调用从更改为
The first thing you have to do is to switch from using an array to an anonymous class, i.e. you change the call from
DbContext.FullText(new [] {x.Col1，x.Col2，x.Col3}，关键字")
到
DbContext.FullText(新的{x.Col1，x.Col2，x.Col3}，关键字")
参数的排序顺序将保持查询中定义的顺序，即 new {x.Col1，x.Col2} 将转换为 Col1，Col2 并将 new {x.Col2，x.Col1} 更改为 Col2，Col1 .
The sort order of the parameters will stay as it is defined by in the query,
i.e new { x.Col1, x.Col2 } will be translated to Col1, Col2
and new { x.Col2, x.Col1 } to Col2, Col1.
您甚至可以执行以下操作: new {x.Col1，_ = x.Col1，Foo ="bar"} 将会翻译为 Col1，Col1，'条码".
You can even to the following: new { x.Col1, _ = x.Col1, Foo = "bar" } that is going to be translated to Col1, Col1, 'bar'.
实施自定义 IMethodCallTranslator
Implement custom IMethodCallTranslator
If you need some hints then you can look through my code on Azure DevOps: RowNumber Support or if you can wait a few days then I will provide a blog post about the implementation of custom functions.
已更新(2019年7月31日)
博客文章:
已更新(2019年7月27日)
感谢下面的评论，我认为需要进行一些澄清.
Thanks to the comments below I see that some clarification is required.
1)如下面的评论所指出的，还有另一种方法.使用 HasDbFunction ，我可以为我节省一些输入，例如用于在EF中注册翻译程序的代码，但由于该函数具有2组参数(对于 PARTITION BY 和 ORDER BY )，而现有的 SqlFunctionExpression 不支持.(或者我错过了什么吗?)之所以选择 IMethodCallTranslator 方法，是因为我希望在设置 DbContextOptionsBuilder 时完成此功能的配置不在 OnModelCreating 中.也就是说，这是我的个人喜好.
1) As pointed out in the comment below there is another approach. With HasDbFunction I could save me some typing like the code for registration of the translator with EF but I would still need the RowNumberExpression because the function has 2 sets of parameters (for PARTITION BY and ORDER BY) and the existing SqlFunctionExpression doesn't support that. (or did I missed something?) The reason I've chosen the approach with IMethodCallTranslator is because I want the configuration of this feature to be done during setting up of the DbContextOptionsBuilder and not in OnModelCreating. That is, it’s a personal preference of mine.
最后，线程创建者可以使用 HasDbFunction 来实现所需的功能.就我而言，代码如下所示:
In the end the thread creator can use HasDbFunction to implement the desired feature as well. In my case the code would look something like the following:
// OnModelCreating
var methodInfo = typeof(DemoDbContext).GetMethod(nameof(DemoRowNumber));
modelBuilder.HasDbFunction(methodInfo)
.HasTranslation(expressions => {
var partitionBy = (Expression[])((ConstantExpression)expressions.First()).Value;
var orderBy = (Expression[])((ConstantExpression)expressions.Skip(1).First()).Value;
return new RowNumberExpression(partitionBy, orderBy);
});
// the usage with this approach is identical to my current approach
.Select(c => new {
RowNumber = DemoDbContext.DemoRowNumber(
new { c.Id },
new { c.RowVersion })
})
2):匿名类型不能强制使用其成员的类型，因此，如果使用例如 integer 调用该函数，则可能会遇到运行时异常.code>而不是 string .尽管如此，它仍然是有效的解决方案.根据客户的不同，您为解决方案工作的方法或多或少是可行的，最终的决定权在客户身上.不提供任何替代方案也是一种可能的解决方案，但不是令人满意的解决方案.特别是，如果不希望使用SQL(因为从编译器获得的支持更少)，那么运行时异常毕竟可能是一个很好的折衷方案.
2) An anonymous type can’t enforce the type(s) of its members, so you can get a runtime exception if the function is called with, say, integer instead of string. Still, it can be valid solution. Depending on the customer you are working for the solution may be more or less viable, in the end the decision lies with the customer. Not providing any alternatives is a possible solution as well but not a satisfying one.
Especially, if the usage of SQL is not desired (because you get even less support from compiler) so the runtime exception may be a good compromise after all.
但是，如果折衷方案仍然不可接受，那么我们可以研究如何增加对阵列的支持.第一种方法可能是实现自定义的 IExpressionFragmentTranslator ，以将数组的处理重定向"给我们.
But, if the compromise is still not acceptable then we can make a research on how to add support for arrays.
First approach could be the implementation of a custom IExpressionFragmentTranslator to "redirect" the handling of arrays to us.
请注意，这只是一个原型，需要更多调查/测试:-)
Please note, it is just a prototype and needs more investigation/testing :-)
// to get into EF pipeline
public class DemoArrayTranslator : IExpressionFragmentTranslator
{
public Expression Translate(Expression expression)
{
if (expression?.NodeType == ExpressionType.NewArrayInit)
{
var arrayInit = (NewArrayExpression)expression;
return new DemoArrayInitExpression(arrayInit.Type, arrayInit.Expressions);
}
return null;
}
}
// lets visitors visit the array-elements
public class DemoArrayInitExpression : Expression
{
private readonly ReadOnlyCollection<Expression> _expressions;
public override Type Type { get; }
public override ExpressionType NodeType => ExpressionType.Extension;
public DemoArrayInitExpression(Type type,
ReadOnlyCollection<Expression> expressions)
{
Type = type ?? throw new ArgumentNullException(nameof(type));
_expressions = expressions ?? throw new ArgumentNullException(nameof(expressions));
}
protected override Expression Accept(ExpressionVisitor visitor)
{
var visitedExpression = visitor.Visit(_expressions);
return NewArrayInit(Type.GetElementType(), visitedExpression);
}
}
// adds our DemoArrayTranslator to the others
public class DemoRelationalCompositeExpressionFragmentTranslator
: RelationalCompositeExpressionFragmentTranslator
{
public DemoRelationalCompositeExpressionFragmentTranslator(
RelationalCompositeExpressionFragmentTranslatorDependencies dependencies)
: base(dependencies)
{
AddTranslators(new[] { new DemoArrayTranslator() });
}
}
// Register the translator
services
.AddDbContext<DemoDbContext>(builder => builder
.ReplaceService<IExpressionFragmentTranslator,
DemoRelationalCompositeExpressionFragmentTranslator>());
为了进行测试，我引入了另一个包含 Guid [] 作为参数的重载.尽管如此，这种方法在我的用例中完全没有意义:)
For testing I introduced another overload containing Guid[] as parameter.
Although, this method doesn't make sense in my use case at all :)
public static long RowNumber(this DbFunctions _, Guid[] orderBy)
并调整了方法的使用方式
And adjusted the usage of the method
// Translates to ROW_NUMBER() OVER(ORDER BY Id)
.Select(c => new {
RowNumber = EF.Functions.RowNumber(new Guid[] { c.Id })
})
这篇关于如何在EF Core中使用DbFunction转换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
