有几种方法可以引入映射到 sql 函数或原始 sql 片段的 c# 函数。
对于直接映射到同名sql函数的函数,只需要定义方法即可;
public static ReturnType CustomMethod(ArgType arg) => throw new NotImplementedException();
protected override void OnModelCreating(ModelBuilder builder){
builder.HasDbFunction(typeof(Context).GetMethod(nameof(CustomMethod)));
}
或者使用[DbFunction]
该方法的属性。
为了生成像“@date AT TIME ZONE @name”这样的sql片段,你不能只使用一个实例SqlFunctionExpression
,还有一些工作要做。
public static DateTimeOffset ToTimeZone(this DateTimeOffset value, string name) => throw new NotImplementedException();
public class SqlFragmentListExpression : SqlExpression
{
public SqlFragmentListExpression(Type type, RelationalTypeMapping typeMapping, params SqlExpression[] fragments) : base(type, typeMapping)
{
Fragments = fragments;
}
public SqlExpression[] Fragments { get; }
public override void Print(ExpressionPrinter expressionPrinter)
{
foreach (var f in Fragments)
f.Print(expressionPrinter);
}
protected override Expression VisitChildren(ExpressionVisitor visitor)
{
var frags = new SqlExpression[Fragments.Length];
var changed = false;
for(var i = 0; i < Fragments.Length; i++)
{
frags[i] = (SqlExpression)visitor.Visit(Fragments[i]);
changed |= frags[i] != Fragments[i];
}
return changed ? new SqlFragmentListExpression(Type, TypeMapping, frags) : this;
}
}
// OnModelCreating
builder
.HasDbFunction(typeof(Extensions).GetMethod(nameof(Extensions.ToTimeZone)))
.HasTranslation(args => {
var dto = args.ElementAt(0);
return new SqlFragmentListExpression(dto.Type, dto.TypeMapping,
dto,
new SqlFragmentExpression(" AT TIME ZONE "),
args.ElementAt(1));
});
然后您可以在查询中使用该方法,例如;
_dbContext.Tickets
.Where(x => x.Date >= from && x.Date <= to)
.Select(x => new {
Date = x.Date.ToTimeZone("Central European Standard Time")
})
.GroupBy(x => new {
Year = x.Date.Year,
Month = x.Date.Month
},
(x, e) => new {
x.Year,
x.Month,
Count = e.Count()
})
应该将其翻译成sql;
SELECT DATEPART(year, [t].[Date] AT TIME ZONE N'Central European Standard Time') AS [Year],
DATEPART(month, [t].[Date] AT TIME ZONE N'Central European Standard Time') AS [Month],
COUNT(*) AS [Count]
FROM [Tickets] AS [t]
GROUP BY DATEPART(year, [t].[Date] AT TIME ZONE N'Central European Standard Time'),
DATEPART(month, [t].[Date] AT TIME ZONE N'Central European Standard Time')