ユーザー定義関数のマッピング

[アーティクル]
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);

ブログには多くの投稿を含めることができ、各投稿には多数のコメントが含まれる場合があります。

次に、ユーザー定義関数 CommentedPostCountForBlog を作成します。これにより、指定したブログのコメントが少なくとも 1 つある投稿の数が、ブログの 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 ではメソッドシグネチャのみが考慮されます。

Note

このメソッドは、この例では 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 メソッドを使用して指定されます。

次の例では、2 つの整数の差の割合を計算する関数を作成します。

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 値の許容を構成する

1 つ以上の引数が null の場合にのみ、ユーザー定義関数で null を返すことができる場合は、EF Core を使用すると、それを指定することができ、よりパフォーマンスが高い SQL が実現します。これを行うには、PropagatesNullability() 呼び出しを関連する関数パラメーターモデル構成に追加します。

これを説明するために、ユーザー関数 ConcatStrings を定義します。

CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    RETURN @prm1 + @prm2;
END

それにマップする 2 つの 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();
    });

最初の関数は、標準の方法で構成されます。 2 番目の関数は、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)

2 番目のクエリでは、NULL 値の許容をテストするために関数自体を再評価する必要はありません。

Note

この最適化は、パラメーターが null の場合にのみ関数で null を返すことができる場合にのみ使用してください。

クエリ可能型関数のテーブル値関数へのマッピング

EF Core では、エンティティ型の IQueryable を返すユーザー定義 CLR メソッドを使用するテーブル値関数へのマッピングもサポートしています。これにより、EF Core で TVF をパラメーターにマップできるようになります。この手順は、スカラーユーザー定義関数を SQL 関数にマッピングするのと似ています。データベースの TVF、LINQ クエリで使用される CLR 関数、およびこの 2 つのマッピングが必要です。

例として、指定した "Like" しきい値を満たすコメントが少なくとも 1 つある投稿をすべて返すテーブル値関数を使用します。

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

Note

クエリ可能型関数はテーブル値関数にマップする必要があり、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 値の許容を構成する

クエリ可能型関数のテーブル値関数へのマッピング

その他のリソース