Mapování uživatelem definované funkce

EF Core umožňuje používat uživatelem definované funkce SQL v dotazech. K tomu je potřeba při konfiguraci modelu namapovat funkce na metodu CLR. Při překladu dotazu LINQ do SQL se místo funkce CLR, na které byla namapována, volá funkce definovaná uživatelem.

Přiřazení metody k funkci SQL

Abychom si ukázali, jak funguje mapování funkcí definovaných uživatelem, pojďme definovat následující entity:

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 následující konfigurace modelu:

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

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

Blog může mít mnoho příspěvků a každý příspěvek může mít mnoho komentářů.

Dále vytvořte uživatelem definovanou funkci CommentedPostCountForBlog, která vrátí počet příspěvků s alespoň jedním komentářem pro daný blog na základě 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

Abychom mohli tuto funkci použít v EF Core, definujeme následující metodu CLR, kterou namapujeme na uživatelem definovanou funkci:

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

Tělo metody CLR není důležité. Metoda nebude vyvolána na straně klienta, pokud EF Core nemůže přeložit své argumenty. Pokud se argumenty dají přeložit, EF Core se stará jenom o podpis metody.

Poznámka:

V příkladu je metoda definována na DbContext, ale může být také definována jako statická metoda uvnitř jiných tříd.

Tuto definici funkce je teď možné přidružit k uživatelem definované funkci v konfiguraci modelu:

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

Varianta přetížení s výrazem lambda HasDbFunction zabraňuje nutnosti ručně vyhledávat MethodInfo. Hodnoty default argumentů slouží pouze k identifikaci metody. Nikdy se neodesílají do databáze.

EF Core ve výchozím nastavení mapuje metodu CLR na funkci databáze se stejným názvem ve výchozím schématu. Použijte HasName a HasSchema kdy se název nebo schéma liší.

Teď spusťte následující dotaz:

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

Vygeneruje tento SQL:

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

Mapování metody na předdefinované funkce

EF Core považuje mapovanou funkci za uživatelem definovanou ve výchozím nastavení. Některé databáze rozlišují předdefinované a uživatelem definované funkce při generování SQL. Například SQL Server vyžaduje, aby uživatelem definované funkce byly kvalifikované podle schématu, ale předdefinované funkce nejsou kvalifikované schématem.

Slouží IsBuiltIn k mapování metody CLR na předdefinované funkce:

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

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

Vlastnost IsBuiltIn poskytuje stejnou konfiguraci při použití atributu:

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

Mapování funkce pomocí DbFunctionAttribute

Místo registrace funkce v OnModelCreating, statická metoda deklarovaná na objektu DbContext lze mapovat přímo použitím DbFunctionAttribute. Vlastnosti atributu Name, Schema, IsBuiltIn a IsNullable konfigurují odpovídající charakteristiky databázové funkce; jde o stejné charakteristiky, které se konfigurují metodami fluent API HasName, HasSchema, IsBuiltIn a IsNullable při použití HasDbFunction. Metody označené atributem v kontextu se automaticky rozpoznají a zaregistrují; metody označené atributem v jiných třídách je stále nutné zaregistrovat pomocí HasDbFunction. Volejte HasDbFunction pro metodu, která se registruje automaticky, pouze tehdy, když je pro další fluent konfiguraci potřeba builder, jako v níže uvedeném příkladu typu úložiště.

Například následující metoda používá DbFunctionAttribute k mapování předdefinované JSON_VALUE funkce SQL Server. Protože IsBuiltIn je true, EF Core vygeneruje název funkce bez uvedení schématu.

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

Konfigurace typů úložiště

Slouží HasStoreType ke konfiguraci návratového typu úložiště funkce a HasStoreType ke konfiguraci typu úložiště parametru. To je užitečné zejména v případě, že typ parametru CLR nemá žádné mapování nativní databáze.

V tomto příkladu JsonEntity.Metadata je slovník uložený jako nvarchar(max) prostřednictvím převaděče hodnot. Parametr funkce json má stejný typ úložiště, zatímco výsledek používá typ nvarchar(4000), který vrací 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)");

Funkci pak můžete použít s převedenou vlastností:

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

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

Převaděč hodnot je převzat z výrazu předaného jako argument funkce. Proto tento vzor funguje pro mapovanou vlastnost, například JsonEntity.Metadata, ale konfigurace typu úložiště parametrů neznamená, že lze překládat libovolné hodnoty ve slovníku. Chcete-li použít slovník v paměti, serializovat jej a předat výsledný řetězec samostatně mapované metodě, jejíž CLR parametr je string.

Mapování metody na vlastní SQL

EF Core také umožňuje, aby se metoda CLR přeložila přímo na výraz SQL, a ne na databázovou funkci. Výraz SQL se zadává pomocí HasTranslation při konfiguraci funkce.

V následujícím příkladu vytvoříme funkci, která vypočítá procentuální rozdíl mezi dvěma celými čísly.

Metoda CLR je následující:

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

Definice funkce je následující:

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

Jakmile funkci definujeme, můžete ji použít v dotazu. Místo volání databázové funkce EF Core přeloží tělo metody přímo do SQL na základě stromu výrazů SQL vytvořeného z HasTranslation. Následující dotaz LINQ:

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

Vytvoří následující SQL:

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

Caution

HasTranslation funguje se stromem výrazů SQL, nikoli s textem SQL. Překlad musí vytvořit platné SqlExpression objekty se správnými mapováními typů, nulovostí a šířením argumentu nullability. Nesprávná metadata můžou způsobit neplatné výsledky dotazu SQL nebo nesprávné výsledky dotazu a typy výrazů používané překladem můžou být specifické pro zprostředkovatele databáze. Toto rozhraní API nízké úrovně používejte až po pochopení stromu výrazů SQL poskytovatele. pokud je to možné, preferujete běžné mapování funkcí nebo existující překlad zprostředkovatele.

Konfigurace nullability uživatelem definované funkce na základě jejích argumentů

Pokud se hodnota null šíří z argumentu funkce – to znamená, že funkce vrátí null, kdykoli má tento argument hodnotu null – EF Core dokáže generovat efektivnější SQL. Nakonfigurujte jej zavoláním PropagatesNullability s příslušnými parametry. Další informace o tom, jak EF Core kompenzuje tříhodnotovou logiku SQL, najdete v tématu sémantika null dotazu.

Chcete-li to ilustrovat, definujte uživatelskou funkci ConcatStrings:

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

a dvě metody CLR, které se na ni mapují:

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

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

Konfigurace modelu (uvnitř OnModelCreating metody) je následující:

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

První funkce je nakonfigurována standardním způsobem. Druhá funkce je nakonfigurovaná tak, aby využívala optimalizaci šíření nulovatelnosti a poskytuje více informací o tom, jak se funkce chová v souvislosti s null parametry.

Při vydávání následujících dotazů:

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

Získáme tento 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)

Druhý dotaz nemusí znovu vyhodnotit samotnou funkci, aby otestovala její nulovost.

Poznámka:

Šíření nullability nakonfigurujte pouze tehdy, když funkce může vrátit null pouze proto, že jeden nebo více nakonfigurovaných parametrů jsou null.

Mapování dotazovatelné funkce na funkci s hodnotou tabulky

EF Core také podporuje mapování na funkci s hodnotou tabulky pomocí uživatelsky definované metody CLR, která vrací IQueryable typů entit, což umožňuje EF Core mapovat TVF s parametry. Tento proces se podobá mapování skalární uživatelem definované funkce na funkci SQL: potřebujeme v databázi TVF, funkci CLR, která se používá v dotazech LINQ, a mapování mezi těmito dvěma funkcemi.

Jako příklad použijeme funkci s hodnotou tabulky, která vrátí všechny příspěvky s alespoň jedním komentářem, který splňuje danou prahovou hodnotu 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
)

Podpis metody CLR je následující:

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

Návod

Volání FromExpression v těle funkce CLR umožňuje použít funkci místo běžné dbSet.

A níže je mapování:

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

Poznámka:

Dotazovatelná funkce musí být namapována na funkci s hodnotou tabulky. HasTranslation podporuje pouze skalární funkce a nelze je použít pro funkci s hodnotou tabulky.

Když je funkce namapovaná, následující dotaz:

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]