EF Core 允许在查询中使用用户定义的 SQL 函数。 为此，需要在模型配置期间将函数映射到 CLR 方法。 将 LINQ 查询转换为 SQL 时，将调用用户定义的函数，而不是它已经映射到的 CLR 函数。
将方法映射到 SQL 函数
为了说明用户定义的函数映射的工作原理，让我们定义以下实体：
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public int? Rating { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int Rating { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
public List<Comment> Comments { get; set; }
}
public class Comment
{
public int CommentId { get; set; }
public string Text { get; set; }
public int Likes { get; set; }
public int PostId { get; set; }
public Post Post { get; set; }
}
以下是模型的配置：
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
博客可以有很多文章，每个文章可以有很多评论。
接下来，创建用户定义的函数CommentedPostCountForBlog，该函数在给定的博客中返回至少有一个评论的文章计数，基于博客Id。
CREATE FUNCTION dbo.CommentedPostCountForBlog(@id int)
RETURNS int
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM [Posts] AS [p]
WHERE ([p].[BlogId] = @id) AND ((
SELECT COUNT(*)
FROM [Comments] AS [c]
WHERE [p].[PostId] = [c].[PostId]) > 0));
END
若要在 EF Core 中使用此函数，我们定义了以下 CLR 方法，该方法映射到用户定义的函数：
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
CLR 方法的正文并不重要。 除非 EF Core 无法翻译其参数，否则该方法不会在客户端被调用。 如果可以转换参数，EF Core 只关心方法签名。
注释
在本示例中，方法定义在 DbContext 上，但也可以定义为其他类中的静态方法。
此函数定义现在可以与模型配置中的用户定义的函数相关联：
modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
.HasName("CommentedPostCountForBlog")
.HasSchema("dbo");
HasDbFunction 的 lambda 重载避免了手动查找 MethodInfo。
default参数值仅用于标识方法;它们永远不会发送到数据库。
默认情况下，EF Core 会将 CLR 方法映射到默认架构中同名的数据库函数。 当名称或架构不同时，请使用 HasName 和 HasSchema。
现在，执行以下查询：
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
将生成此 SQL：
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
将方法映射到内置函数
EF Core 将映射函数视为默认情况下用户定义的函数。 某些数据库在生成 SQL 时区分内置函数和用户定义的函数。 例如，SQL Server要求用户定义的函数具有架构限定性，但内置函数不具有架构限定。
用于 IsBuiltIn 将 CLR 方法映射到内置函数：
public static int IsDate(string value)
=> throw new NotSupportedException();
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
.HasName("ISDATE")
.IsBuiltIn();
使用属性时，该 IsBuiltIn 属性提供相同的配置：
[DbFunction(Name = "ISDATE", IsBuiltIn = true)]
使用 DbFunctionAttribute 映射函数
可以通过应用 OnModelCreating 直接映射声明在 DbContext 上的静态方法，而不是在 DbFunctionAttribute 中注册函数。 属性的 、、 和 属性用于配置数据库函数的相应特性；这些特性与使用 时通过 、、 和 fluent API 方法配置的特性相同。 上下文中的带特性的方法会被自动发现并注册；其他类中的带特性的方法仍必须通过 HasDbFunction 进行注册。 仅当需要构建器来进行额外的 Fluent 配置时，才对自动注册的方法调用 HasDbFunction，如下面的存储类型示例所示。
例如，以下方法使用DbFunctionAttribute来映射 SQL Server 中的内置JSON_VALUE函数。 因为 IsBuiltIn 是 true，所以 EF Core 会生成不带架构的函数名称。
[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
=> throw new NotSupportedException();
配置存储类型
使用 HasStoreType 配置函数的返回存储类型，使用 HasStoreType 配置参数的存储类型。 当 CLR 参数类型没有原生数据库映射时，这一点尤其有用。
在此示例中，JsonEntity.Metadata 是一个通过值转换器存储为 nvarchar(max) 的字典。
json 函数参数具有相同的存储类型，而结果使用 JSON_VALUE 返回的 nvarchar(4000) 类型：
modelBuilder.Entity<JsonEntity>()
.Property(e => e.Metadata)
.HasConversion(
value => JsonSerializer.Serialize(value, (JsonSerializerOptions)null),
value => JsonSerializer.Deserialize<Dictionary<string, string>>(value, (JsonSerializerOptions)null),
new ValueComparer<Dictionary<string, string>>(
(c1, c2) => c1.Count == c2.Count && !c1.Except(c2).Any(),
c => c.Aggregate(0, (a, kvp) => a ^ HashCode.Combine(kvp.Key, kvp.Value)),
c => c.ToDictionary(kvp => kvp.Key, kvp => kvp.Value)));
var jsonValueFunction = modelBuilder.HasDbFunction(() => JsonValue(default, default));
jsonValueFunction.HasStoreType("nvarchar(4000)");
jsonValueFunction.HasParameter("json").HasStoreType("nvarchar(max)");
然后，该函数可以与转换的属性一起使用：
var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));
SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]
值转换器取自作为函数参数传递的表达式。 因此，此模式适用于映射的属性，例如 JsonEntity.Metadata，但配置参数存储类型不会使任意字典值可转换。 若要使用内存中字典，请对其进行序列化，并将生成的字符串传递给其 CLR 参数为 string单独映射的方法。
将方法映射到自定义 SQL
EF Core 还允许将 CLR 方法直接转换为 SQL 表达式，而不是数据库函数。 在函数配置过程中，通过 HasTranslation 提供 SQL 表达式。
在下面的示例中，我们将创建一个函数，用于计算两个整数之间的百分比差异。
CLR 方法如下所示：
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
函数定义如下所示：
// 100 * ABS(first - second) / ((first + second) / 2)
modelBuilder.HasDbFunction(
typeof(BloggingContext).GetMethod(nameof(PercentageDifference), [typeof(double), typeof(int)]))
.HasTranslation(
args =>
new SqlBinaryExpression(
ExpressionType.Multiply,
new SqlConstantExpression(100, new IntTypeMapping("int", DbType.Int32)),
new SqlBinaryExpression(
ExpressionType.Divide,
new SqlFunctionExpression(
"ABS",
[
new SqlBinaryExpression(
ExpressionType.Subtract,
args.First(),
args.Skip(1).First(),
args.First().Type,
args.First().TypeMapping)
],
nullable: true,
argumentsPropagateNullability: [true, true],
type: args.First().Type,
typeMapping: args.First().TypeMapping),
new SqlBinaryExpression(
ExpressionType.Divide,
new SqlBinaryExpression(
ExpressionType.Add,
args.First(),
args.Skip(1).First(),
args.First().Type,
args.First().TypeMapping),
new SqlConstantExpression(2, new IntTypeMapping("int", DbType.Int32)),
args.First().Type,
args.First().TypeMapping),
args.First().Type,
args.First().TypeMapping),
args.First().Type,
args.First().TypeMapping));
定义函数后，可以在查询中使用。 EF Core 将基于从 HasTranslation 构造的 SQL 表达式树将方法正文直接转换为 SQL，而不是调用数据库函数。 以下 LINQ 查询：
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
生成以下 SQL：
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Caution
HasTranslation 适用于 SQL 表达式树，而不是 SQL 文本。 该转换必须构造具有正确类型映射、可空性以及参数可空性传播的有效 SqlExpression 对象。 不正确的元数据可能会生成无效的 SQL 或不正确的查询结果，翻译使用的表达式类型可能特定于数据库提供程序。 仅在了解提供程序的 SQL 表达式树后才使用此低级别 API;尽可能首选常规函数映射或现有提供程序转换。
根据用户定义函数参数配置其 null 性属性
如果可为 Null 性由函数参数传播—即每当该参数为 null 时，函数就返回 null，EF Core 可以生成更高效的 SQL。 通过调用 PropagatesNullability 相关参数来配置此配置。 有关 EF Core 如何补偿 SQL 的三值逻辑的详细信息，请参阅 查询 null 语义。
为了说明这一点，请定义用户函数 ConcatStrings：
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
和两个映射到该函数的 CLR 方法：
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
模型配置（在OnModelCreating方法内部）如下所示：
modelBuilder
.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ConcatStrings), [typeof(string), typeof(string)]))
.HasName("ConcatStrings");
modelBuilder.HasDbFunction(
typeof(BloggingContext).GetMethod(nameof(ConcatStringsOptimized), [typeof(string), typeof(string)]),
b =>
{
b.HasName("ConcatStrings");
b.HasParameter("prm1").PropagatesNullability();
b.HasParameter("prm2").PropagatesNullability();
});
第一个函数以标准方式进行配置。 第二个函数配置为利用空值传播优化，提供更详细信息说明该函数在处理空值参数时的行为。
发出以下查询时：
var query3 = context.Blogs.Where(e => context.ConcatStrings(e.Url, e.Rating.ToString()) != "https://mytravelblog.com/4");
var query4 = context.Blogs.Where(
e => context.ConcatStringsOptimized(e.Url, e.Rating.ToString()) != "https://mytravelblog.com/4");
我们得到这个 SQL 语句：
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE ([dbo].[ConcatStrings]([b].[Url], CONVERT(VARCHAR(11), [b].[Rating])) <> N'Lorem ipsum...') OR [dbo].[ConcatStrings]([b].[Url], CONVERT(VARCHAR(11), [b].[Rating])) IS NULL
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE ([dbo].[ConcatStrings]([b].[Url], CONVERT(VARCHAR(11), [b].[Rating])) <> N'Lorem ipsum...') OR ([b].[Url] IS NULL OR [b].[Rating] IS NULL)
第二个查询不需要重新评估函数本身以测试其可为 null 性。
注释
仅当函数能够返回 null 的唯一原因是一个或多个已配置的参数为 null 时，配置可为 null 性传播。
将可查询函数映射到表值函数
EF Core 还支持映射到表值函数，方法是使用返回实体类型的 IQueryable 的用户定义的 CLR 方法并允许 EF Core 映射带参数的 TVF。 此过程类似于将标量用户定义函数映射到 SQL 函数：我们需要数据库中的 TVF、LINQ 查询中使用的 CLR 函数以及两者之间的映射。
例如，我们将使用表值函数，该函数返回至少有一个满足给定“Like”阈值的注释的所有帖子：
CREATE FUNCTION dbo.PostsWithPopularComments(@likeThreshold int)
RETURNS TABLE
AS
RETURN
(
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Posts] AS [p]
WHERE (
SELECT COUNT(*)
FROM [Comments] AS [c]
WHERE ([p].[PostId] = [c].[PostId]) AND ([c].[Likes] >= @likeThreshold)) > 0
)
CLR 方法签名如下所示：
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
小窍门
在 CLR 函数体中，FromExpression 的调用允许将函数用作常规 DbSet 的替代。
下面是映射：
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));
注释
可查询函数必须映射到表值函数。
HasTranslation 仅支持标量函数，不能用于表值函数。
当函数被映射时，以下是查询：
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
生产：
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
