Сопоставление определяемых пользователем функций

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. Выражение 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 преобразует текст метода непосредственно в 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, 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, возвращающего IQueryable из типов сущностей, тем самым позволяя EF Core сопоставлять ТВФ с параметрами. Процесс аналогичен сопоставлению скалярной определяемой пользователем функции с функцией SQL: нам нужен TVF в базе данных, функция CLR, используемая в запросах LINQ, и сопоставление между ними.

В качестве примера мы будем использовать табличную функцию, которая возвращает все записи, имеющие по крайней мере один комментарий, соответствующий заданному порогу "Like".

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]