Mapeamento de funções definidas pelo usuário

O EF Core permite a utilização de funções SQL definidas pelo utilizador em consultas. Para isso, as funções precisam de ser mapeadas para um método CLR durante a configuração do modelo. Ao traduzir a consulta LINQ para SQL, a função definida pelo utilizador é chamada em vez da função CLR para a qual foi mapeada.

Mapear um método para uma função SQL

Para ilustrar como funciona o mapeamento de funções definidas pelo utilizador, vamos definir as seguintes entidades:

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 a seguinte configuração de modelo:

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

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

O blog pode ter muitos posts e cada post pode ter muitos comentários.

De seguida, crie a função CommentedPostCountForBlogdefinida pelo utilizador , que devolve a contagem de publicações com pelo menos um comentário para um dado blog, com base no 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

Para usar esta função no EF Core, definimos o seguinte método CLR, que mapeamos para a função definida pelo utilizador:

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

O corpo do método CLR não é importante. O método não será invocado do lado do cliente, a menos que o EF Core não consiga traduzir os seus argumentos. Se os argumentos puderem ser traduzidos, o EF Core só se preocupa com a assinatura do método.

Observação

No exemplo, o método está definido em DbContext, mas também pode ser definido como um método estático dentro de outras classes.

Esta definição de função pode agora ser associada a uma função definida pelo utilizador na configuração do modelo:

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

A sobrecarga com lambda de HasDbFunction evita procurar manualmente o MethodInfo. Os default valores dos argumentos são usados apenas para identificar o método; nunca são enviados para a base de dados.

Por defeito, o EF Core mapeia o método CLR para uma função de base de dados com o mesmo nome no esquema padrão. Usar HasName e HasSchema quando o nome ou esquema for diferente.

Agora, executando a seguinte consulta:

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

Vai produzir este SQL:

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

Mapear um método para uma função incorporada

O EF Core considera uma função mapeada como definida pelo utilizador por defeito. Algumas bases de dados distinguem funções incorporadas e definidas pelo utilizador ao gerar SQL. Por exemplo, o SQL Server exige que as funções definidas pelo utilizador sejam qualificadas para o esquema, mas as funções incorporadas não são qualificadas para o esquema.

Use IsBuiltIn para mapear um método CLR para uma função incorporada:

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

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

A IsBuiltIn propriedade fornece a mesma configuração ao usar um atributo:

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

Mapear uma função usando o DbFunctionAttribute

Em vez de registar uma função em OnModelCreating, um método estático declarado no DbContext pode ser mapeado diretamente aplicando DbFunctionAttribute. As propriedades Name, HasName, HasSchema e IsBuiltIn do atributo configuram as características correspondentes da função de base de dados; estas são as mesmas características configuradas pelos métodos da API fluente IsNullable, HasDbFunction, IsNullable e IsBuiltIn ao usar Schema. Os métodos atribuídos no contexto são descobertos e registados automaticamente; os métodos atribuídos noutras classes devem ainda estar registados com HasDbFunction. Chame HasDbFunction para um método registado automaticamente apenas quando for necessário um builder para configuração fluente adicional, como no exemplo do tipo de armazenamento abaixo.

Por exemplo, o método seguinte serve DbFunctionAttribute para mapear a função incorporada JSON_VALUE do SQL Server. Como IsBuiltIn é true, o EF Core emite o nome da função sem um esquema.

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

Configuração dos tipos de armazenamento

Use HasStoreType para configurar o tipo de armazenamento de retorno de uma função e HasStoreType para configurar o tipo de armazenamento de um parâmetro. Isto é particularmente útil quando o tipo de parâmetro CLR não tem mapeamento nativo da base de dados.

Neste exemplo, JsonEntity.Metadata é um dicionário armazenado como nvarchar(max) através de um conversor de valores. O json parâmetro da função tem o mesmo tipo de armazenamento, enquanto o resultado usa o nvarchar(4000) tipo devolvido por 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)");

A função pode então ser usada com a propriedade convertida:

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

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

O conversor de valores é retirado da expressão passada como argumento de função. Portanto, este padrão funciona para uma propriedade mapeada como JsonEntity.Metadata, mas configurar o tipo de armazenamento de parâmetros não torna transferíveis valores arbitrários do dicionário. Para usar um dicionário em memória, serialize-o e passa a cadeia resultante para um método mapeado separadamente cujo parâmetro CLR seja string.

Mapear um método para um SQL personalizado

O EF Core também permite que um método CLR seja traduzido diretamente para uma expressão SQL em vez de uma função de base de dados. A expressão SQL é fornecida através de HasTranslation durante a configuração da função.

No exemplo abaixo, vamos criar uma função que calcula a diferença percentual entre dois inteiros.

O método CLR é o seguinte:

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

A definição da função é a seguinte:

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

Depois de definirmos a função, ela pode ser usada na consulta. Em vez de chamar a função da base de dados, o EF Core irá traduzir o corpo do método diretamente para SQL com base na árvore de expressões SQL construída a partir do HasTranslation. A seguinte consulta LINQ:

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

Produz o seguinte SQL:

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

Caution

HasTranslation funciona com a árvore de expressões SQL, não com texto SQL. A tradução deve construir objetos SqlExpression válidos com os mapeamentos de tipo corretos, a nulidade correta e a propagação correta da nulidade dos argumentos. Metadados incorretos podem produzir SQL inválido ou resultados de consulta incorretos, e os tipos de expressão usados por uma tradução podem ser específicos de um fornecedor de bases de dados. Use esta API de baixo nível apenas depois de compreender a árvore de expressões SQL do fornecedor; prefira um mapeamento regular de funções ou uma tradução de fornecedor existente sempre que possível.

Configuração da nulidade de uma função definida pelo utilizador com base nos seus argumentos

Se a anulabilidade se propagar a partir de um argumento de função — ou seja, a função retorna null sempre que esse argumento é null— o EF Core pode gerar SQL mais eficiente. Configure isto chamando PropagatesNullability com os parâmetros relevantes. Para mais informações sobre como o EF Core compensa a lógica de três valores do SQL, veja Consultar semântica nula.

Para ilustrar isto, defina a função ConcatStrings de utilizador:

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

e dois métodos CLR que fazem a correspondência para ele:

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

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

A configuração do modelo (dentro do método OnModelCreating) é a seguinte:

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

A primeira função é configurada da forma padrão. A segunda função está configurada para tirar partido da otimização de propagação de anulabilidade, fornecendo mais informação sobre como a função se comporta em relação aos parâmetros nulos.

Ao emitir as seguintes consultas:

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

Obtemos este 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)

A segunda consulta não precisa de reavaliar a própria função para testar a sua nulidade.

Observação

Só configurar a propagação da nulidade quando a função pode devolver null apenas porque um ou mais dos parâmetros configurados são null.

Mapear uma função consultável para uma função com valores de tabela

O EF Core também suporta o mapeamento para uma função com valores de tabela usando um método CLR definido pelo utilizador que retorna um conjunto IQueryable de tipos de entidades, permitindo ao EF Core mapear funções com valores de tabela (TVFs) com parâmetros. O processo é semelhante a mapear uma função escalar definida pelo utilizador para uma função SQL: precisamos de um TVF na base de dados, uma função CLR usada nas consultas LINQ e um mapeamento entre as duas.

Por exemplo, usaremos uma função que retorna valores em forma de tabela, que devolve todas as publicações que tenham pelo menos um comentário que cumpra um determinado limiar de "Gosto".

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
)

A assinatura do método CLR é a seguinte:

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

Sugestão

A FromExpression chamada no corpo da função CLR permite que a função seja usada em vez de um DbSet normal.

E abaixo está o mapeamento:

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

Observação

Uma função consultável deve ser mapeada para uma função com valores de tabela. HasTranslation suporta apenas funções escalares e não pode ser usado para uma função com valores de tabela.

Quando a função é mapeada, a seguinte consulta:

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

Produz:

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