Mappage de fonctions définies par l’utilisateur
EF Core permet d’utiliser des fonctions de SQL définies par l’utilisateur dans des requêtes. Pour ce faire, les fonctions doivent être mappées à une méthode CLR lors de la configuration du modèle. Lors de la traduction de la requête LINQ en SQL, la fonction définie par l’utilisateur est appelée à la place de la fonction CLR à laquelle elle a été mappée.
Mappage d’une méthode à une fonction SQL
Pour illustrer le fonctionnement du mappage des fonctions définies par l’utilisateur, nous allons définir les entités suivantes :
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; }
}
Et la configuration de modèle suivante :
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
Le blog peut comporter de nombreuses publications et chaque billet peut comporter de nombreux commentaires.
Ensuite, créez la fonction définie par l’utilisateur CommentedPostCountForBlog, qui retourne le nombre de publications avec au moins un commentaire pour un blog donné, en fonction du 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
Pour utiliser cette fonction dans EF Core, nous définissons la méthode CLR suivante, que nous allons mapper à la fonction définie par l’utilisateur :
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
Le corps de la méthode CLR n’est pas important. La méthode n’est pas appelée côté client, sauf si EF Core ne peut pas traduire ses arguments. Si les arguments peuvent être traduits, EF Core uniquement la signature de la méthode.
Remarque
Dans l’exemple, la méthode est définie sur DbContext, mais elle peut également être définie en tant que méthode statique dans d’autres classes.
Cette définition de fonction peut désormais être associée à une fonction définie par l’utilisateur dans la configuration du modèle :
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
.HasName("CommentedPostCountForBlog");
Par défaut, EF Core tente de mapper la fonction CLR à une fonction définie par l’utilisateur portant le même nom. Si les noms diffèrent, nous pouvons utiliser HasName pour fournir le nom correct de la fonction définie par l’utilisateur à mapper.
À présent, exécutez la requête suivante :
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Produira cette SQL :
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Mappage d’une méthode à un SQL personnalisé
EF Core autorise également les fonctions définies par l’utilisateur qui sont converties en un SQL spécifique. L’expression SQL est fournie à l’aide de la HasTranslation méthode pendant la configuration de la fonction définie par l’utilisateur.
Dans l’exemple ci-dessous, nous allons créer une fonction qui calcule le pourcentage de différence entre deux entiers.
La méthode CLR est la suivante :
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
La définition de la fonction est la suivante :
// 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));
Une fois la fonction définie, elle peut être utilisée dans la requête. Au lieu d’appeler la fonction de base de données, EF Core traduira le corps de la méthode directement en SQL en fonction de l’arborescence d’expression SQL construite à partir de HasTranslation. La requête LINQ suivante :
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Génère l’instruction SQL suivante :
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Configuration de la possibilité de valeur null d’une fonction définie par l’utilisateur en fonction de ses arguments
Si la fonction définie par l’utilisateur ne peut retourner que null lorsqu’un ou plusieurs de ses arguments sont null, EFCore offre la possibilité de spécifier cela, ce qui permet d’obtenir des SQL plus performants. Pour ce faire, vous pouvez ajouter un PropagatesNullability() appel à la configuration de modèle de paramètres de fonction appropriée.
Pour illustrer cela, définissez la fonction utilisateur ConcatStrings :
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
et deux méthodes CLR qui sont mappées à celle-ci :
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
La configuration du modèle (à l’intérieur de la méthode OnModelCreating) est la suivante :
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();
});
La première fonction est configurée de manière standard. La deuxième fonction est configurée pour tirer parti de l’optimisation de la propagation de la possibilité de valeur null, en fournissant plus d’informations sur le comportement de la fonction autour des paramètres null.
Lors de l’émission des requêtes suivantes :
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");
Nous obtenons cette 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)
La deuxième requête n’a pas besoin de réévaluer la fonction elle-même pour tester sa possibilité de valeur null.
Remarque
Cette optimisation ne doit être utilisée que si la fonction ne peut être retournée que null lorsqu’il s’agit de paramètres null.
Mappage d’une fonction interrogeable à une fonction table
EF Core prend également en charge le mappage à une fonction table à l’aide d’une méthode CLR définie par l’utilisateur retournant un IQueryable de types d’entité, ce qui permet à EF Core de mapper les TVF avec les paramètres. Le processus est similaire au mappage d’une fonction scalaire définie par l’utilisateur à une fonction de SQL : nous avons besoin d’une TVF dans la base de données, d’une fonction CLR utilisée dans les requêtes LINQ et d’un mappage entre les deux.
À titre d’exemple, nous allons utiliser une fonction table qui retourne toutes les publications contenant au moins un commentaire qui correspond à un seuil « like » donné :
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
)
La signature de la méthode CLR est la suivante :
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
Conseil
L'appel FromExpression dans le corps de la fonction CLR permet d’utiliser la fonction à la place d’un DbSet normal.
Le mappage est le suivant :
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));
Remarque
Une fonction interrogeable doit être mappée à une fonction table et ne peut pas utiliser HasTranslation.
Lorsque la fonction est mappée, la requête suivante :
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
Produit :
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour