Asignación de funciones definidas por el usuario
EF Core permite usar funciones SQL definidas por el usuario en las consultas. Para ello, las funciones deben asignarse a un método CLR durante la configuración del modelo. Al traducir la consulta LINQ a SQL, se llama a la función definida por el usuario en lugar de a la función CLR a la que se ha asignado.
Asignación de un método a una función SQL
Para mostrar cómo funciona la asignación de funciones definidas por el usuario, vamos a definir las siguientes 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; }
}
Y la siguiente configuración del modelo:
modelBuilder.Entity<Blog>()
.HasMany(b => b.Posts)
.WithOne(p => p.Blog);
modelBuilder.Entity<Post>()
.HasMany(p => p.Comments)
.WithOne(c => c.Post);
El blog puede tener muchas publicaciones, y cada publicación puede tener muchos comentarios.
Después, creamos la función definida por el usuario CommentedPostCountForBlog, que devuelve el recuento de publicaciones con al menos un comentario para un blog determinado, en función del Id del blog:
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 función en EF Core, definimos el siguiente método CLR y lo asignamos a la función definida por el usuario:
public int ActivePostCountForBlog(int blogId)
=> throw new NotSupportedException();
El cuerpo del método CLR no es importante. No se invocará el método del lado cliente a menos que EF Core no pueda traducir sus argumentos. Si los argumentos se pueden traducir, EF Core solo tiene en cuenta la firma del método.
Nota:
En este ejemplo, el método se define en DbContext, pero también se puede definir como un método estático dentro de otras clases.
Esta definición de función ahora se puede asociar a una función definida por el usuario en la configuración del modelo:
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), new[] { typeof(int) }))
.HasName("CommentedPostCountForBlog");
De forma predeterminada, EF Core intenta asignar la función CLR a una función con el mismo nombre definida por el usuario. Si los nombres difieren, se puede usar HasName para proporcionar el nombre correcto para la función definida por el usuario a la que queremos asignar el método.
Después, ejecutamos la siguiente consulta:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Se generará esta función SQL:
SELECT [b].[BlogId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
WHERE [dbo].[CommentedPostCountForBlog]([b].[BlogId]) > 1
Asignación de un método a una función SQL personalizada
EF Core también permite funciones definidas por el usuario que se convierten en una función SQL específica. La expresión SQL se proporciona mediante el método HasTranslation durante la configuración de la función definida por el usuario.
En el ejemplo siguiente, crearemos una función que calcula la diferencia de porcentaje entre dos enteros.
El método CLR es el siguiente:
public double PercentageDifference(double first, int second)
=> throw new NotSupportedException();
La definición de la función es la siguiente:
// 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));
Una vez definida la función, se puede usar en la consulta. En lugar de llamar a la función de base de datos, EF Core traducirá el cuerpo del método directamente a SQL basándose en el árbol de expresión SQL construido a partir del método HasTranslation. La siguiente consulta LINQ:
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Produce el siguiente SQL:
SELECT 100 * (ABS(CAST([p].[BlogId] AS float) - 3) / ((CAST([p].[BlogId] AS float) + 3) / 2))
FROM [Posts] AS [p]
Configuración de la nulabilidad de funciones definidas por el usuario en función de sus argumentos
Si la función definida por el usuario solo puede devolver null cuando uno o varios de sus argumentos son null, EF Core proporciona una manera de especificarlo, lo que da lugar a código SQL de mayor rendimiento. Para ello, se puede agregar una llamada a PropagatesNullability() a la configuración del modelo de parámetros de función pertinente.
Para ilustrarlo, defina la función de usuario ConcatStrings:
CREATE FUNCTION [dbo].[ConcatStrings] (@prm1 nvarchar(max), @prm2 nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN @prm1 + @prm2;
END
Y dos métodos CLR que se asignan a la función:
public string ConcatStrings(string prm1, string prm2)
=> throw new InvalidOperationException();
public string ConcatStringsOptimized(string prm1, string prm2)
=> throw new InvalidOperationException();
La configuración del modelo (dentro del método OnModelCreating) es la siguiente:
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();
});
La primera función se configura de la forma habitual. La segunda función se configura para aprovechar las ventajas de la optimización de la propagación de nulabilidad, lo que proporciona más información sobre cómo se comporta la función en relación a los parámetros NULL.
Al emitir las consultas siguientes:
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");
Se obtiene este código 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)
La segunda consulta no tiene que volver a evaluar la función para probar su nulabilidad.
Nota:
Esta optimización únicamente se debe usar si la función solo puede devolver null cuando sus parámetros son null.
Asignación de una función consultable a una función con valores de tabla
EF Core también admite la asignación a una función con valores de tabla mediante un método CLR definido por el usuario que devuelve un objeto IQueryable de tipos de entidad, lo que permite a EF Core asignar funciones con valores de tabla (TVF) con parámetros. El proceso es similar a la asignación de una función escalar definida por el usuario a una función SQL: necesitamos una TVF en la base de datos, una función CLR que se usa en las consultas LINQ y una asignación entre las dos.
Como ejemplo, usaremos una función con valores de tabla que devuelve todas las publicaciones que tienen al menos un comentario que cumple un umbral de "Me gusta" determinado:
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
)
La firma del método CLR es la siguiente:
public IQueryable<Post> PostsWithPopularComments(int likeThreshold)
=> FromExpression(() => PostsWithPopularComments(likeThreshold));
Sugerencia
La llamada a FromExpression en el cuerpo de la función CLR permite usar la función en lugar de un objeto DbSet normal.
A continuación se muestra la asignación:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), new[] { typeof(int) }));
Nota:
Una función consultable debe estar asignada a una función con valores de tabla y no puede usar HasTranslation.
Cuando se asigna la función, se ejecuta la siguiente consulta:
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
Esta consulta genera lo siguiente:
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
