Executando funções personalizadas no banco de dados usando SqlExpression no .Net
Tags: csharp, programming
Published: 2023-05-06 20:26:24

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

```c#
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.

```c#
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*.

```c#
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.

```c#
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.

```c#
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.

```c#
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.

```c#
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*.

```c#
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:

```c#
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.

```c#
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*.

```c#
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.

```sql
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*.

```c#
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*.

```c#
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!



## Fontes e leituras complementares

[https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping](https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping)

[https://www.allhandsontech.com/data-professional/entityframework/entity-framework-core-advanced-mapping/](https://www.allhandsontech.com/data-professional/entityframework/entity-framework-core-advanced-mapping/)

[https://khalidabuhakmeh.com/add-custom-database-functions-for-entity-framework-core](https://khalidabuhakmeh.com/add-custom-database-functions-for-entity-framework-core)

[https://stackoverflow.com/questions/56261258/mixing-ef-core-convensions-and-dbfunction-for-a-jobject-property](https://stackoverflow.com/questions/56261258/mixing-ef-core-convensions-and-dbfunction-for-a-jobject-property)

[https://medium.com/@pawel.gerr/entity-framework-core-custom-functions-using-hasdbfunction-191dd57e4967](https://medium.com/@pawel.gerr/entity-framework-core-custom-functions-using-hasdbfunction-191dd57e4967)

[https://medium.com/@pawel.gerr/entity-framework-core-custom-functions-using-imethodcalltranslator-f8209ae6f95b](https://medium.com/@pawel.gerr/entity-framework-core-custom-functions-using-imethodcalltranslator-f8209ae6f95b)