Executando funções personalizadas no banco de dados usando SqlExpression no .Net

Olá, pessoal! Espero encontrar todos bem. Hoje teremos um material "mão na massa", demonstrando como fazer o EF Core (Entity Framework Core) ser capaz de traduzir funções personalizadas no banco de dados em expressões construídas com Linq (Consulta Integrada à Linguagem), que normalmente, não seria possível traduzi-las.

Se você trabalha com a linguagem de programação C#, certamente já escreveu alguma query com Linq que executa perfeitamente quando a lista de dados da operação envolvida já está carregada em memória, mas teve uma triste surpresa ao tentar aplicar o mesmo código acessando dados que estavam no banco, recebendo algum erro similar a este:

 The LINQ expression could not be translated

Isso ocorre porque há um limite acerca do que o EF Core é capaz de traduzir para linguagem nativa do banco de dados configurado em seu projeto, restringindo assim as possibilidades de uso do que pode ser feito diretamente no banco, em ralação ao que pode ser feito quando os dados já estão em memória. Mas há uma luz no fim do túnel, e não se preocupe, não é um trem vindo em sua direção... Podemos usar o recurso SqlExpression para ensinar o EF Core a traduzir comandos que ele nativamente não consegue, sendo essas operações nativas do seu banco de dados, ou funções criadas por você dentro do banco de dados.

Antes de começarmos efetivamente, vamos dar uma olhada nas versões de pacotes e programas que estou usando, sabemos que o modo como os recursos são implementados podem variar de acordo com suas versões, então para não haver mal-entendidos, estou usando o .Net 5 e o banco de dados SQL Server 2016, um pouco datado, eu sei.

Vamos começar com um problema simples. Imagine que você precise realizar uma consulta em uma massa de dados de Players, e retornar às seguintes propriedades: Name, Score, e a posição do player no ranking, que claro, é calculado a partir do Score. Para atender a essa demanda, um simples código como o seguinte pode ser usado

var playerList = new List<Player>()
{
    new Player() { Name = "Mauricio", Score = 10 },
    new Player() { Name = "André", Score = 6 },
    new Player() { Name = "Redmerski", Score = 8 },
};

var playerDtoList = playerList
    .OrderByDescending(player => player.Score)
    .Select((player, index) => new PlayerDto()
    {
        Name = player.Name,
        Score = player.Score,
        Position = index + 1,
    })
    .ToList();

Esse código funciona perfeitamente porque os dados que esse algoritmo manipula estão carregados em memória, se você trocar a variável playerList de modo a apontar para uma base de dados, usando dbContext.PlayersDbSet por exemplo, descobrirá que isso não pode ser traduzido para um comando SQL. O parâmetro index contido no Select representa a posição do item percorrido dentro da lista carregada em memória, logo se os dados não estão em memória, essa informação não existe. Poderíamos esperar que essa posição fosse substituída pela função do banco de dados que determina a linha da consulta, chamado de ROW_NUMBE no SQL Server, mas o EF Core não tem uma forma simples de executar essa operação para retornar o número da linha do registro em uma consulta ao banco de dados.

PS: Eu sei que você está com vontade de comentar que esse código não prevê a possibilidade de dois ou mais jogadores estarem empatados, mas quero manter as coisas simples, então segura essa vontade 😁.

Certo, então como podemos tornar essa operação traduzível ao banco de dados? Muito simples meu caro leitor, basta seguirmos os passos abaixo. Ah, os nomes e estruturas que mostro abaixo são uma representação de como eu acredito que essas funções devem ser organizadas, fique à vontade para alterá-las.

Começamos criando um diretório dentro do seu projeto responsável por lidar com a infraestrutura, especificamente o banco de dados, chamo essa pasta de Functions, dentro dessa pasta crio um arquivo chamado EfCoreCustomFunctions, dentro desse crio uma função chamada RowNumber que recebe três parâmetros, um parâmetro do tipo long que representará a coluna do banco de dados também do mesmo tipo que será usada para particionar a enumeração, um segundo parâmetro do tipo long que representará a coluna pela qual a ordenação deve ser aplicada para a enumeração, e um parâmetro booleano que indica a direção de ordenação, quando verdadeiro a ordem é ascendente. Observe que o conteúdo dessa função não é importante, então podemos simplesmente lançar uma exceção.

using System;

namespace MyProject.EntityFrameworkCore
{
    public static class EfCoreCustomFunctions
    {
        public static double RowNumber(long? partition, long ordering, bool ascending)
            => throw new NotSupportedException();
    }
}

Em seguida crio um arquivo na mesma pasta chamado RowNumberFunction, é dentro desse arquivo que a função realmente será criada. Nesse arquivo crio uma função de extensão para a classe ModelBuilder.

namespace MyProject.EntityFrameworkCore
{
    public static class RowNumberFunction
    {
        public static ModelBuilder AddRowNumberFunction(this ModelBuilder modelBuilder)
        {
            return modelBuilder;
        }
    }
}

A primeira coisa a ser feita é recuperar o tipo da classe que criamos para manter as assinaturas dos nossos métodos, e usar a função GetMethod para capturar a assinatura do método específico desejado. Eu gosto de especificar explicitamente os tipos dos parâmetros da função em questão, passando-os como segundo parâmetro da função GetMethod, mas se houver apenas uma função com o nome especificado, essa etapa é opcional.

var method = typeof(EfCoreCustomFunctions)
    .GetMethod(
        nameof(EfCoreCustomFunctions.RowNumber),
        new[] { typeof(long?), typeof(long), typeof(bool) });

Em seguida vem o passo mais importante: instruir o EF Core de fato a como traduzir esse comando, e podemos fazer isso de algumas formas

Depois de usar o modelMuilder para chamar a função HasDbFunction passando como parâmetro a variável method criada na etapa anterior, podemos usar a função HasName e passar o nome de uma função personalizada que nós criamos diretamente no banco.

modelBuilder.HasDbFunction(method).HasName("MinhaFuncaoJaCriadaNaBase");

Eu particularmente não gosto de criar funções diretamente no banco de dados, entendo como a melhor opção usarmos o método HasTranslation após a função HasDbFunction, esse método nos permite instruir o EF Core a como a operação precisa ser criada, sem que a função exista previamente na base, assim mantemos todas as regras de operação dentro do nosso programa. É claro que esse é um posicionamento particular, a função HasName existe para ser usada, então fique à vontade para fazer uso da mesma caso ela atenda ao seu cenário.

A função HasTranslation recebe uma function que contém uma lista de SqlExpression, essa lista é uma representação dos parâmetros que foram enviados para a função, e retorna um SqlExpression que será a função a ser executada. Sim, os tipos de entrada e saídas são iguais, isso porque o parâmetro da sua função pode ser o resultado de uma outra função traduzida para o banco.

A operação RowNumber em que estamos trabalhando possui a classe RowNumberExpression que facilita sua implementação e é herdeira de SqlExpression. Essa classe recebe como parâmetro uma lista opcional com os campos da base a serem usados como partição de agrupamento para a enumeração das linhas, uma lista obrigatória da classe OrderingExpression que contém o nome da coluna a ser usada na ordenação e a direção de ordenação, e o tipo de retorno esperado.

modelBuilder.HasDbFunction(method)
    .HasTranslation(args =>
        new RowNumberExpression(
            null,
            new List<OrderingExpression>()
            {
            },
            new LongTypeMapping("long", DbType.Int64)));

Os parâmetros da função RowNumber que criamos em nossa classe EfCoreCustomFunctions são um pouco diferentes do que é esperado, então vamos manipulá-los um pouco. Primeiro vamos verificar se o primeiro parâmetro que enviamos é do tipo SqlConstantExpression, qualquer constante enviada geraria o mesmo resultado, então eu prefiro testar essa condição, e caso ela seja verdadeira, passar o valor null como parâmetro.

modelBuilder.HasDbFunction(method)
    .HasTranslation(args =>
        new RowNumberExpression(
            args.First() is SqlConstantExpression
                ? null
                : args.Take(1).ToList(),
            new List<OrderingExpression>()
            {
            },
            new LongTypeMapping("long", DbType.Int64)));

Em seguida vamos criar um item na lista passada como segundo parâmetro, esse item vai ter os outros dois campos que recebemos em nossa função de ordenação personalizada. Observe que dada a natureza do tipo do objeto args, precisamos primeiro converter o terceiro parâmetro recebido para SqlConstantExpression para só então conseguir convertê-lo para booleano.

modelBuilder.HasDbFunction(method)
    .HasTranslation(args =>
        new RowNumberExpression(
            args.First() is SqlConstantExpression
                ? null
                : args.Take(1).ToList(),
            new List<OrderingExpression>()
            {
                new OrderingExpression(
                    args.Skip(1).First(),
                    ((bool)((SqlConstantExpression)args.Skip(2).First()).Value))
            },
            new LongTypeMapping("long", DbType.Int64)));

O código final dessa classe deve se parecer com o seguinte:

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;

namespace MyProject.EntityFrameworkCore
{
    public static class RowNumberFunction
    {
        public static ModelBuilder AddRowNumberFunction(this ModelBuilder modelBuilder)
        {
            var method = typeof(EfCoreCustomFunctions)
                .GetMethod(
                    nameof(EfCoreCustomFunctions.RowNumber),
                    new[] { typeof(long?), typeof(long), typeof(bool) });

            modelBuilder.HasDbFunction(method)
                .HasTranslation(args =>
                    new RowNumberExpression(
                        args.First() is SqlConstantExpression
                            ? null
                            : args.Take(1).ToList(),
                        new List<OrderingExpression>()
                        {
                            new OrderingExpression(
                                args.Skip(1).First(),
                                ((bool)((SqlConstantExpression)args.Skip(2).First()).Value))
                        },
                        new LongTypeMapping("long", DbType.Int64))
                    );

            return modelBuilder;
        }
    }
}

Agora tudo que temos que fazer é chamar a construção desse método dentro do ModelBuilder, para que quando tentarmos usá-lo, ele saiba exatamente o que fazer. Para isso, vamos criar uma classe chamada ModelFunctionsExtensions dentro da pasta Functions, vamos usar esse arquivo para chamar todas as funções customizadas que criaremos no futuro.

using Microsoft.EntityFrameworkCore;

namespace MyProject.EntityFrameworkCore
{
    public static class ModelFunctionsExtensions
    {
        public static ModelBuilder AddCustomFunctions(this ModelBuilder modelBuilder)
        {
            modelBuilder.AddRowNumberFunction();

            return modelBuilder;
        }
    }
}

Agora no local onde você cria o seu contexto, tudo que você tem a fazer é incluir a seguinte linha builder.AddCustomFunctions(); e todas as configurações estarão prontas. É claro que a variável builder é uma instância de ModelBuilder.

Agora vamos ver como podemos alterar o nosso código de exemplo para usar a função que acabamos de criar. Primeiro vamos acessar os dados diretamente do contexto, e na atribuição da propriedade Position, vamos chamar nossa função RowNumber.

var playerDtoList = playerList
    .OrderByDescending(player => player.Score)
    .Select(player => new PlayerDto()
    {
        Name = player.Name,
        Score = player.Score,
        Position = EfCoreCustomFunctions.RowNumber(null, player.Score, false)
    })
    .ToList();

Pronto! Esse código será capaz de ser traduzido pelo EF Core, e a posição do player será calculada diretamente no banco de dados. Veja um exemplo de como essa query se parecerá quando for executada no banco de dados.

SELECT Name, Score, ROW_NUMBER() OVER(ORDER BY Score DESC) as Position
FROM Playes
ORDER BY Score DESC

Que tal olharmos rapidamente mais um exemplo de função, mas dessa vez um exemplo que use a classe SqlFunctionExpression? Essa classe recebe como primeiro parâmetro o nome da função a ser executado, os argumentos a serem transmitidos, um indicador se o retorno pode ser nulo, e uma lista com o mesmo número de argumentos passados que representa a possibilidade de os parâmetros enviados serem nulos, um parâmetro do tipo System.Type e outro RelationalTypeMapping respectivamente, ambos indicando o tipo de retorno da função.

Vamos criar um método que chama a função Replicate do banco de dados, começamos incluindo a função Replicate à nossa classe EfCoreCustomFunctions.

using System;

namespace MyProject.EntityFrameworkCore
{
    public static class EfCoreCustomFunctions
    {
        public static double RowNumber(long? partition, long ordering, bool ascending)
            => throw new NotSupportedException();

        public static string Replicate(string replicate, int length)
            => throw new NotSupportedException();
    }
}

Agora criamos o nosso arquivo ReplicateFunction e criamos um conteúdo muito semelhante ao exemplo anterior, mas agora usando SqlFunctionExpression.

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;

namespace MyProject.EntityFrameworkCore
{
    public static class ReplicateFunction
    {
        public static ModelBuilder AddReplicateFunction(this ModelBuilder modelBuilder)
        {
            var method = typeof(EfCoreCustomFunctions)
                .GetMethod(
                    nameof(EfCoreCustomFunctions.Replicate),
                    new[] { typeof(string), typeof(int) });

            modelBuilder.HasDbFunction(method)
                .HasTranslation(args =>
                    new SqlFunctionExpression(
                        "REPLICATE",
                        args,
                        false,
                        args.Select(x => false),
                        typeof(string),
                        new StringTypeMapping("string", DbType.String))
                    );

            return modelBuilder;
        }
    }
}

Agora basta incluir a linha modelBuilder.AddReplicateFunction(); na função AddCustomFunctions do arquivo ModelFunctionsExtensions e nossa função já está pronta para ser usada.

Nota do autor

Conhecer recursos como esses a respeito da ferramenta com a qual trabalhamos é muito importante, e nos permite fazer coisas incríveis em nossas aplicações. Espero que esse artigo o tenha ajudado a resolver o que você precisava, ou ao menos incluído mais uma ferramenta em sua caixa de ferramentas para lidar com o complexo e maravilhoso mundo da programação.

Abraço!