사용자 정의 함수 매핑
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로 변환되는 사용자 정의 함수도 사용할 수 있습니다. 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]
.NET