Partage via

Mappage de fonctions définies par l’utilisateur

EF Core permet d’utiliser des fonctions SQL définies par l’utilisateur dans les requêtes. Pour ce faire, les fonctions doivent être mappées à une méthode CLR pendant la configuration du modèle. Lors de la traduction de la requête LINQ vers SQL, la fonction définie par l’utilisateur est appelée au lieu de la fonction CLR à laquelle elle a été mappée.

Mappage d’une méthode à une fonction SQL

Pour illustrer le fonctionnement du mappage de fonction défini 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 avoir de nombreux billets et chaque billet peut avoir de nombreux commentaires.

Ensuite, créez la fonction CommentedPostCountForBlogdéfinie par l’utilisateur, qui retourne le nombre de billets 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 mappons à 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 se soucie uniquement de la signature de méthode.

Note

Dans l’exemple, la méthode est définie sur DbContext, mais elle peut également être définie comme une méthode statique à l’intérieur d’autres classes.

Cette définition de fonction peut désormais être associée à la fonction définie par l’utilisateur dans la configuration du modèle :

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), [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 approprié pour la fonction définie par l’utilisateur à laquelle nous voulons mapper.

Nous exécutons maintenant la requête suivante :

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

Produit ce code 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 permet également des fonctions définies par l’utilisateur qui sont converties en SQL spécifique. L’expression SQL est fournie en utilisant la méthode HasTranslation lors de la configuration de la fonction définie par l’utilisateur.

Dans l’exemple ci-dessous, nous allons créer une fonction qui calcule la différence de pourcentage 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), [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));

Une fois que nous définissons la fonction, elle peut être utilisée dans la requête. Au lieu d’appeler la fonction de base de données, EF Core traduit le corps de la méthode directement en SQL en fonction de l’arborescence d’expressions SQL construite à partir de HasTranslation. Requête LINQ suivante :

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

Produit le code SQL suivant :

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

Configuration de la nullabilité de la fonction définie par l’utilisateur en fonction de ses arguments

Si la fonction définie par l’utilisateur ne peut retourner null que lorsqu'un ou plusieurs de ses arguments sont null, EFCore permet de spécifier cela, ce qui entraîne une exécution SQL plus efficace. Cette tâche peut être effectuée en ajoutant un PropagatesNullability() appel à la configuration du modèle des paramètres de fonction concernés.

Pour illustrer cela, définissez la fonction ConcatStringsutilisateur :

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 correspondent à 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 OnModelCreating de la méthode) est la suivante :

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

La première fonction est configurée de la manière standard. La deuxième fonction est configurée pour tirer parti de l’optimisation de la propagation de la nullabilité, 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 ce code 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 nullabilité.

Note

Cette optimisation ne doit être utilisée que si la fonction ne peut que retourner null lorsque ses paramètres sont null.

Mappage d'une fonction interrogable à une fonction à valeurs de table

EF Core prend également en charge le mappage à une fonction table à l’aide d’une méthode CLR définie par l’utilisateur qui retourne un type d’entité IQueryable , ce qui permet à EF Core de mapper des fichiers TVF avec des paramètres. Le processus est similaire au mappage d’une fonction scalaire définie par l’utilisateur à une fonction SQL : nous avons besoin d’une fonction 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.

Par exemple, nous allons utiliser une fonction table-valorisée qui retourne tous les billets ayant au moins un commentaire répondant à 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 / Astuce

L’appel FromExpression dans le corps de la fonction CLR permet d’utiliser la fonction au lieu d’un DbSet classique.

Vous trouverez ci-dessous la cartographie :

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

Note

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]
  • Last updated on