Zuordnung benutzerdefinierter FunktionenUser-defined function mapping

EF Core ermöglicht die Verwendung benutzerdefinierter SQL-Funktionen in Abfragen.EF Core allows for using user-defined SQL functions in queries. Dazu müssen die Funktionen während der Modellkonfiguration einer CLR-Methode zugeordnet werden.To do that, the functions need to be mapped to a CLR method during model configuration. Beim Übersetzen der LINQ-Abfrage in SQL wird die benutzerdefinierte Funktion anstelle der CLR-Funktion aufgerufen, der sie zugeordnet wurde.When translating the LINQ query to SQL, the user-defined function is called instead of the CLR function it has been mapped to.

Zuordnen einer Methode zu einer SQL-FunktionMapping a method to a SQL function

Damit veranschaulicht werden kann, wie das Zuordnen benutzerdefinierter Funktionen funktioniert, werden hier die folgenden Entitäten definiert:To illustrate how user-defined function mapping work, let's define the following entities:

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

Außerdem wird als Beispiel die folgende Modellkonfiguration verwendet:And the following model configuration:

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

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

In Blogs finden sich viele Beiträge. Jeder Beitrag wiederum kann über viele Kommentare verfügen.Blog can have many posts and each post can have many comments.

Erstellen Sie als Nächstes die benutzerdefinierte Funktion CommentedPostCountForBlog. Diese gibt die Anzahl der Beiträge zurück, für die pro Blog – Blog Id in diesem Beispiel – mindestens ein Kommentar vorhanden ist:Next, create the user-defined function CommentedPostCountForBlog, which returns the count of posts with at least one comment for a given blog, based on the blog 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

Zur Verwendung dieser Funktion in EF Core wird die folgende CLR-Methode definiert, die der benutzerdefinierten Funktion zugeordnet wird:To use this function in EF Core, we define the following CLR method, which we map to the user-defined function:

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

Der Methodenkörper der CLR-Methode spielt keine Rolle.The body of the CLR method is not important. Die Methode wird nur clientseitig aufgerufen, wenn EF Core die dazugehörigen Argumente nicht übersetzen kann.The method will not be invoked client-side, unless EF Core can't translate its arguments. Wenn die Argumente übersetzt werden können, spielt nur die Methodensignatur eine Rolle für EF Core.If the arguments can be translated, EF Core only cares about the method signature.

Hinweis

Im Beispiel wird die Methode für DbContext definiert. Die Definition als statische Methode innerhalb anderer Klassen ist aber ebenfalls möglich.In the example, the method is defined on DbContext, but it can also be defined as a static method inside other classes.

Diese Funktionsdefinition kann nun der benutzerdefinierten Funktion in der Modellkonfiguration zugeordnet werden:This function definition can now be associated with user-defined function in the model configuration:

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

Standardmäßig versucht EF Core, CLR-Funktionen einer benutzerdefinierten Funktion mit demselben Name zuzuordnen.By default, EF Core tries to map CLR function to a user-defined function with the same name. Wenn sich der Name unterscheidet, kann HasName verwendet werden, um den richtigen Namen für die benutzerdefinierte Funktion anzugeben, zu der die Zuordnung erfolgen soll.If the names differ, we can use HasName to provide the correct name for the user-defined function we want to map to.

Als Nächstes wird nun die folgende Abfrage ausgeführt:Now, executing the following query:

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

Dies führt zu folgendem SQL-Code:Will produce this SQL:

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

Zuordnen einer Methode zu einem benutzerdefinierten SQL-AusdruckMapping a method to a custom SQL

EF Core ermöglicht es auch, benutzerdefinierte Funktionen in einen bestimmten SQL-Ausdruck zu konvertieren.EF Core also allows for user-defined functions that get converted to a specific SQL. Der SQL-Ausdruck wird mithilfe der HasTranslation-Methode während der Konfiguration der benutzerdefinierten Funktion angegeben.The SQL expression is provided using HasTranslation method during user-defined function configuration.

Im Beispiel unten wird eine Funktion erstellt, die den prozentualen Unterschied zwischen zwei Integern berechnet.In the example below, we'll create a function that computes percentage difference between two integers.

Die CLR-Methode lautet wie folgt:The CLR method is as follows:

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

Die Funktionsdefinition lautet wie folgt:The function definition is as follows:

// 100 * ABS(first - second) / ((first + second) / 2)
modelBuilder.HasDbFunction(
        typeof(BloggingContext).GetMethod(nameof(PercentageDifference), new[] { typeof(double), typeof(int) }))
    .HasTranslation(
        args =>
            new SqlBinaryExpression(
                ExpressionType.Multiply,
                new SqlConstantExpression(
                    Expression.Constant(100),
                    new IntTypeMapping("int", DbType.Int32)),
                new SqlBinaryExpression(
                    ExpressionType.Divide,
                    new SqlFunctionExpression(
                        "ABS",
                        new SqlExpression[]
                        {
                            new SqlBinaryExpression(
                                ExpressionType.Subtract,
                                args.First(),
                                args.Skip(1).First(),
                                args.First().Type,
                                args.First().TypeMapping)
                        },
                        nullable: true,
                        argumentsPropagateNullability: new[] { 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(
                            Expression.Constant(2),
                            new IntTypeMapping("int", DbType.Int32)),
                        args.First().Type,
                        args.First().TypeMapping),
                    args.First().Type,
                    args.First().TypeMapping),
                args.First().Type,
                args.First().TypeMapping));

Sobald die Funktion definiert wurde, kann sie in der Abfrage verwendet werden.Once we define the function, it can be used in the query. Anstatt die Datenbankfunktion aufzurufen, übersetzt EF Core den Methodenkörper direkt in die SQL-basierte SQL-Ausdrucksbaumstruktur, die auf Grundlage von HasTranslation erstellt wird.Instead of calling database function, EF Core will translate the method body directly into SQL based on the SQL expression tree constructed from the HasTranslation. Betrachten Sie die folgende LINQ-Abfrage:The following LINQ query:

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

Dies generiert diese SQL-Anweisung:Produces the following SQL:

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

Konfigurieren der NULL-Zulässigkeit von benutzerdefinierten Funktionen auf Basis von ArgumentenConfiguring nullability of user-defined function based on its arguments

Wenn die benutzerdefinierte Funktion nur null zurückgeben kann, wenn mindestens ein Argument null ist, kann dies in EF Core angegeben werden. Dies führt zu einem leistungsfähigeren SQL-Code.If the user-defined function can only return null when one or more of its arguments are null, EFCore provides way to specify that, resulting in more performant SQL. Dazu muss der Modellkonfiguration des relevanten Funktionsparameters ein PropagatesNullability()-Aufruf hinzugefügt werden.It can be done by adding a PropagatesNullability() call to the relevant function parameters model configuration.

Zur Veranschaulichung wird die Benutzerfunktion ConcatStrings definiert:To illustrate this, define user function ConcatStrings:

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

Außerdem werden zwei CLR-Methoden definiert, die ihr zugeordnet sind:and two CLR methods that map to it:

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

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

Die Modellkonfiguration (innerhalb der Methode OnModelCreating) lautet wie folgt:The model configuration (inside OnModelCreating method) is as follows:

modelBuilder
    .HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ConcatStrings), new[] { typeof(string), typeof(string) }))
    .HasName("ConcatStrings");

modelBuilder.HasDbFunction(
    typeof(BloggingContext).GetMethod(nameof(ConcatStringsOptimized), new[] { typeof(string), typeof(string) }),
    b =>
    {
        b.HasName("ConcatStrings");
        b.HasParameter("prm1").PropagatesNullability();
        b.HasParameter("prm2").PropagatesNullability();
    });

Die erste Funktion wird standardmäßig konfiguriert.The first function is configured in the standard way. Die zweite Funktion ist so konfiguriert, dass sie die Propagierungsoptimierung der NULL-Zulässigkeit nutzt und weitere Informationen zur Funktionsweise der Funktion im Kontext von NULL-Parametern bietet.The second function is configured to take advantage of the nullability propagation optimization, providing more information on how the function behaves around null parameters.

Die folgenden Abfragen:When issuing the following queries:

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

Ergeben den folgenden SQL-Code:We get this 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 nicht selbst neu auswerten, um deren NULL-Zulässigkeit zu testen.The second query doesn't need to re-evaluate the function itself to test its nullability.

Hinweis

Diese Optimierung sollte nur eingesetzt werden, wenn die Funktion nur null zurückgeben kann, wenn ihre Parameter null sind.This optimization should only be used if the function can only return null when it's parameters are null.

Zuordnen einer abfragbaren Funktion zu einer TabellenwertfunktionMapping a queryable function to a table-valued function

EF Core unterstützt auch das Zuordnen zu einer Tabellenwertfunktion mithilfe einer benutzerdefinierten CLR-Methode. Dabei wird eine IQueryable-Klasse der Entitätstypen zurückgegeben, was es EF Core ermöglicht, Tabellenwertfunktionen Parameter zuzuordnen.EF Core also supports mapping to a table-valued function using a user-defined CLR method returning an IQueryable of entity types, allowing EF Core to map TVFs with parameters. Der Prozess ähnelt dem Zuordnen einer benutzerdefinierten Skalarfunktion zu einer SQL-Funktion: Es wird eine Tabellenwertfunktion in der Datenbank benötigt, eine CLR-Funktion, die in den LINQ-Abfragen verwendet wird, sowie eine Zuordnung zwischen diesen beiden.The process is similar to mapping a scalar user-defined function to a SQL function: we need a TVF in the database, a CLR function that is used in the LINQ queries, and a mapping between the two.

Als Beispiel soll eine Tabellenwertfunktion verwendet werden, die alle Beiträge zurückgibt, die über mindestens einen Kommentar verfügen, der einen gesetzten Schwellenwert an „Gefällt mir“-Markierungen erreicht:As an example, we'll use a table-valued function that returns all posts having at least one comment that meets a given "Like" threshold:

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 Signatur der CLR-Methode sieht folgendermaßen aus:The CLR method signature is as follows:

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

Tipp

Der FromExpression-Aufruf im CLR-Funktionskörper ermöglicht es, dass die Funktion anstelle der regulären DbSet-Klasse verwendet wird.The FromExpression call in the CLR function body allows for the function to be used instead of a regular DbSet.

Unten sehen Sie die Zuordnung:And below is the mapping:

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

Achtung

Solange das Issue 23408 nicht behoben ist, wird bei Zuordnungen zu einer IQueryable-Schnittstelle von Entitätstypen die Standardzuordnung zu einer Tabelle für die DbSet-Klasse überschrieben.Until issue 23408 is fixed, mapping to an IQueryable of entity types overrides the default mapping to a table for the DbSet. Falls erforderlich, z. B. wenn die Entität nicht schlüssellos ist, muss die Zuordnung zur Tabelle mithilfe der ToTable-Methode explizit angegeben werden.If necessary - for example when the entity is not keyless - mapping to the table must be specified explicitly using ToTable method.

Hinweis

Abfragbare Funktionen müssen einer Tabellenwertfunktion zugeordnet werden und können HasTranslation nicht verwenden.Queryable function must be mapped to a table-valued function and can't use of HasTranslation.

Sehen Sie sich die folgende Abfrage an, bei der die Funktion zugeordnet ist:When the function is mapped, the following query:

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

Sie führt zu folgender Ausgabe:Produces:

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