Mapowanie funkcji zdefiniowanych przez użytkownika

EF Core umożliwia korzystanie z funkcji SQL zdefiniowanych przez użytkownika w zapytaniach. W tym celu funkcje muszą być mapowane na metodę CLR podczas konfigurowania modelu. Podczas tłumaczenia zapytania LINQ na SQL wywoływana jest funkcja zdefiniowana przez użytkownika zamiast funkcji CLR, do która została zamapowana.

Mapowanie metody na funkcję SQL

Aby zilustrować sposób działania mapowania funkcji zdefiniowanego przez użytkownika, zdefiniujmy następujące jednostki:

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; }
}

Oraz następującą konfigurację modelu:

modelBuilder.Entity<Blog>()
    .HasMany(b => b.Posts)
    .WithOne(p => p.Blog);

modelBuilder.Entity<Post>()
    .HasMany(p => p.Comments)
    .WithOne(c => c.Post);

Blog może mieć wiele wpisów, a każdy wpis może mieć wiele komentarzy.

Następnie utwórz funkcję zdefiniowaną przez użytkownika , która zwraca liczbę wpisów z co najmniej jednym komentarzem dla danego CommentedPostCountForBlog bloga na podstawie bloga 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

Aby użyć tej funkcji w EF Core, definiujemy następującą metodę CLR, którą mapujemy na funkcję zdefiniowaną przez użytkownika:

public int ActivePostCountForBlog(int blogId)
    => throw new NotSupportedException();

Treść metody CLR nie jest ważna. Metoda nie zostanie wywołana po stronie klienta, chyba że EF Core nie może przetłumaczyć argumentów. Jeśli argumenty można przetłumaczyć, EF Core tylko sygnaturę metody.

Uwaga

W tym przykładzie metoda jest zdefiniowana w metodzie , ale można ją również zdefiniować jako metodę statyczną DbContext wewnątrz innych klas.

Tę definicję funkcji można teraz skojarzyć z funkcją zdefiniowaną przez użytkownika w konfiguracji modelu:

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
    .HasName("CommentedPostCountForBlog");

Domyślnie program EF Core mapować funkcję CLR na funkcję zdefiniowaną przez użytkownika o tej samej nazwie. Jeśli nazwy różnią się, możemy użyć funkcji , aby podać poprawną nazwę funkcji zdefiniowanej przez HasName użytkownika, na którą chcemy mapować.

Teraz należy wykonać następujące zapytanie:

var query1 = from b in context.Blogs
             where context.ActivePostCountForBlog(b.BlogId) > 1
             select b;

Spowoduje to SQL:

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1

Mapowanie metody na niestandardową SQL

EF Core umożliwia również korzystanie z funkcji zdefiniowanych przez użytkownika, które są konwertowane na określone SQL. Wyrażenie SQL jest dostarczane przy użyciu HasTranslation metody podczas konfiguracji funkcji zdefiniowanej przez użytkownika.

W poniższym przykładzie utworzymy funkcję, która oblicza różnicę procentową między dwiema liczbami całkowitymi.

Metoda CLR jest następująca:

public double PercentageDifference(double first, int second)
    => throw new NotSupportedException();

Definicja funkcji jest następująca:

// 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));

Po zdefiniowaniu funkcji można jej użyć w zapytaniu. Zamiast wywoływania funkcji bazy danych, EF Core przetłumaczy treść metody bezpośrednio na SQL na podstawie drzewa SQL wyrażenia zbudowanego na podstawie metody HasTranslation. Następujące zapytanie LINQ:

var query2 = from p in context.Posts
             select context.PercentageDifference(p.BlogId, 3);

Tworzy następujące SQL:

SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]

Konfigurowanie dopuszczania wartości null funkcji zdefiniowanej przez użytkownika na podstawie jej argumentów

Jeśli funkcja zdefiniowana przez użytkownika może zwrócić wartość tylko wtedy, gdy co najmniej jeden z jej argumentów to , funkcja EFCore umożliwia jej określenie, co zapewnia bardziej nullnull SQL. Można to zrobić, dodając wywołanie konfiguracji modelu odpowiednich PropagatesNullability() parametrów funkcji.

Aby to zilustrować, zdefiniuj funkcję użytkownika ConcatStrings :

CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    RETURN @prm1 + @prm2;
END

i dwie metody CLR, które są do niego mapowe:

public string ConcatStrings(string prm1, string prm2)
    => throw new InvalidOperationException();

public string ConcatStringsOptimized(string prm1, string prm2)
    => throw new InvalidOperationException();

Konfiguracja modelu (wewnątrz OnModelCreating metody) jest następująca:

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();
    });

Pierwsza funkcja jest konfigurowana w standardowy sposób. Druga funkcja jest skonfigurowana do korzystania z optymalizacji propagacji wartości null, zapewniając więcej informacji na temat zachowania funkcji w przypadku parametrów o wartości null.

Podczas wystawiania następujących zapytań:

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");

Otrzymamy tę 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)

Drugie zapytanie nie musi ponownie oceniać samej funkcji, aby przetestować jej dopuszczanie do wartości null.

Uwaga

Tej optymalizacji należy używać tylko wtedy, gdy funkcja może zwrócić tylko null wtedy, gdy jej parametry to null .

Mapowanie funkcji z zapytaniem na funkcję o wartości tabeli

EF Core obsługuje również mapowanie do funkcji o wartości tabeli przy użyciu zdefiniowanej przez użytkownika metody CLR zwracanej przez typy jednostek, co EF Core mapowanie plików IQueryable TVF za pomocą parametrów. Proces jest podobny do mapowania skalarnych funkcji zdefiniowanych przez użytkownika na funkcję SQL: potrzebujemy funkcji TVF w bazie danych, funkcji CLR, która jest używana w zapytaniach LINQ, oraz mapowania między nimi.

Na przykład użyjemy funkcji z wartością tabeli, która zwraca wszystkie wpisy z co najmniej jednym komentarzem spełniającym podany próg "Lubię to":

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
)

Sygnatura metody CLR jest następująca:

public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
    => FromExpression(() => PostsWithPopularComments(likeThreshold));

Porada

Wywołanie w treści funkcji CLR umożliwia korzystanie z funkcji zamiast FromExpression zwykłego zestawu dbset.

Poniżej przedstawiono mapowanie:

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

Przestroga

Dopóki problem 23408 nie zostanie rozwiązany, mapowanie na typy jednostek zastąpi domyślne mapowanie na tabelę dla elementu DbSet. W razie potrzeby — na przykład gdy jednostka nie jest bez kluczy — mapowanie do tabeli musi być określone jawnie przy użyciu ToTable metody .

Uwaga

Funkcja z zapytaniem musi być mapowana na funkcję o wartości tabeli i nie może używać funkcji HasTranslation .

Gdy funkcja jest mapowana, następujące zapytanie:

var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
             orderby p.Rating
             select p;

Produkuje:

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]