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(() => ActivePostCountForBlog(default))
.HasName("CommentedPostCountForBlog")
.HasSchema("dbo");
HasDbFunction의 람다 오버로드를 사용하면 MethodInfo을(를) 수동으로 조회할 필요가 없습니다. 인수 값은 default 메서드를 식별하는 데만 사용되며 데이터베이스로 전송되지 않습니다.
기본적으로 EF Core는 CLR 메서드를 기본 스키마에서 이름이 같은 데이터베이스 함수에 매핑합니다. 이름이나 스키마가 다를 경우 HasSchema 및 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
기본 제공 함수에 메서드 매핑
EF Core는 매핑된 함수를 기본적으로 사용자 정의로 간주합니다. 일부 데이터베이스는 SQL을 생성할 때 기본 제공 함수와 사용자 정의 함수를 구분합니다. 예를 들어 SQL Server 사용자 정의 함수를 스키마로 한정해야 하지만 기본 제공 함수는 스키마로 한정되지 않습니다.
CLR 메서드를 기본 제공 함수에 매핑하는 데 사용합니다 IsBuiltIn .
public static int IsDate(string value)
=> throw new NotSupportedException();
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
.HasName("ISDATE")
.IsBuiltIn();
이 속성은 IsBuiltIn 특성을 사용할 때 동일한 구성을 제공합니다.
[DbFunction(Name = "ISDATE", IsBuiltIn = true)]
DbFunctionAttribute를 사용하여 함수 매핑
함수 OnModelCreating를 등록하는 대신, 함수에 선언된 정적 메서드를 DbContext 적용 DbFunctionAttribute하여 직접 매핑할 수 있습니다. 특성의 IsBuiltIn, IsNullable, Name, 및 Schema 속성은 데이터베이스 함수의 해당 특성을 설정합니다. 이러한 특성은 HasName를 사용할 때 HasDbFunction, HasSchema, IsNullable, 및 IsBuiltIn fluent API 메서드로 설정되는 특성과 동일합니다. 컨텍스트에서 특성이 지정된 메서드가 자동으로 검색되고 등록됩니다. 다른 클래스의 특성이 지정된 메서드는 여전히 .에 등록 HasDbFunction되어야 합니다. 아래의 저장소 유형 예제와 같이 추가 흐름 구성을 위해 작성기를 필요로 하는 경우에만 자동으로 등록된 메서드를 호출 HasDbFunction 합니다.
예를 들어, 다음 메서드는 SQL Server의 기본 제공 DbFunctionAttribute 함수를 매핑하는 데 JSON_VALUE를 사용합니다.
IsBuiltIn이(가) true이므로 EF Core는 스키마를 붙이지 않고 함수 이름을 생성합니다.
[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
=> throw new NotSupportedException();
저장소 유형 구성
함수의 반환 저장소 형식을 구성하고 HasStoreType 매개 변수의 저장소 형식을 구성하는 데 사용합니다HasStoreType. CLR 매개 변수 형식에 네이티브 데이터베이스 매핑이 없는 경우에 특히 유용합니다.
이 예제에서는 JsonEntity.Metadata 값 변환기를 통해 저장된 nvarchar(max) 사전입니다.
json 함수 매개변수는 동일한 저장소 유형을 가지며, 결과는 nvarchar(4000)에서 반환된 JSON_VALUE 유형을 사용합니다:
modelBuilder.Entity<JsonEntity>()
.Property(e => e.Metadata)
.HasConversion(
value => JsonSerializer.Serialize(value, (JsonSerializerOptions)null),
value => JsonSerializer.Deserialize<Dictionary<string, string>>(value, (JsonSerializerOptions)null),
new ValueComparer<Dictionary<string, string>>(
(c1, c2) => c1.Count == c2.Count && !c1.Except(c2).Any(),
c => c.Aggregate(0, (a, kvp) => a ^ HashCode.Combine(kvp.Key, kvp.Value)),
c => c.ToDictionary(kvp => kvp.Key, kvp => kvp.Value)));
var jsonValueFunction = modelBuilder.HasDbFunction(() => JsonValue(default, default));
jsonValueFunction.HasStoreType("nvarchar(4000)");
jsonValueFunction.HasParameter("json").HasStoreType("nvarchar(max)");
그런 다음 변환된 속성과 함께 함수를 사용할 수 있습니다.
var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));
SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]
값 변환기는 함수 인수로 전달된 식에서 가져옵니다. 따라서 이 패턴은 매핑된 속성에 JsonEntity.Metadata대해 작동하지만 매개 변수 저장소 형식을 구성해도 임의의 사전 값을 변환할 수 없습니다. 메모리 내 사전을 사용하려면 해당 사전을 serialize하고 결과 문자열을 CLR 매개 변수가 있는 별도로 매핑된 메서드에 전달합니다 string.
메서드를 사용자 지정 SQL에 매핑
또한 EF Core를 사용하면 CLR 메서드를 데이터베이스 함수가 아닌 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), [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]
Caution
HasTranslation 는 SQL 텍스트가 아닌 SQL 식 트리와 함께 작동합니다. 변환은 올바른 형식 매핑, null 허용 여부 및 인수 null 허용 여부 전파를 사용하여 유효한 SqlExpression 개체를 생성해야 합니다. 잘못된 메타데이터는 잘못된 SQL 또는 잘못된 쿼리 결과를 생성할 수 있으며, 변환에 사용되는 식 형식은 데이터베이스 공급자와 관련이 있을 수 있습니다. 공급자의 SQL 식 트리를 이해한 후에만 이 하위 수준 API를 사용합니다. 는 가능한 경우 일반 함수 매핑 또는 기존 공급자 변환을 선호합니다.
인수에 따라 사용자 정의 함수의 null 허용 여부 구성
null 허용 여부가 함수 인수에서 전파되는 경우, 즉 해당 인수가 null일 때마다 함수가 null를 반환하는 경우 EF Core는 더 효율적인 SQL을 생성할 수 있습니다. 관련 매개 변수를 호출 PropagatesNullability 하여 구성합니다. EF Core가 SQL의 세 가지 값 논리를 보정하는 방법에 대한 자세한 내용은 쿼리 null 의미 체계를 참조하세요.
이를 설명하기 위해 사용자 함수 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 허용 가능성을 테스트하기 위해 함수 자체를 다시 평가할 필요가 없습니다.
메모
함수가 null를 반환할 수 있는 유일한 이유가 구성된 매개변수 중 하나 이상이 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));
팁 (조언)
FromExpression CLR 함수 본문의 호출을 통해 일반 DbSet 대신 함수를 사용할 수 있습니다.
다음은 매핑입니다:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [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]
GitHub에서 Microsoft와 공동 작업
이 콘텐츠의 원본은 GitHub에서 찾을 수 있으며, 여기서 문제와 끌어오기 요청을 만들고 검토할 수도 있습니다. 자세한 내용은 참여자 가이드를 참조하세요.
.NET