Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of mappen te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen om mappen te wijzigen.
EF Core maakt het gebruik van door de gebruiker gedefinieerde SQL-functies in query's mogelijk. Hiervoor moeten de functies tijdens de modelconfiguratie worden toegewezen aan een CLR-methode. Bij het vertalen van de LINQ-query naar SQL wordt de door de gebruiker gedefinieerde functie aangeroepen in plaats van de CLR-functie waaraan deze is toegewezen.
Een methode toewijzen aan een SQL-functie
Laten we de volgende entiteiten definiëren om te laten zien hoe door de gebruiker gedefinieerde functietoewijzing werkt:
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; }
}
En de volgende modelconfiguratie:
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
Blog kan veel berichten bevatten en elk bericht kan veel opmerkingen bevatten.
Maak vervolgens de door de gebruiker gedefinieerde functie CommentedPostCountForBlog, die het aantal berichten met ten minste één opmerking voor een bepaalde blog retourneert, op basis van het 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
Als u deze functie in EF Core wilt gebruiken, definiëren we de volgende CLR-methode, die we toewijzen aan de door de gebruiker gedefinieerde functie:
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
De inhoud van de CLR-methode is niet belangrijk. De methode wordt niet aangeroepen aan de clientzijde, tenzij EF Core de argumenten niet kan omzetten. Als de argumenten kunnen worden vertaald, geeft EF Core uitsluitend om de handtekening van de methode.
Opmerking
In het voorbeeld wordt de methode gedefinieerd op DbContext, maar deze kan ook worden gedefinieerd als een statische methode in andere klassen.
Deze functiedefinitie kan nu worden gekoppeld aan de door de gebruiker gedefinieerde functie in de modelconfiguratie:
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), [typeof(int)]))
.HasName("CommentedPostCountForBlog");
Standaard probeert EF Core de CLR-functie toe te wijzen aan een door de gebruiker gedefinieerde functie met dezelfde naam. Als de namen verschillen, kunnen we HasName de juiste naam opgeven voor de door de gebruiker gedefinieerde functie waaraan we willen toewijzen.
Voer nu de volgende query uit:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Hiermee wordt deze SQL geproduceerd:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Een methode toewijzen aan een aangepaste SQL
EF Core biedt ook door de gebruiker gedefinieerde functies die worden geconverteerd naar een specifieke SQL. De SQL-expressie wordt geleverd met behulp van HasTranslation de methode tijdens de door de gebruiker gedefinieerde functieconfiguratie.
In het onderstaande voorbeeld maken we een functie waarmee het percentageverschil tussen twee gehele getallen wordt berekend.
De CLR-methode is als volgt:
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
De functiedefinitie is als volgt:
// 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));
Zodra we de functie hebben gedefinieerd, kan deze worden gebruikt in de query. In plaats van de databasefunctie aan te roepen, vertaalt EF Core de methodebody direct in SQL op basis van de SQL-expressieboom die is geconstrueerd vanuit de HasTranslation. De volgende LINQ-query:
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Produceert de volgende SQL:
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Nullbaarheid van gebruikersgedefinieerde functie configureren op basis van zijn argumenten
Als de door de gebruiker gedefinieerde functie alleen kan worden geretourneerd null wanneer een of meer van de argumenten zijn null, biedt EFCore een manier om dat op te geven, wat resulteert in meer presterende SQL. U kunt dit doen door een PropagatesNullability() aanroep toe te voegen aan de configuratie van het relevante functieparametersmodel.
Om dit te illustreren, definieert u de gebruikersfunctie ConcatStrings:
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
en twee CLR-methoden die ermee overeenkomen:
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
De modelconfiguratie (binnen OnModelCreating de methode) is als volgt:
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();
});
De eerste functie wordt op de standaard manier geconfigureerd. De tweede functie is geconfigureerd om te profiteren van de optimalisatie voor het doorgeven van null-waarden, zodat u meer informatie krijgt over hoe de functie zich gedraagt rond null-parameters.
Bij het uitvoeren van de volgende 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");
We krijgen deze 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)
De tweede query hoeft de functie zelf niet opnieuw te evalueren om de null-waarde ervan te testen.
Opmerking
Deze optimalisatie mag uitsluitend worden gebruikt wanneer de functie alleen null kan teruggeven als de parameters null zijn.
Een opvraagbare functie toewijzen aan een tabelwaarde-functie
EF Core biedt ook ondersteuning voor toewijzing aan een tabelwaardefunctie met behulp van een door de gebruiker gedefinieerde CLR-methode die een IQueryable entiteitstype retourneert, zodat EF Core TVF's kan toewijzen aan parameters. Het proces is vergelijkbaar met het toewijzen van een scalaire door de gebruiker gedefinieerde functie aan een SQL-functie: we hebben een TVF in de database nodig, een CLR-functie die wordt gebruikt in de LINQ-query's en een toewijzing tussen de twee.
Als voorbeeld gebruiken we een tabelwaardefunctie die alle berichten retourneert met ten minste één opmerking die voldoet aan een bepaalde drempelwaarde voor Vind ik leuk:
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
)
De CLR methodesignatuur is als volgt:
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
Aanbeveling
De aanroep FromExpression in de functiebody van de CLR maakt het mogelijk de functie te gebruiken in plaats van een gewone DbSet.
Hieronder ziet u de mapping:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));
Opmerking
Een doorzoekbare functie moet worden toegewezen aan een tabelwaardefunctie en kan geen gebruik maken van HasTranslation.
Wanneer de functie is toegewezen, voert u de volgende query uit:
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
Produceert:
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
Werk met ons samen op GitHub
De bron voor deze inhoud vindt u op GitHub, waar u ook problemen en pull-aanvragen kunt maken en bekijken. Raadpleeg onze gids voor inzenders voor meer informatie.
