用户定义的函数映射

项目
03/12/2023

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);

博客可以有多篇帖文，每篇帖文可以有多条评论。

接下来，根据博客 Id，创建用户定义的函数 CommentedPostCountForBlog，该函数将返回针对给定博客至少具有一条评论的帖文计数：

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(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
    .HasName("CommentedPostCountForBlog");

默认情况下，EF Core 尝试将 CLR 函数映射到具有相同名称的用户定义的函数。如果名称不同，可以使用 HasName 为要映射到的用户定义的函数提供正确的名称。

接下来，执行下面的查询：

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

将方法映射到自定义 SQL

EF Core 还允许将用户定义的函数转换为特定 SQL。 SQL 表达式是在配置用户定义的函数过程中使用 HasTranslation 方法提供的。

在下面的示例中，我们将创建一个函数，用于计算两个整数之间的百分比差异。

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), new[] { typeof(double), typeof(int) }))
    .HasTranslation(
        args =>
            new SqlBinaryExpression(
                ExpressionType.Multiply,
                new SqlConstantExpression(
                    Expression.Constant(100),
                    new IntTypeMapping("int", DbType.Int32)),
                new SqlBinaryExpression(
                    ExpressionType.Divide,
                    new SqlFunctionExpression(
                        "ABS",
                        new SqlExpression[]
                        {
                            new SqlBinaryExpression(
                                ExpressionType.Subtract,
                                args.First(),
                                args.Skip(1).First(),
                                args.First().Type,
                                args.First().TypeMapping)
                        },
                        nullable: true,
                        argumentsPropagateNullability: new[] { 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(
                            Expression.Constant(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]

基于用户定义函数的参数配置该函数的为 null 性

如果用户定义的函数只能在其一个或多个参数为 null 时返回 null，则 EFCore 会提供指定该参数的方法，从而提高 SQL 的性能。可通过向相关函数参数模型配置添加 PropagatesNullability() 调用来完成此操作。

为了说明这一点，请定义用户函数 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), new[] { typeof(string), typeof(string) }))
    .HasName("ConcatStrings");

modelBuilder.HasDbFunction(
    typeof(BloggingContext).GetMethod(nameof(ConcatStringsOptimized), new[] { typeof(string), typeof(string) }),
    b =>
    {
        b.HasName("ConcatStrings");
        b.HasParameter("prm1").PropagatesNullability();
        b.HasParameter("prm2").PropagatesNullability();
    });

第一个函数是以标准方式配置的。第二个函数配置为利用为 Null 性传播优化，并提供详细信息介绍函数如何围绕 null 参数做出行为。

发出以下查询时：

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 时，才应使用此优化。

将可查询函数映射到表值函数

EF Core 还支持映射到表值函数，方法是使用返回实体类型的 IQueryable 的用户定义的 CLR 方法并允许 EF Core 映射带参数的 TVF。此过程类似于将标量用户定义的函数映射到 SQL 函数：我们需要数据库中的 TVF、在 LINQ 查询中使用的 CLR 函数，以及两者之间的映射。

例如，我们将使用表值函数返回至少具有一个符合给定“赞”阈值的评论的所有帖文：

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), new[] { 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]

通过

用户定义的函数映射

将方法映射到 SQL 函数

将方法映射到自定义 SQL

基于用户定义函数的参数配置该函数的为 null 性

将可查询函数映射到表值函数

其他资源