Mapowanie funkcji zdefiniowanych przez użytkownika

Program 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 język SQL funkcja zdefiniowana przez użytkownika jest wywoływana zamiast funkcji CLR, na która została zamapowana.

Mapowanie metody na funkcję SQL

Aby zilustrować sposób działania mapowania funkcji zdefiniowanych 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; }
}

A następująca konfiguracja 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 zawierać wiele wpisów, a każdy wpis może zawierać wiele komentarzy.

Następnie utwórz funkcję CommentedPostCountForBlogzdefiniowaną przez użytkownika , która zwraca liczbę wpisów z co najmniej jednym komentarzem dla danego bloga na podstawie blogu 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 programie EF Core, zdefiniujemy 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 program EF Core nie może przetłumaczyć swoich argumentów. Jeśli argumenty można przetłumaczyć, program EF Core dba tylko o sygnaturę metody.

Uwaga / Notatka

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

Ta definicja funkcji może być teraz skojarzona z funkcją zdefiniowaną przez użytkownika w konfiguracji modelu:

modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
    .HasName("CommentedPostCountForBlog")
    .HasSchema("dbo");

Przeciążenie lambda pozwala HasDbFunction uniknąć ręcznego wyszukiwania elementu MethodInfo. Wartości default argumentów są używane tylko do identyfikowania metody; nigdy nie są wysyłane do bazy danych.

Domyślnie program EF Core mapuje metodę CLR na funkcję bazy danych o tej samej nazwie w domyślnym schemacie. Użyj HasName i HasSchema gdy nazwa lub schemat się różnią.

Teraz wykonaj następujące zapytanie:

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

Spowoduje to wygenerowanie tego kodu SQL:

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

Przypisanie metody do funkcji wbudowanej

EF Core domyślnie uznaje zamapowaną funkcję za funkcję zdefiniowaną przez użytkownika. Niektóre bazy danych rozróżniają wbudowane i zdefiniowane przez użytkownika funkcje podczas generowania kodu SQL. Na przykład SQL Server wymaga, aby funkcje zdefiniowane przez użytkownika zostały kwalifikowane pod kątem schematu, ale wbudowane funkcje nie są kwalifikowane schematem.

Użyj IsBuiltIn, aby zmapować metodę CLR na funkcję wbudowaną:

public static int IsDate(string value)
    => throw new NotSupportedException();

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
    .HasName("ISDATE")
    .IsBuiltIn();

Właściwość IsBuiltIn zapewnia tę samą konfigurację podczas używania atrybutu:

[DbFunction(Name = "ISDATE", IsBuiltIn = true)]

Mapowanie funkcji przy użyciu elementu DbFunctionAttribute

Zamiast rejestrować funkcję w OnModelCreating, metodę statyczną zadeklarowaną w klasie DbContext można bezpośrednio zamapować, stosując DbFunctionAttribute. Właściwości Name, Schema, IsBuiltIn i IsNullable atrybutu konfigurują odpowiadające im cechy funkcji bazy danych; są to te same cechy, które są konfigurowane przez metody interfejsu Fluent API HasName, IsBuiltIn, HasSchema i IsNullable podczas korzystania z HasDbFunction. Metody oznaczone atrybutami w obrębie kontekstu są wykrywane i rejestrowane automatycznie; metody oznaczone atrybutami w innych klasach nadal muszą zostać zarejestrowane za pomocą HasDbFunction. Wywołaj HasDbFunction metodę automatycznie zarejestrowaną tylko wtedy, gdy konstruktor jest potrzebny do dodatkowej płynnej konfiguracji, jak w poniższym przykładzie typu sklepu.

Na przykład poniższa metoda używa metody DbFunctionAttribute do mapowania wbudowanej funkcji JSON_VALUE programu SQL Server. Ponieważ IsBuiltIn jest true, EF Core generuje nazwę funkcji bez schematu.

[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
    => throw new NotSupportedException();

Konfigurowanie typów magazynów

Użyj HasStoreType, aby skonfigurować typ magazynowania wartości zwracanej funkcji, a HasStoreType, aby skonfigurować typ magazynowania parametru. Jest to szczególnie przydatne, gdy typ parametru CLR nie ma natywnego mapowania bazy danych.

W tym przykładzie JsonEntity.Metadata jest słownik przechowywany jako nvarchar(max) za pomocą konwertera wartości. Parametr funkcji json ma ten sam typ przechowywania, a wynik używa typu nvarchar(4000) zwróconego przez 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)");

Następnie można użyć funkcji z przekonwertowaną właściwością:

var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));

SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]

Konwerter wartości jest pobierany z wyrażenia przekazanego jako argument funkcji. W związku z tym ten wzorzec działa dla właściwości mapowanej, takiej jak JsonEntity.Metadata, ale skonfigurowanie typu magazynu parametrów nie powoduje translatowania dowolnych wartości słownika. Aby użyć słownika w pamięci, serializuj go i przekaż wynikowy ciąg do oddzielnej metody mapowanej, której parametr CLR to string.

Mapowanie metody na niestandardowy SQL

Program EF Core umożliwia również tłumaczenie metody CLR bezpośrednio na wyrażenie SQL, a nie na funkcję bazy danych. Wyrażenie SQL jest udostępniane przy użyciu HasTranslation podczas konfiguracji funkcji.

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), [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));

Po zdefiniowaniu funkcji można jej użyć w zapytaniu. Zamiast wywoływać funkcję bazy danych, program EF Core przetłumacze treść metody bezpośrednio na język SQL na podstawie drzewa wyrażeń SQL skonstruowanego z funkcji HasTranslation. Następujące zapytanie LINQ:

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

Tworzy następujący kod SQL:

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

Caution

HasTranslation współpracuje z drzewem wyrażeń SQL, a nie tekstem SQL. Tłumaczenie musi konstruować prawidłowe SqlExpression obiekty z prawidłowymi mapowaniami typów, wartościami null i propagacją wartości null argumentu. Nieprawidłowe metadane mogą powodować generowanie nieprawidłowego kodu SQL lub zwracanie nieprawidłowych wyników zapytania, a typy wyrażeń wykorzystywane przez tłumaczenie mogą być specyficzne dla dostawcy bazy danych. Użyj tego interfejsu API niskiego poziomu tylko po zrozumieniu drzewa wyrażeń SQL dostawcy; preferuj regularne mapowanie funkcji lub istniejące tłumaczenie dostawcy, jeśli jest to możliwe.

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

Jeśli możliwość przyjmowania wartości null jest propagowana z argumentu funkcji — to znaczy, że funkcja zwraca null zawsze, gdy ten argument ma wartość null — EF Core może wygenerować bardziej wydajne zapytanie SQL. Skonfiguruj to, wywołując PropagatesNullability z odpowiednimi parametrami. Aby uzyskać więcej informacji o tym, jak EF Core kompensuje trójwartościową logikę języka SQL, zobacz Semantyka wartości null w zapytaniach.

Aby to zilustrować, zdefiniuj funkcję ConcatStringsużytkownika :

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

i dwie metody CLR mapujące do niej:

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

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

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

Otrzymujemy następujący język 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 ocenić samej funkcji, aby przetestować jej wartość null.

Uwaga / Notatka

Skonfiguruj propagację wartości null tylko wtedy, gdy funkcja może zwrócić null wyłącznie dlatego, że co najmniej jeden ze skonfigurowanych parametrów ma wartość null.

Mapowanie funkcji z możliwością wykonywania zapytań do funkcji wartości tabeli

Program EF Core obsługuje również mapowanie funkcji zwracającej wartości tabeli przy użyciu zdefiniowanej przez użytkownika metody CLR, zwracającej IQueryable typy jednostek, co umożliwia programowi EF Core mapowanie funkcji TVF z parametrami. Proces jest podobny do mapowania funkcji zdefiniowanej przez użytkownika skalarnej na funkcję SQL: potrzebujemy funkcji TVF w bazie danych, funkcji CLR używanej w zapytaniach LINQ i mapowania między nimi.

Na przykład użyjemy funkcji tabelarycznej, która zwraca wszystkie wpisy mające co najmniej jeden komentarz spełniający określony próg "polubień".

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

Wskazówka

Wywołanie FromExpression w treści funkcji CLR umożliwia użycie funkcji zamiast zwykłego zestawu dbSet.

Poniżej znajduje się mapowanie:

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

Uwaga / Notatka

Funkcja z możliwością wykonywania zapytań musi zostać zamapowana na funkcję wartości tabeli. HasTranslation obsługuje tylko funkcje skalarne i nie można ich używać w przypadku funkcji wartości tabeli.

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]