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 alla funzione definita dall'utente nella configurazione del modello:

modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
    .HasName("CommentedPostCountForBlog");

Per impostazione predefinita, EF Core tenta di eseguire il mapping della funzione CLR a una funzione definita dall'utente con lo stesso nome. Se i nomi differiscono, è possibile usare HasName per specificare il nome corretto per la funzione definita dall'utente a cui si vuole eseguire il mapping.

Eseguire ora 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

Mapping di un metodo a un'istanza SQL personalizzata

EF Core consente anche funzioni definite dall'utente che vengono convertite in un'istanza di SQL specifica. L'espressione SQL viene fornita usando il HasTranslation metodo durante la configurazione della funzione definita dall'utente.

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

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 funzione definita dall'utente può restituire null solo quando uno o più dei relativi argomenti sono null, EFCore consente di specificare che, con conseguente maggiore prestazioni di SQL. A tale scopo, è possibile aggiungere una PropagatesNullability() chiamata alla configurazione del modello di parametri di funzione pertinente.

Per illustrare questo concetto, definire la funzione ConcatStringsutente :

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

e due metodi CLR che eseguono il mapping:

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), 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 prima funzione viene configurata nel modo standard. La seconda funzione è configurata per sfruttare i vantaggi dell'ottimizzazione della propagazione dei valori Null, fornendo altre informazioni sul comportamento della funzione intorno ai 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");

Si ottiene questo CODICE 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 il supporto dei valori Null.

Mapping di una funzione queryable a una funzione con valori di tabella

EF Core supporta anche il mapping a una funzione con valori di tabella usando un metodo CLR definito dall'utente che restituisce un IQueryable di tipi di entità, consentendo a EF Core di eseguire il mapping delle funzioni 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), new[] { 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]