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.

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(() => ActivePostCountForBlog(default))
    .HasName("CommentedPostCountForBlog")
    .HasSchema("dbo");

La surcharge lambda de HasDbFunction évite de rechercher manuellement le MethodInfo. Les default valeurs d’argument sont utilisées uniquement pour identifier la méthode ; elles ne sont jamais envoyées à la base de données.

Par défaut, EF Core mappe la méthode CLR à une fonction de base de données portant le même nom dans le schéma par défaut. Utilisez HasName et HasSchema lorsque le nom ou le schéma diffère.

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 à une fonction intégrée

EF Core considère qu’une fonction mappée doit être définie par l’utilisateur par défaut. Certaines bases de données distinguent les fonctions intégrées et définies par l’utilisateur lors de la génération de SQL. Par exemple, SQL Server nécessite que les fonctions définies par l'utilisateur soient qualifiées de schéma, mais les fonctions intégrées ne sont pas qualifiées par le schéma.

Permet IsBuiltIn de mapper une méthode CLR à une fonction intégrée :

public static int IsDate(string value)
    => throw new NotSupportedException();

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
    .HasName("ISDATE")
    .IsBuiltIn();

La IsBuiltIn propriété fournit la même configuration lors de l’utilisation d’un attribut :

[DbFunction(Name = "ISDATE", IsBuiltIn = true)]

Mappage d’une fonction à l’aide de DbFunctionAttribute

Au lieu d’enregistrer une fonction dans OnModelCreating, une méthode statique déclarée sur DbContext peut être associée directement en appliquant DbFunctionAttribute. Les propriétés IsNullable, IsBuiltIn, Schema et Name de l’attribut configurent les caractéristiques correspondantes de la fonction de base de données ; ce sont les mêmes caractéristiques que celles configurées par les méthodes HasName, HasSchema, IsBuiltIn et IsNullable de l’API Fluent lors de l’utilisation de HasDbFunction. Les méthodes avec attribut dans le contexte sont détectées et enregistrées automatiquement ; les méthodes avec attribut dans d’autres classes doivent toujours être enregistrées à l’aide de HasDbFunction. Appelez HasDbFunction pour une méthode enregistrée automatiquement uniquement lorsqu’un builder est nécessaire pour une configuration fluide supplémentaire, comme dans l’exemple de type de magasin ci-dessous.

Par exemple, la méthode suivante utilise DbFunctionAttribute pour mapper la fonction intégrée JSON_VALUE de SQL Server. Étant donné que true est IsBuiltIn, EF Core génère le nom de la fonction sans schéma.

[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
    => throw new NotSupportedException();

Configuration des types de magasins

Permet HasStoreType de configurer le type de magasin de retour d’une fonction et HasStoreType de configurer le type de magasin d’un paramètre. Cela est particulièrement utile lorsque le type de paramètre CLR n’a pas de mappage de base de données natif.

Dans cet exemple, JsonEntity.Metadata est un dictionnaire stocké sous la forme de nvarchar(max) via un convertisseur de valeurs. Le paramètre de la fonction json a le même type de stockage, tandis que le résultat utilise le type nvarchar(4000) renvoyé par JSON_VALUE:

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

La fonction peut ensuite être utilisée avec la propriété convertie :

var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));

SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]

Le convertisseur de valeur est extrait de l’expression passée en tant qu’argument de fonction. Par conséquent, ce modèle fonctionne pour une propriété mappée telle que JsonEntity.Metadata, mais la configuration du type de magasin de paramètres ne rend pas les valeurs de dictionnaire arbitraires traduites. Pour utiliser un dictionnaire en mémoire, sérialisez-le et passez la chaîne résultante à une méthode mappée séparément dont le paramètre CLR est string.

Mappage d’une méthode à un SQL personnalisé

EF Core permet également à une méthode CLR d’être traduite directement en expression SQL plutôt qu’en fonction de base de données. L’expression SQL est fournie au moyen de HasTranslation lors de la configuration de la fonction.

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 nullabilité se propage à partir d’un argument de fonction, autrement dit, la fonction retourne null chaque fois que cet argument est null— EF Core peut générer un SQL plus efficace. Configurez-le en appelant PropagatesNullability avec les paramètres appropriés. Pour plus d’informations sur la façon dont EF Core compense la logique à trois valeurs de SQL, consultez la sémantique de requête null.

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é.

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

Vous trouverez ci-dessous la cartographie :

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

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]