Mapping delle funzioni definite dall'utente

EF Core consente di usare funzioni SQL definite dall'utente nelle query. A tale scopo, è necessario eseguire il mapping delle funzioni a un metodo CLR durante la configurazione del modello. Quando si converte la query LINQ in SQL, la funzione definita dall'utente viene chiamata anziché la funzione CLR a cui è stato eseguito il mapping.

Mapping di un metodo a una funzione SQL

Per illustrare il funzionamento del mapping delle funzioni definite dall'utente, definire le entità seguenti:

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

E la configurazione del modello seguente:

modelBuilder.Entity<Blog>()
    .HasMany(b => b.Posts)
    .WithOne(p => p.Blog);

modelBuilder.Entity<Post>()
    .HasMany(p => p.Comments)
    .WithOne(c => c.Post);

Il blog può avere molti post e ogni post può avere molti commenti.

Creare quindi la funzione CommentedPostCountForBlogdefinita dall'utente , che restituisce il conteggio dei post con almeno un commento per un determinato blog, in base al 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

Per usare questa funzione in EF Core, viene definito il metodo CLR seguente, che viene mappato alla funzione definita dall'utente:

public int ActivePostCountForBlog(int blogId)
    => throw new NotSupportedException();

Il corpo del metodo CLR non è importante. Il metodo non verrà richiamato sul lato client, a meno che EF Core non possa tradurre i relativi argomenti. Se gli argomenti possono essere tradotti, EF Core si occupa solo della firma del metodo.

Questa definizione di funzione può ora essere associata a una funzione definita dall'utente nella configurazione del modello:

modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
    .HasName("CommentedPostCountForBlog")
    .HasSchema("dbo");

Il sovraccarico lambda di HasDbFunction evita di individuare manualmente MethodInfo. I valori dell'argomento default vengono usati solo per identificare il metodo e non vengono mai inviati al database.

Per impostazione predefinita, EF Core esegue il mapping del metodo CLR a una funzione di database con lo stesso nome nello schema predefinito. Usare HasName e HasSchema quando il nome o lo schema è diverso.

Ora si esegue la query seguente:

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

Produrrà questo codice SQL:

SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1

Associare un metodo a una funzione predefinita

EF Core considera una funzione mappata come definita dall'utente per impostazione predefinita. Alcuni database distinguono le funzioni predefinite e definite dall'utente durante la generazione di SQL. Ad esempio, SQL Server richiede che le funzioni definite dall'utente siano qualificate per lo schema, ma le funzioni predefinite non sono qualificate per lo schema.

Usare IsBuiltIn per eseguire il mapping di un metodo CLR a una funzione predefinita:

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

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

La IsBuiltIn proprietà fornisce la stessa configurazione quando si usa un attributo:

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

Mappatura di una funzione tramite DbFunctionAttribute

Anziché registrare una funzione in , un metodo statico dichiarato in OnModelCreatingDbContext può essere mappato direttamente applicando DbFunctionAttribute. Le proprietà Name, HasName, IsBuiltIn e HasSchema dell'attributo configurano le caratteristiche corrispondenti della funzione del database; si tratta delle stesse caratteristiche configurate dai metodi dell'API fluente IsNullable, HasDbFunction, IsNullable e IsBuiltIn quando si usa Schema. I metodi con attributi nel contesto vengono individuati e registrati automaticamente; I metodi con attributi in altre classi devono comunque essere registrati con HasDbFunction. Richiamare HasDbFunction per un metodo registrato automaticamente solo quando è necessario un builder per un'ulteriore configurazione fluente, come nell'esempio del tipo di archivio qui sotto.

Ad esempio, il metodo seguente usa DbFunctionAttribute per eseguire il mapping della funzione predefinita JSON_VALUE di SQL Server. Poiché IsBuiltIn è true, EF Core genera il nome della funzione senza uno schema.

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

Configurazione dei tipi di archivio

Usare HasStoreType per configurare il tipo di archivio restituito di una funzione e HasStoreType per configurare il tipo di archivio di un parametro. Ciò è particolarmente utile quando il tipo di parametro CLR non dispone di mapping del database nativo.

In questo esempio, JsonEntity.Metadata è un dizionario archiviato come nvarchar(max) tramite un convertitore di valori. Il json parametro della funzione ha lo stesso tipo di archivio, mentre il risultato usa il nvarchar(4000) tipo restituito da 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 funzione può quindi essere usata con la proprietà convertita:

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

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

Il convertitore di valori viene ricavato dall'espressione passata come argomento della funzione. Pertanto, questo modello funziona per una proprietà mappata, JsonEntity.Metadataad esempio , ma la configurazione del tipo di archivio dei parametri non rende traducibili valori arbitrari del dizionario. Per usare un dizionario in memoria, serializzarlo e passare la stringa risultante a un metodo mappato separatamente il cui parametro CLR è string.

Mapping di un metodo a un SQL personalizzato

EF Core consente anche di convertire un metodo CLR direttamente in un'espressione SQL anziché in una funzione di database. L'espressione SQL viene fornita usando HasTranslation durante la configurazione della funzione.

Nell'esempio seguente si creerà una funzione che calcola la differenza percentuale tra due numeri interi.

Il metodo CLR è il seguente:

public double PercentageDifference(double first, int second)
    => throw new NotSupportedException();

La definizione della funzione è la seguente:

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

Dopo aver definito la funzione, può essere usata nella query. Anziché chiamare la funzione di database, EF Core trasla il corpo del metodo direttamente in SQL in base all'albero delle espressioni SQL costruito da HasTranslation. La query LINQ seguente:

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

Produce il codice SQL seguente:

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

Configurazione di valori Null della funzione definita dall'utente in base ai relativi argomenti

Se la nullabilità si propaga da un argomento della funzione, cioè la funzione restituisce null ogni volta che tale argomento è null, EF Core può generare SQL più efficiente. Configurare questa operazione chiamando PropagatesNullability per i parametri pertinenti. Per altre informazioni su come EF Core compensa la logica ternaria di SQL, vedere Semantica null nelle query.

Per illustrare questo concetto, definire la funzione utente ConcatStrings:

CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    RETURN @prm1 + @prm2;
END

e due metodi CLR che si mappano a esso:

public string ConcatStrings(string prm1, string prm2)
    => throw new InvalidOperationException();

public string ConcatStringsOptimized(string prm1, string prm2)
    => throw new InvalidOperationException();

La configurazione del modello (all'interno OnModelCreating del metodo) è la seguente:

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 prima funzione viene configurata nel modo standard. La seconda funzione è configurata per sfruttare l'ottimizzazione della propagazione della nullità, fornendo ulteriori informazioni sul comportamento della funzione quando si tratta di parametri null.

Quando si eseguono le query seguenti:

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

Otteniamo il seguente 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 seconda query non deve rivalutare la funzione stessa per testare la sua nullabilità.

Mappatura di una funzione interrogabile in una funzione a valori di tabella

EF Core supporta anche il mapping a una funzione che restituisce una tabella, utilizzando un metodo CLR definito dall'utente che restituisce un IQueryable di tipi di entità, consentendo a EF Core di eseguire il mapping delle funzioni TVF con parametri. Il processo è simile al mapping di una funzione scalare definita dall'utente a una funzione SQL: nel database è necessaria una funzione TVF, una funzione CLR usata nelle query LINQ e un mapping tra i due.

Ad esempio, si userà una funzione con valori di tabella che restituisce tutti i post con almeno un commento che soddisfa una determinata soglia "Like":

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 firma del metodo CLR è la seguente:

public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
    => FromExpression(() => PostsWithPopularComments(likeThreshold));

Di seguito è riportato il mapping:

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

Quando viene eseguito il mapping della funzione, la query seguente:

var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
             orderby p.Rating
             select p;

Produce:

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]