Mapeamento de função definido pelo usuário
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.
Como mapear 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 CommentedPostCountForBlog definida 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 à função definida pelo usuário na configuração do modelo:
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
.HasName("CommentedPostCountForBlog");
Por padrão, o EF Core tenta mapear a função CLR para uma função definida pelo usuário com o mesmo nome. Se os nomes forem diferentes, podemos usar HasName para fornecer o nome correto para a função definida pelo usuário para a qual desejamos mapear.
Agora, executar a seguinte consulta:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Produzirá esse SQL:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Como mapear um método para um SQL personalizado
O EF Core também permite funções definidas pelo usuário que são convertidas em um SQL específico. A expressão SQL é fornecida usando o método HasTranslation durante a configuração de função definida pelo usuário.
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), 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));
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);
Isso produz o seguinte SQL:
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Configurando a nulidade da função definida pelo usuário com base em seus argumentos
Se a função definida pelo usuário só puder retornar null quando um ou mais de seus argumentos forem null, o EFCore fornecerá uma maneira de especificar isso, resultando em UM SQL com mais desempenho. Isso pode ser feito adicionando uma chamada PropagatesNullability() à configuração do modelo de parâmetros de função relevante.
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), 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();
});
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 esse 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
Essa otimização só deve ser usada se a função só puder retornar null quando seus parâmetros forem null.
Como maperar 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 o mapeamento está abaixo:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));
Observação
Uma função que pode ser consultada deve ser mapeada para uma função com valor de tabela e não pode fazer uso de HasTranslation.
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]
