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ó a felhasználó által definiált függvénnyel a modellkonfigurációban:

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

Az EF Core alapértelmezés szerint egy felhasználó által definiált, azonos nevű függvényre próbálja leképezni a CLR-függvényt. Ha a nevek eltérnek, a HasName segítségével megadhatjuk a felhasználó által definiált függvényhez tartozó helyes nevet, amelyhez szeretnénk leképezni.

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

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

Az EF Core lehetővé teszi a felhasználó által definiált függvények használatát is, amelyek egy adott SQL-sé alakulnak át. Az SQL-kifejezés a felhasználó által definiált függvénykonfiguráció során metódussal HasTranslation érhető el.

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]

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

Ha a felhasználó által definiált függvény csak akkor tud visszaadni null , ha egy vagy több argumentuma van null, az EFCore lehetővé teszi ennek megadását, ami nagyobb teljesítményű SQL-t eredményez. Ezt úgy teheti meg, hogy felvesz egy PropagatesNullability() hívást a megfelelő függvényparaméterek modellkonfigurációjába.

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:

Ezt az optimalizálást csak akkor szabad használni, ha a függvény csak akkor tud null visszaadni, ha a paraméterek null adottak.

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ényre kell leképezni, és nem lehet használni HasTranslation.

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]