Felhasználó által definiált függvényleképezés

Az EF Core lehetővé teszi a felhasználó által definiált SQL-függvények használatát a lekérdezésekben. Ehhez a függvényeket CLR-metódusra kell leképezni a modellkonfiguráció során. Amikor a LINQ-lekérdezést SQL-re fordítja, a rendszer a felhasználó által definiált függvényt hívja meg a leképezett CLR-függvény helyett.

Metódus leképezése SQL-függvényhez

A felhasználó által definiált függvényleképezés működésének szemléltetéséhez definiáljuk a következő entitásokat:

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

És a következő modellkonfiguráció:

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

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

A blog számos bejegyzést tartalmazhat, és minden bejegyzéshez sok megjegyzés is tartozhat.

Ezután hozza létre a felhasználó által definiált függvényt CommentedPostCountForBlog, amely egy adott bloghoz legalább egy megjegyzéssel rendelkező bejegyzések számát adja vissza a blog Idalapján:

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

A függvény EF Core-ban való használatához a következő CLR-metódust határozzuk meg, amelyet a felhasználó által definiált függvényre képezünk le:

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

A CLR-metódus törzse nem lényeges. A metódus nem hívható meg ügyféloldalon, hacsak az EF Core nem tudja lefordítani az argumentumait. Ha az argumentumok lefordíthatók, az EF Core csak a metódus aláírásával foglalkozik.

Megjegyzés:

A példában a metódus a DbContext van definiálva, de statikus metódusként is definiálható más osztályokban.

Ez a függvénydefiníció mostantól társítható egy felhasználó által definiált függvénnyel a modellkonfigurációban:

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

A HasDbFunction lambda típusú túlterhelése szükségtelenné teszi a MethodInfo kézi kikeresését. Az default argumentumértékek csak a metódus azonosítására szolgálnak, a rendszer soha nem küldi el őket az adatbázisba.

Alapértelmezés szerint az EF Core leképezi a CLR-metódust egy adatbázisfüggvényre, amelynek neve megegyezik az alapértelmezett sémában. Használja a(z) HasName és HasSchema elemet, ha a név vagy a séma eltér.

Most hajtsa végre a következő lekérdezést:

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

A következő SQL-t fogja előállítani:

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

Metódus leképezése egy beépített függvényre

Az EF Core alapértelmezés szerint felhasználó által definiáltnak tekint egy megfeleltetett függvényt. Egyes adatbázisok megkülönböztetik a beépített és a felhasználó által definiált függvényeket az SQL létrehozásakor. A SQL Server például megköveteli, hogy a felhasználó által definiált függvények séma-minősítéssel rendelkezzenek, de a beépített függvények nem rendelkeznek séma-minősítéssel.

CLR-metódusok egy beépített függvényre való leképezésére használható IsBuiltIn :

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

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

A IsBuiltIn tulajdonság ugyanazt a konfigurációt biztosítja egy attribútum használatakor:

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

Függvény leképezése a DbFunctionAttribute használatával

Ahelyett, hogy egy függvényt regisztrálna a(z) OnModelCreating elemben, a(z) DbContext elemen deklarált statikus metódus közvetlenül hozzárendelhető a(z) DbFunctionAttribute alkalmazásával. Az attribútum Name, Schema, IsBuiltIn és IsNullable tulajdonságai konfigurálják az adatbázisfüggvény megfelelő jellemzőit; ezek megegyeznek azokkal a jellemzőkkel, amelyeket a HasSchema, IsBuiltIn, IsNullable és HasName fluent API-metódusok konfigurálnak a HasDbFunction használatakor. A környezethez rendelt metódusok felderítése és regisztrálása automatikusan megtörténik; más osztályokban lévő attribútumokkal rendelkező metódusokat továbbra is regisztrálni kell a HasDbFunction. A HasDbFunction metódust csak akkor hívja meg automatikus regisztrációhoz, ha a további fluent konfigurációhoz építőre van szükség, mint az alábbi, tárolótípusra vonatkozó példában.

Például a következő metódus a JSON_VALUE használja a SQL Server beépített DbFunctionAttribute függvényének leképezésére. Az EF Core ugyanis IsBuiltIntrueséma nélkül bocsátja ki a függvény nevét.

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

Tárolótípusok konfigurálása

Használja a HasStoreType elemet a függvény visszatérési tárolótípusának konfigurálására, a HasStoreType elemet pedig egy paraméter tárolótípusának konfigurálására. Ez különösen akkor hasznos, ha a CLR paramétertípusnak nincs natív adatbázis-leképezése.

Ebben a példában a(z) JsonEntity.Metadata egy értékkonverteren keresztül nvarchar(max)-ként van tárolva. A json függvényparaméter ugyanazzal a tárolótípussal rendelkezik, míg az eredmény a JSON_VALUE által visszaadott nvarchar(4000) típust használja:

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

A függvény ezután használható a konvertált tulajdonsággal:

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

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

Az értékkonverter a függvényargumentumként átadott kifejezésből származik. Ezért ez a minta egy olyan megfeleltetett tulajdonság esetében működik, mint például JsonEntity.Metadataa paramétertároló típusa, de a paramétertároló típusának konfigurálása nem teszi a szótár tetszőleges értékeit lefordíthatóvá. Memóriabeli szótár használatához szerializálja azt, majd adja át az eredményül kapott sztringet egy külön leképezett metódusnak, amelynek CLR-paramétere: string.

Egy metódus saját SQL-re való leképezése

Az EF Core azt is lehetővé teszi, hogy a CLR-metódusok közvetlenül egy SQL-kifejezésre legyenek lefordítva adatbázisfüggvény helyett. Az SQL-kifejezés a függvény konfigurálása során a HasTranslation használatával adható meg.

Az alábbi példában létrehozunk egy függvényt, amely kiszámítja a két egész szám közötti százalékos különbséget.

A CLR metódus a következő:

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

A függvénydefiníció a következő:

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

A függvény definiálása után használható a lekérdezésben. Az adatbázisfüggvény meghívása helyett az EF Core a metódus törzsét közvetlenül SQL-be fordítja le a HasTranslationból létrehozott SQL-kifejezésfa alapján. A következő LINQ-lekérdezés:

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

A következő SQL-t hozza létre:

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

Caution

HasTranslation Az SQL-kifejezésfával működik, nem az SQL-szöveggel. A fordításnak érvényes SqlExpression objektumokat kell létrehoznia a megfelelő típustársításokkal, a nullázhatósággal és az argumentumok nullázhatóságának terjesztésével. A helytelen metaadatok érvénytelen SQL- vagy helytelen lekérdezési eredményeket eredményezhetnek, és a fordítás által használt kifejezéstípusok egy adatbázis-szolgáltatóra vonatkozhatnak. Ezt az alacsony szintű API-t csak a szolgáltató SQL-kifejezés fájának megismerése után használja; ha lehetséges, egy normál függvényleképezést vagy egy meglévő szolgáltatói fordítást szeretne.

A felhasználó által definiált függvény null érték kezelése az argumentumai alapján

Ha a nullértékesség egy függvényargumentumból terjed tovább – vagyis a függvény null értéket ad vissza, amikor az argumentum null –, az EF Core hatékonyabb SQL-t tud generálni. Konfigurálja ezt a megfelelő paraméterek meghívásával PropagatesNullability . További információkért arról, hogy az EF Core hogyan kompenzálja az SQL háromértékű logikáját, lásd: A lekérdezések nullszemantikája.

Ennek szemléltetéséhez adja meg a következő felhasználói függvényt ConcatStrings:

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

és két CLR-metódus, amelyek hozzá kapcsolódnak:

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

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

A modellkonfiguráció (belső OnModelCreating metódus) a következő:

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

Az első függvény a szokásos módon van konfigurálva. A második függvény úgy van konfigurálva, hogy kihasználja a nullképesség-propagálás optimalizálását, és további információt nyújt arról, hogyan viselkedik a függvény a null paraméterek körül.

A következő lekérdezések kiadásakor:

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

Ezt az SQL-t kapjuk:

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)

A második lekérdezésnek nem kell újra kiértékelnie magát a függvényt, hogy tesztelje a nullképességét.

Megjegyzés:

Csak akkor konfigurálja a null érték terjedését, ha a függvény kizárólag azért adhat vissza null értéket, mert a konfigurált paraméterek közül egy vagy több null.

Lekérdezhető függvény leképezése táblaértékű függvényre

Az EF Core emellett támogatja a táblaértékű függvényekre való leképezést egy felhasználó által definiált CLR-metódussal, amely entitástípusokat IQueryable ad vissza, lehetővé téve, hogy az EF Core paraméterekkel képezhesse le a TVF-eket. A folyamat hasonló egy skaláris, felhasználó által definiált függvény SQL-függvényhez való leképezéséhez: szükségünk van egy TVF-re az adatbázisban, egy CLR-függvényre, amelyet a LINQ-lekérdezések használnak, és a kettő közötti leképezésre.

Példaként egy táblaértékű függvényt használunk, amely az összes olyan bejegyzést visszaadja, amely legalább egy megjegyzéssel rendelkezik, amely megfelel egy adott "Like" küszöbértéknek:

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
)

A CLR metódus aláírása a következő:

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

Jótanács

A FromExpression CLR-függvény törzsében lévő hívás lehetővé teszi a függvény használatát normál DbSet helyett.

Az alábbiakban a leképezés látható:

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

Megjegyzés:

A lekérdezhető függvényeket táblaértékű függvényhez kell hozzárendelni. HasTranslation csak a skaláris függvényeket támogatja, és nem használható táblaértékű függvényekhez.

A függvény megfeleltetésekor a következő lekérdezés történik:

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

Előállít:

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