使用EF Core 3.1将SQL查询挂载到EXTRACT postgresql函数

Question

我在我的ASP.NET项目上使用带有EF Core 3.1的Postgresql

我想生成这样的sql片段：

EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE f."MyDateTimeField"(；

所以，我在DbContext:的OnModelCreating方法上写了这段代码

modelBuilder
.HasDbFunction(typeof(MyDbFunctions).GetMethod(
nameof(MyDbFunctions.ExtractEpochFromTimestampWithTimezone)))
.HasTranslation(args
=> SqlFunctionExpression.Create(
"EXTRACT",
new []
{
new SqlFragmentExpression(
$"EPOCH FROM TIMESTAMP WITH TIME ZONE"),
args.First()
}, typeof(double), null));

但是生成的SQL片段是：

EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE，f."MyDateTimeField"(

注意，SQL有一个"在单词"之后；区域"；。

如果没有这个"，我如何修复我的代码以生成SQL片段"？

Answer 1

任何一个面临与上述相同问题的人。我已经用以下代码解决了上述问题

测试实体

public class Employee
{
public int Id { get; set; }
public DateTime DOB { get; set; }
public string Name { get; set; }
}

带有自定义postgres EXTRACT函数的EF Core Dbcontext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(ExtractMethod)
.HasTranslation(args =>
{
var arguments = args.ToList();
//{((Microsoft.EntityFrameworkCore.Query.SqlExpressions.ColumnExpression)arguments[1]).TypeMapping.StoreType}
arguments[0] = new SqlFragmentExpression($"{((Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression)arguments[0]).Value} from {((Microsoft.EntityFrameworkCore.Query.SqlExpressions.ColumnExpression)arguments[1]).Table.Alias}."{((Microsoft.EntityFrameworkCore.Query.SqlExpressions.ColumnExpression)arguments[1]).Name}"");
arguments.RemoveAt(1);
return new SqlFunctionExpression("EXTRACT",
arguments,
false,
new bool[] { false, false},
typeof(decimal),
RelationalTypeMapping.NullMapping);
});
}

映射的EF函数

private readonly MethodInfo ExtractMethod
= typeof(Context).GetRuntimeMethod(nameof(Extract), new[] { typeof(string), typeof(DateTime) });
public DateTime Extract(string field, DateTime source)
=> throw new NotSupportedException();

更新的代码

使用嵌套函数时，上述方法将失败。我已通过适当的实施对其进行了更新。我希望这将帮助其他

添加了一个名为ExtractExpression 的新类

public class ExtractExpression:SqlFunctionExpression
{
private readonly IReadOnlyCollection<SqlExpression> _params;        
public ExtractExpression(IReadOnlyCollection<SqlExpression> parameters):base("EXTRACT", true, typeof(double), RelationalTypeMapping.NullMapping)
{
_params = parameters;
}        
protected override Expression Accept(ExpressionVisitor visitor)
{
if (!(visitor is QuerySqlGenerator))
return base.Accept(visitor);
visitor.Visit(new SqlFragmentExpression("EXTRACT("));   //Postgres function name
visitor.Visit(_params.First());                         //First paramenter
visitor.Visit(new SqlFragmentExpression(" from "));     //query clause
visitor.Visit(_params.Skip(1).First());                 //2nd parameter
visitor.Visit(new SqlFragmentExpression(")"));          //function ending brace
return this;
}
protected override void Print([NotNullAttribute] ExpressionPrinter expressionPrinter)
{
Console.WriteLine(expressionPrinter);
}
}

在OnModeCreating功能中添加以下代码

modelBuilder.HasDbFunction(ExtractMethod)
.HasTranslation(expressions =>
{
return new ExtractExpression(expressions);
});