사용자 정의 함수 매핑

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는 메서드 서명만 중요합니다.

이제 이 함수 정의를 모델 구성의 사용자 정의 함수와 연결할 수 있습니다.

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), [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), [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]

인수에 따라 사용자 정의 함수의 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), [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();
    });

첫 번째 함수는 표준 방식으로 구성됩니다. 두 번째 함수는 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 허용 가능성을 테스트하기 위해 함수 자체를 다시 평가할 필요가 없습니다.

쿼리 가능한 함수를 테이블 반환 함수에 매핑

또한 EF Core는 엔터티 형식을 반환하는 사용자 정의 CLR 메서드를 사용하여 테이블 값 함수(TVF)에 매핑을 IQueryable 지원하므로, 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));

다음은 매핑입니다:

modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));

함수가 매핑되면 다음 쿼리를 수행합니다.

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]