Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
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 Methodenkörper der CLR-Methode spielt keine Rolle. 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.
Hinweis
Im Beispiel wird die Methode auf DbContext definiert, kann aber auch als statische Methode innerhalb anderer Klassen definiert werden.
Diese Funktionsdefinition kann nun einer benutzerdefinierten Funktion in der Modellkonfiguration zugeordnet werden:
modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
.HasName("CommentedPostCountForBlog")
.HasSchema("dbo");
Die Lambda-Überladung von HasDbFunction verhindert das manuelle Nachschlagen der MethodInfo. Die default Argumentwerte werden nur verwendet, um die Methode zu identifizieren. Sie werden nie an die Datenbank gesendet.
Standardmäßig ordnet EF Core die CLR-Methode einer Datenbankfunktion mit demselben Namen im Standardschema zu. Verwenden Sie HasName und HasSchema, wenn sich der Name oder das Schema unterscheidet.
Führen Sie nun die folgende Abfrage aus:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Dies führt zu folgendem SQL-Code:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Zuordnen einer Methode zu einer integrierten Funktion
EF Core betrachtet eine zugeordnete Funktion standardmäßig als benutzerdefinierte Funktion. Einige Datenbanken unterscheiden integrierte und benutzerdefinierte Funktionen beim Generieren von SQL. Beispielsweise müssen benutzerdefinierte Funktionen in SQL Server mit Schemaangabe versehen werden, integrierte Funktionen jedoch nicht.
Verwenden Sie IsBuiltIn, um eine CLR-Methode einer eingebauten Funktion zuzuordnen:
public static int IsDate(string value)
=> throw new NotSupportedException();
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
.HasName("ISDATE")
.IsBuiltIn();
Die IsBuiltIn Eigenschaft stellt dieselbe Konfiguration bei Verwendung eines Attributs bereit:
[DbFunction(Name = "ISDATE", IsBuiltIn = true)]
Zuordnen einer Funktion mithilfe von DbFunctionAttribute
Anstatt in OnModelCreating eine Funktion zu registrieren, kann eine in der DbContext deklarierte statische Methode durch Anwenden von DbFunctionAttribute direkt zugeordnet werden. Die Eigenschaften Schema, IsNullable, IsBuiltIn und Name des Attributs konfigurieren die entsprechenden Merkmale der Datenbankfunktion; dies sind dieselben Merkmale, die bei Verwendung von HasDbFunction durch die Fluent-API-Methoden HasName, HasSchema, IsBuiltIn und IsNullable konfiguriert werden. Attributierte Methoden im Kontext werden automatisch erkannt und registriert. Attributmethoden für andere Klassen müssen weiterhin registriert werden.HasDbFunction Rufen Sie HasDbFunction für eine automatisch registrierte Methode nur dann auf, wenn für eine zusätzliche Fluent-Konfiguration ein Builder benötigt wird, wie im folgenden Speichertyp-Beispiel.
Beispielsweise verwendet die folgende Methode DbFunctionAttribute, um die integrierte JSON_VALUE-Funktion von SQL Server zuzuordnen. Da IsBuiltIntrue ist, gibt EF Core den Funktionsnamen ohne Schema aus.
[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
=> throw new NotSupportedException();
Konfigurieren von Speichertypen
Dient HasStoreType zum Konfigurieren des Rückgabespeichertyps einer Funktion und HasStoreType zum Konfigurieren des Speichertyps eines Parameters. Dies ist besonders nützlich, wenn der CLR-Parametertyp keine systemeigene Datenbankzuordnung aufweist.
In diesem Beispiel ist JsonEntity.Metadata ein Wörterbuch, das über einen Wertkonverter als nvarchar(max) gespeichert wird. Der json-Funktionsparameter hat denselben Speichertyp, während das Ergebnis den nvarchar(4000)-Typ verwendet, der von JSON_VALUE zurückgegeben wird:
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)");
Die Funktion kann dann mit der konvertierten Eigenschaft verwendet werden:
var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));
SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]
Der Wertkonverter stammt aus dem Ausdruck, der als Funktionsargument übergeben wird. Daher funktioniert dieses Muster für eine zugeordnete Eigenschaft wie JsonEntity.Metadata, jedoch macht die Konfiguration des Typs des Parameterspeichers beliebige Wörterbuchwerte nicht übersetzbar. Um ein Speicherwörterbuch zu verwenden, serialisieren Sie es, und übergeben Sie die resultierende Zeichenfolge an eine separat zugeordnete Methode, deren CLR-Parameter lautet string.
Zuordnen einer Methode zu einem benutzerdefinierten SQL
EF Core ermöglicht auch die direkte Übersetzung einer CLR-Methode in einen SQL-Ausdruck und nicht in eine Datenbankfunktion. Der SQL-Ausdruck wird bei der Funktionskonfiguration mit HasTranslation angegeben.
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]
Caution
HasTranslation funktioniert mit der SQL-Ausdrucksstruktur, nicht mit SQL-Text. Die Übersetzung muss gültige SqlExpression-Objekte mit korrekten Typzuweisungen, Null-Zulässigkeit und Weitergabe der Null-Zulässigkeit von Argumenten erstellen. Falsche Metadaten können ungültige SQL- oder falsche Abfrageergebnisse erzeugen, und die von einer Übersetzung verwendeten Ausdruckstypen können für einen Datenbankanbieter spezifisch sein. Verwenden Sie diese API auf niedriger Ebene nur nach dem Verständnis der SQL-Ausdrucksstruktur des Anbieters; bevorzugen sie nach Möglichkeit eine normale Funktionszuordnung oder eine vorhandene Anbieterübersetzung.
Konfigurieren der Nullierbarkeit einer benutzerdefinierten Funktion basierend auf ihren Argumenten
Wenn sich die Nullbarkeit von einem Funktionsargument fortpflanzt – das heißt, die Funktion gibt immer dann null zurück, wenn dieses Argument null ist –, kann EF Core effizienteres SQL generieren. Konfigurieren Sie dies, indem Sie die relevanten Parameter aufrufen PropagatesNullability . Weitere Informationen dazu, wie EF Core die dreiwertige Logik von SQL kompensiert, finden Sie unter Abfrage-NULL-Semantik.
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.
Hinweis
Konfigurieren Sie eine Weitergabe der Null-Zulässigkeit nur, wenn die Funktion ausschließlich null zurückgeben kann, weil mindestens einer der konfigurierten Parameter null ist
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));
Tipp
Der FromExpression Aufruf im CLR-Funktionstext ermöglicht die Verwendung der Funktion anstelle eines regulären DbSets.
Unten sehen Sie die Zuordnung:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));
Hinweis
Eine abfragefähige Funktion muss einer Tabellenwertfunktion zugeordnet werden.
HasTranslation unterstützt nur skalare Funktionen und kann nicht für eine Tabellenwertfunktion verwendet werden.
Sehen Sie sich die folgende Abfrage an, bei der die Funktion zugeordnet ist:
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]
Zusammenarbeit auf GitHub
Die Quelle für diesen Inhalt finden Sie auf GitHub, wo Sie auch Issues und Pull Requests erstellen und überprüfen können. Weitere Informationen finden Sie in unserem Leitfaden für Mitwirkende.
