Сопоставление определяемых пользователем функций
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 будет преобразовывать тело метода непосредственно в код SQL, исходя из дерева выражений SQL, построенного на основе HasTranslation. Следующий запрос 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, EF Core позволяет указать это, что дает возможность повысить производительность 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 типов сущностей, что позволяет EF Core сопоставлять функции с табличным значением (TVF) с параметрами. Этот процесс аналогичен сопоставлению скалярной пользовательской функции с функцией SQL: нам нужна функция с табличным значением в базе данных, функция CLR, используемая в запросах LINQ, и сопоставление между ними.
В качестве примера мы будем использовать функцию с табличным значением, которая возвращает все записи блога, имеющие хотя бы один комментарий, количество отметок "Нравится" которого соответствует заданному пороговому значению:
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), 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]
