使用者定義函數對應

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(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 的使用者定義函式。 在使用者定義函陣列態期間，會使用 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), 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 也支援使用使用者定義的 CLR 方法傳回 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));

提示

FromExpressionCLR 函式主體中的呼叫可讓函式使用，而不是一般 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]