Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los 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 una función definida por el usuario en la configuración del modelo:
modelBuilder.HasDbFunction(() => ActivePostCountForBlog(default))
.HasName("CommentedPostCountForBlog")
.HasSchema("dbo");
La sobrecarga de lambda de HasDbFunction evita localizar manualmente MethodInfo. Los default valores de argumento solo se usan para identificar el método; nunca se envían a la base de datos.
De forma predeterminada, EF Core asigna el método CLR a una función de base de datos con el mismo nombre en el esquema predeterminado. Use HasName y HasSchema cuando el nombre o el esquema difieren.
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 una función integrada
EF Core considera de forma predeterminada que una función mapeada está definida por el usuario. Algunas bases de datos distinguen las funciones integradas y definidas por el usuario al generar SQL. Por ejemplo, SQL Server requiere que las funciones definidas por el usuario estén calificadas por el esquema, pero las funciones integradas no están calificadas por el esquema.
Use IsBuiltIn para asignar un método CLR a una función integrada:
public static int IsDate(string value)
=> throw new NotSupportedException();
modelBuilder.HasDbFunction(typeof(BloggingContext).GetMethod(nameof(IsDate), [typeof(string)]))
.HasName("ISDATE")
.IsBuiltIn();
La IsBuiltIn propiedad proporciona la misma configuración cuando se usa un atributo :
[DbFunction(Name = "ISDATE", IsBuiltIn = true)]
Mapeo de una función con DbFunctionAttribute
En lugar de registrar una función en OnModelCreating, se puede asignar directamente un método estático declarado en DbContext aplicando DbFunctionAttribute. Las propiedades HasName, HasSchema, IsBuiltIn y IsNullable del atributo configuran las características correspondientes de la función de la base de datos; estas son las mismas características que configuran los métodos HasDbFunction, Name, IsNullable y IsBuiltIn de la API fluida al usar Schema. Los métodos con atributos en el contexto se detectan y registran automáticamente; Los métodos con atributos de otras clases deben registrarse con HasDbFunction. Llama a HasDbFunction para un método registrado automáticamente solo cuando se necesite un generador para una configuración fluida adicional, como en el ejemplo de tipo de almacenamiento que se muestra a continuación.
Por ejemplo, el siguiente método usa DbFunctionAttribute para asociar la función integrada JSON_VALUE de SQL Server. Dado que IsBuiltIn es true, EF Core emite el nombre de la función sin un esquema.
[DbFunction(Name = "JSON_VALUE", IsBuiltIn = true, IsNullable = true)]
public static string JsonValue(Dictionary<string, string> json, string path)
=> throw new NotSupportedException();
Configuración de tipos de almacén
Use HasStoreType para configurar el tipo de almacén devuelto de una función y HasStoreType para configurar el tipo de almacén de un parámetro. Esto resulta especialmente útil cuando el tipo de parámetro CLR no tiene ninguna asignación de base de datos nativa.
En este ejemplo, JsonEntity.Metadata es un diccionario almacenado como nvarchar(max) a través de un convertidor de valores. El parámetro de función json tiene el mismo tipo de almacén, mientras que el resultado usa el tipo nvarchar(4000) devuelto 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)");
A continuación, se puede usar la función con la propiedad convertida:
var jsonQuery = context.JsonEntities.Select(e => BloggingContext.JsonValue(e.Metadata, "$.Filter"));
SELECT JSON_VALUE([j].[Metadata], N'$.Filter')
FROM [JsonEntities] AS [j]
El convertidor de valores se toma de la expresión pasada como argumento de función. Por lo tanto, este patrón funciona para una propiedad mapeada como JsonEntity.Metadata, pero configurar el tipo de almacén de parámetros no hace que los valores arbitrarios de un diccionario sean traducibles. Para usar un diccionario en memoria, serialícelo y pase la cadena resultante a un método asignado por separado cuyo parámetro CLR es string.
Asignación de un método a un SQL personalizado
EF Core también permite traducir un método CLR directamente a una expresión SQL en lugar de una función de base de datos. La expresión SQL se proporciona mediante HasTranslation durante la configuración de la función.
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]
Caution
HasTranslation funciona con el árbol de expresiones SQL, no con texto SQL. La conversión debe construir objetos SqlExpression válidos con las asignaciones de tipos correctas, la nulidad y la propagación de la nulidad de los argumentos. Los metadatos incorrectos pueden generar resultados de consulta sql no válidos o incorrectos, y los tipos de expresión usados por una traducción pueden ser específicos de un proveedor de base de datos. Use esta API de bajo nivel solo después de comprender el árbol de expresiones SQL del proveedor; prefiere una asignación de funciones normal o una traducción de proveedor existente siempre que sea posible.
Configuración de la nulabilidad de la función definida por el usuario en función de sus argumentos
Si la nulabilidad se propaga desde un argumento de función, es decir, la función devuelve null cada vez que ese argumento es null, EF Core puede generar SQL más eficaz. Para configurarlo, llame a PropagatesNullability para los parámetros pertinentes. Para obtener más información sobre cómo EF Core compensa la lógica de tres valores de SQL, consulte Semántica nula de consulta.
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 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 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:
Configure la propagación de nulabilidad solo cuando la función pueda devolver null únicamente porque uno o más de los parámetros configurados 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 a FromExpression en el cuerpo de la función CLR permite usar la función en lugar de un objeto DbSet normal.
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.
HasTranslation solo admite funciones escalares y no se puede usar para una función con valores de tabla.
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;
Genera:
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [dbo].[PostsWithPopularComments](@likeThreshold) AS [p]
ORDER BY [p].[Rating]
Colaborar con nosotros en GitHub
El origen de este contenido se puede encontrar en GitHub, donde también puede crear y revisar problemas y solicitudes de incorporación de cambios. Para más información, consulte nuestra guía para colaboradores.
