Nota
O acceso a esta páxina require autorización. Pode tentar iniciar sesión ou modificar os directorios.
O acceso a esta páxina require autorización. Pode tentar modificar os directorios.
EF Core permite usar funciones SQL definidas por el usuario en 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 ilustrar 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 entradas y cada entrada puede tener muchos comentarios.
A continuación, cree la función CommentedPostCountForBlogdefinida por el usuario , que devuelve el recuento de entradas con al menos un comentario para un blog determinado, basado en el 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 esta función en EF Core, definimos el siguiente método CLR, que se asigna 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. El método no se invocará en el lado cliente, a menos que EF Core no pueda traducir sus argumentos. Si los argumentos se pueden traducir, EF Core solo se preocupa por la firma del método.
Nota:
En el 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 la función definida por el usuario en la configuración del modelo:
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(ActivePostCountForBlog), [typeof(int)]))
.HasName("CommentedPostCountForBlog");
De forma predeterminada, EF Core intenta asignar la función CLR a una función definida por el usuario con el mismo nombre. Si los nombres difieren, podemos usar HasName para proporcionar el nombre correcto para la función definida por el usuario a la que queremos asignar.
Ahora, ejecutando la consulta siguiente:
var query1 = from b in context.Blogs
where context.ActivePostCountForBlog(b.BlogId) > 1
select b;
Generará este código 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 un SQL personalizado
EF Core también permite funciones definidas por el usuario que se convierten en un SQL específico. La expresión SQL se proporciona mediante el HasTranslation método durante la configuración de funciones definidas por el usuario.
En el ejemplo siguiente, crearemos una función que calcula la diferencia porcentual 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), [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));
Una vez que definamos 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 en función del árbol de expresiones SQL construido a partir de HasTranslation. La siguiente consulta LINQ:
var query2 = from p in context.Posts
select context.PercentageDifference(p.BlogId, 3);
Genera el siguiente código 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 la función definida 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, EFCore proporciona una manera de especificarlo, lo que da lugar a SQL más eficaz. Para ello, agregue una PropagatesNullability() llamada a la configuración del modelo de parámetros de función pertinentes.
Para ilustrar esto, defina la función ConcatStringsde usuario :
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 este último:
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 OnModelCreating del método) es la siguiente:
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();
});
La primera función se configura de la manera estándar. La segunda función está configurada para aprovechar la optimización de propagación de la nulabilidad, proporcionando más información sobre cómo se comporta la función en torno a los parámetros nulos.
Al emitir las siguientes 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");
Obtenemos 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 necesita volver a evaluar la propia función para probar su nulabilidad.
Nota:
Esta optimización solo 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 el mapeo a una función de valor de tabla mediante un método CLR definido por el usuario que devuelve un IQueryable de tipos de entidad, lo que permite a EF Core asignar funciones de valor de tabla (TVF) con parámetros. El proceso es similar a asignar una función escalar definida por el usuario a una función SQL: necesitamos un TVF en la base de datos, una función CLR que se usa en las consultas LINQ y una asignación entre los dos.
Por ejemplo, usaremos una función con valores de tabla que devuelve todas las publicaciones que tengan al menos un comentario que cumpla un umbral "Like" 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 FromExpression en el cuerpo de la función del CLR permite usar la función en lugar de un DbSet convencional.
Y a continuación se muestra la asignación:
modelBuilder.Entity<Post>().ToTable("Posts");
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(PostsWithPopularComments), [typeof(int)]));
Nota:
Una función consultable debe asignarse a una función con valores de tabla y no puede usar HasTranslation.
Cuando se asigna la función, la siguiente consulta:
var likeThreshold = 3;
var query5 = from p in context.PostsWithPopularComments(likeThreshold)
orderby p.Rating
select p;
Genera:
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
Colaborar con nós en GitHub
A orixe deste contido pódese atopar en GitHub, onde tamén podes crear e revisar problemas e extraer solicitudes. Para obter máis información, consulta a guía para colaboradores.
