Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
O EF Core permite o uso de funções SQL definidas pelo usuário em consultas. Para fazer isso, as funções precisam 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 usuário é chamada em vez da função CLR para a qual ela foi mapeada.
Mapeando um método para uma função SQL
Para ilustrar como o mapeamento de funções definido pelo usuário funciona, 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 muitas postagens e cada postagem pode ter muitos comentários.
Em seguida, crie a função CommentedPostCountForBlogdefinida pelo usuário, que retorna a contagem de postagens com pelo menos um comentário para um determinado 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 essa função no EF Core, definimos o seguinte método CLR, que mapeamos para a função definida pelo usuário:
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
O corpo do método CLR não é importante. O método não será invocado no lado do cliente, a menos que o EF Core não possa traduzir seus argumentos. Se os argumentos puderem ser traduzidos, o EF Core se preocupa apenas com a assinatura do método.
Observação
No exemplo, o método é definido no DbContext mas também pode ser definido como um método estático dentro de outras classes.
Essa definição de função agora pode ser associada a uma função definida pelo usuário na configuração do modelo:
modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
.HasName("CommentedPostCountForBlog")
.HasSchema("dbo");
A sobrecarga com lambda HasDbFunction evita fazer manualmente a busca por MethodInfo. Os default valores de argumento são usados apenas para identificar o método; eles nunca são enviados para o banco de dados.
Por padrão, o EF Core mapeia o método CLR para uma função de banco de dados com o mesmo nome no esquema padrão. Use 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;
Produzirá este SQL:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Mapeando um método para uma função interna
O EF Core considera uma função mapeada como definida pelo usuário por padrão. Alguns bancos de dados distinguem funções internas e definidas pelo usuário ao gerar o SQL. Por exemplo, SQL Server requer que as funções definidas pelo usuário sejam qualificadas por esquema, mas as funções internas não são qualificadas para esquema.
Use IsBuiltIn para mapear um método CLR para uma função interna:
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)]
Mapeando uma função usando DbFunctionAttribute
Em vez de registrar uma função em OnModelCreating, um método estático declarado em DbContext pode ser mapeado diretamente ao aplicar DbFunctionAttribute. As propriedades IsNullable, Schema, Name e IsBuiltIn do atributo configuram as respectivas características da função de banco de dados; essas são as mesmas características configuradas pelos métodos de API fluente HasName, HasSchema, IsBuiltIn e IsNullable ao usar HasDbFunction. Os métodos atribuídos no contexto são descobertos e registrados automaticamente; métodos atribuídos em outras classes ainda devem ser registrados com HasDbFunction. Chame HasDbFunction para um método registrado automaticamente somente quando um builder for necessário para uma configuração fluente adicional, como no exemplo de tipo de armazenamento abaixo.
Por exemplo, o método a seguir usa DbFunctionAttribute para mapear a função interna JSON_VALUE do SQL Server. Como IsBuiltIn é true, o EF Core gera o nome da função sem esquema.
[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
=> throw new NotSupportedException();
Configurando tipos de repositório
Use HasStoreType para configurar o tipo de repositório de retorno de uma função e HasStoreType para configurar o tipo de repositório de um parâmetro. Isso é particularmente útil quando o tipo de parâmetro CLR não tem nenhum mapeamento de banco de dados nativo.
Neste exemplo, JsonEntity.Metadata é um dicionário armazenado como nvarchar(max) por meio de um conversor de valor. O json parâmetro de função tem o mesmo tipo de repositório, enquanto o resultado usa o nvarchar(4000) tipo retornado 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)");
Em seguida, a função pode 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 valor é extraído da expressão passada como o argumento da função. Portanto, esse padrão funciona para uma propriedade mapeada, como JsonEntity.Metadata, mas configurar o tipo de repositório de parâmetros não torna os valores arbitrários de dicionário traduzidos. Para usar um dicionário na memória, serialize-o e passe a cadeia de caracteres resultante para um método mapeado separadamente cujo parâmetro CLR é string.
Mapeando 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 banco de dados. A expressão SQL é fornecida usando HasTranslation durante a configuração da função.
No exemplo a seguir, criaremos 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 poderá ser usada na consulta. Em vez de chamar a função de banco de dados, o EF Core converterá o corpo do método diretamente no SQL com base na árvore de expressão 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ão SQL, não com o texto SQL. A tradução deve construir objetos SqlExpression válidos com os mapeamentos de tipos corretos, a capacidade de aceitar valor nulo e a propagação da capacidade de aceitar valor nulo dos argumentos. Metadados incorretos podem produzir resultados de consulta sql inválidos ou incorretos, e os tipos de expressão usados por uma tradução podem ser específicos para um provedor de banco de dados. Use esta API de baixo nível somente após entender a árvore de expressão SQL do provedor; prefira um mapeamento de função regular ou uma tradução existente do provedor, quando possível.
Configurando a nulidade da função definida pelo usuário com base em 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 isso chamando PropagatesNullability com os parâmetros relevantes. Para obter mais informações sobre como o EF Core compensa a lógica de três valores do SQL, consulte a semântica nula de consulta.
Para ilustrar isso, defina a função de usuário ConcatStrings:
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 são mapeados 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 maneira padrão. A segunda função é configurada para aproveitar a otimização de propagação de nulidade, fornecendo mais informações sobre como a função se comporta em relação a 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 reavaliar a função em si para testar sua nulidade.
Observação
Configure apenas a propagação de nulidade quando a função puder retornar null somente porque um ou mais dos parâmetros configurados são null.
Mapeando uma função que pode ser consultada para uma função com valor de tabela
O EF Core também dá suporte ao mapeamento para uma função com valor de tabela usando um método CLR definido pelo usuário que retorna um IQueryable dos tipos de entidade, permitindo que o EF Core mapeie TVFs com parâmetros. O processo é semelhante ao mapeamento de uma função escalar definida pelo usuário para uma função SQL: precisamos de um TVF no banco de dados, uma função CLR usada nas consultas LINQ e um mapeamento entre os dois.
Por exemplo, usaremos uma função com valor de tabela que retorna todas as postagens com pelo menos um comentário que atenda a um determinado limite "Curtir":
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));
Dica
A chamada FromExpression no corpo da função CLR permite que a função seja usada em vez de um DbSet regular.
E abaixo está o mapeamento:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));
Observação
Uma função que pode ser consultada deve ser mapeada para uma função com valor de tabela.
HasTranslation dá suporte apenas a funções escalares e não pode ser usado para uma função com valor 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]
Colaborar conosco no GitHub
A fonte deste conteúdo pode ser encontrada no GitHub, onde você também pode criar e revisar problemas e solicitações de pull. Para obter mais informações, confira o nosso guia para colaboradores.
