Zuordnung benutzerdefinierter Funktionen

EF Core ermöglicht die Verwendung benutzerdefinierter SQL-Funktionen in Abfragen. Dazu müssen die Funktionen während der Modellkonfiguration einer CLR-Methode zugeordnet werden. Beim Übersetzen der LINQ-Abfrage in SQL wird die benutzerdefinierte Funktion anstelle der CLR-Funktion aufgerufen, der sie zugeordnet wurde.

Zuordnen einer Methode zu einer SQL-Funktion

Um zu veranschaulichen, wie die benutzerdefinierte Funktionszuordnung funktioniert, definieren wir die folgenden Entitäten:

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

Und die folgende Modellkonfiguration:

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

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

Blog kann viele Beiträge haben, und jeder Beitrag kann viele Kommentare haben.

Erstellen Sie als Nächstes die benutzerdefinierte Funktion CommentedPostCountForBlog, die die Anzahl der Beiträge mit mindestens einem Kommentar für einen bestimmten Blog basierend auf dem Blog Idzurückgibt:

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

Um diese Funktion in EF Core zu verwenden, definieren wir die folgende CLR-Methode, die wir der benutzerdefinierten Funktion zuordnen:

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

Der Körper der CLR-Methode ist nicht wichtig. Die Methode wird nicht clientseitig aufgerufen, es sei denn, EF Core kann seine Argumente nicht übersetzen. Wenn die Argumente übersetzt werden können, kümmert sich EF Core nur um die Methodensignatur.

Diese Funktionsdefinition kann nun der benutzerdefinierten Funktion in der Modellkonfiguration zugeordnet werden:

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

Standardmäßig versucht EF Core, die CLR-Funktion einer benutzerdefinierten Funktion mit demselben Namen zuzuordnen. Wenn sich die Namen unterscheiden, können wir HasName verwenden, um den richtigen Namen für die benutzerdefinierte Funktion anzugeben, der wir zuordnen möchten.

Führen Sie nun die folgende Abfrage aus:

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

Diese SQL-Datei wird erzeugt:

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

Zuordnen einer Methode zu einem benutzerdefinierten SQL

EF Core ermöglicht auch benutzerdefinierte Funktionen, die in ein bestimmtes SQL konvertiert werden. Der SQL-Ausdruck wird mithilfe der HasTranslation Methode während der benutzerdefinierten Funktionskonfiguration bereitgestellt.

Im folgenden Beispiel erstellen wir eine Funktion, die die Prozentuale Differenz zwischen zwei ganzzahligen Zahlen berechnet.

Die CLR-Methode lautet wie folgt:

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

Die Funktionsdefinition lautet wie folgt:

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

Nachdem wir die Funktion definiert haben, kann sie in der Abfrage verwendet werden. Statt die Datenbankfunktion aufzurufen, übersetzt EF Core den Methodentext direkt in SQL basierend auf der sql-Ausdrucksstruktur, die aus der HasTranslation erstellt wurde. Die folgende LINQ-Abfrage:

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

Erzeugt die folgende SQL-Datei:

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

Konfigurieren der Nullierbarkeit einer benutzerdefinierten Funktion basierend auf ihren Argumenten

Wenn die benutzerdefinierte Funktion nur null zurückgeben kann, wenn ein oder mehrere Argumente gleich null sind, bietet EFCore die Möglichkeit, dies anzugeben, was zu leistungsfähigerem SQL führt. Dies kann durch Hinzufügen eines PropagatesNullability() Aufrufs zur Konfiguration des Modells der relevanten Funktionsparameter erfolgen.

Um dies zu veranschaulichen, definieren Sie die Benutzerfunktion ConcatStrings:

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

und zwei CLR-Methoden, die ihr zugeordnet sind:

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

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

Die Modellkonfiguration (innerhalb OnModelCreating der Methode) lautet wie folgt:

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

Die erste Funktion wird standardmäßig konfiguriert. Die zweite Funktion ist so konfiguriert, dass sie die Optimierung der Übertragung der Nullbarkeit nutzt und zusätzliche Informationen bereitstellt, wie sich die Funktion im Umgang mit Null-Parametern verhält.

Beim Ausgeben der folgenden Abfragen:

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

Wir erhalten folgendes 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)

Die zweite Abfrage muss die Funktion selbst nicht erneut auswerten, um die Nullbarkeit zu testen.

Zuordnen einer abfragefähigen Funktion zu einer Tabellenwertfunktion

EF Core unterstützt auch die Zuordnung zu einer Tabellenwertfunktion mithilfe einer benutzerdefinierten CLR-Methode, die einen IQueryable Entitätstyp zurückgibt, sodass EF Core TVFs mit Parametern zuordnen kann. Der Prozess ähnelt dem Zuordnen einer skalaren benutzerdefinierten Funktion zu einer SQL-Funktion: Wir benötigen eine TVF in der Datenbank, eine CLR-Funktion, die in den LINQ-Abfragen verwendet wird, und eine Zuordnung zwischen den beiden.

Als Beispiel verwenden wir eine Tabellenwertfunktion, die alle Beiträge mit mindestens einem Kommentar zurückgibt, der einen bestimmten Schwellenwert für "Gefällt mir" erfüllt:

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
)

Die CLR-Methodensignatur lautet wie folgt:

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

Und unten sehen Sie die Zuordnung:

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

Wenn die Funktion abgebildet wird, lautet die folgende Abfrage:

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

Produziert:

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