Consultas SQL

  • Artículo

Entity Framework Core le permite descender hasta las consultas SQL cuando trabaja con una base de datos relacional. Las consultas SQL son útiles si la consulta que le interesa no se puede expresar mediante LINQ, o bien si una consulta LINQ hace que EF genere código SQL ineficaz. Las consultas SQL pueden devolver tipos de entidad normales o tipos de entidad sin clave que forman parte del modelo.

Sugerencia

Puede ver un ejemplo de este artículo en GitHub.

Consultas SQL básicas

Puede usar FromSql para iniciar una consulta LINQ basada en una consulta SQL:

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

Nota:

FromSql se incluyó por primera vez en EF Core 7.0. Si trabaja con versiones anteriores, use FromSqlInterpolated en su lugar.

Las consultas SQL se pueden usar para ejecutar un procedimiento almacenado que devuelve datos de entidad:

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Nota:

FromSql solo se puede usar directamente en un elemento DbSet. No se puede redactar sobre una consulta LINQ arbitraria.

Pasar parámetros

Advertencia

Preste mucha atención a la parametrización al usar consultas SQL.

Al indicar cualquier valor proporcionado por el usuario en una consulta SQL, debe tener cuidado para evitar ataques por inyección de código SQL. La inyección de código SQL se produce cuando un programa integra en una consulta SQL un valor de cadena proporcionado por el usuario que está diseñado para finalizar la cadena y realizar otra operación SQL malintencionada. Para obtener más información sobre la inyección de código SQL, consulte esta página.

Los métodos FromSql y FromSqlInterpolated están protegidos contra la inyección de código SQL y siempre integran los datos de parámetros como un parámetro SQL independiente. Aun así, el método FromSqlRaw puede ser vulnerable a ataques por inyección de código SQL si se usa incorrectamente. Consulte a continuación para más información.

En el ejemplo siguiente se pasa un parámetro único a un procedimiento almacenado. Para ello, se incluye un marcador de posición de parámetro en la cadena de consulta SQL y se proporciona un argumento adicional:

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Aunque esta sintaxis podría parecer una interpolación de cadenas de C# normal, el valor suministrado se encapsula en un elemento DbParameter y el nombre del parámetro generado se inserta donde se haya especificado el marcador de posición {0}. Esto hace que FromSql esté protegido contra ataques por inyección de código SQL y envía el valor de forma eficaz y correcta a la base de datos.

Al ejecutar procedimientos almacenados, puede ser útil usar parámetros con nombre en la cadena de consulta SQL, sobre todo cuando el procedimiento almacenado tiene parámetros opcionales:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

Si necesita más control sobre el parámetro de base de datos que se envía, también puede construir un elemento DbParameter y proporcionarlo como un valor de parámetro. Esto le permite establecer el tipo de base de datos preciso del parámetro, o bien facetas como su tamaño, precisión o longitud:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Nota:

Los parámetros que pase deben coincidir exactamente con la definición del procedimiento almacenado. Preste especial atención a la ordenación de los parámetros para no pasar por alto ni perder ninguno de ellos. También puede considerar la posibilidad de usar la notación de parámetros con nombre. Además, asegúrese de que los tipos de parámetro se correspondan y de que sus facetas (tamaño, precisión y escala) estén establecidas según sea necesario.

SQL dinámico y parámetros

Conviene usar FromSql y su parametrización siempre que sea posible. Aun así, hay ciertos escenarios en los que SQL debe estar agrupado dinámicamente y no se pueden usar parámetros de base de datos. Por ejemplo, supongamos que una variable de C# contiene el nombre de una propiedad por la que se va a filtrar. Puede resultar tentador usar una consulta SQL como la siguiente:

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

Este código no funciona, ya que las bases de datos no permiten parametrizar nombres de columna (ni ninguna otra parte del esquema).

En primer lugar, es importante tener en cuenta las implicaciones de construir dinámicamente una consulta, ya sea mediante SQL o de otro modo. Si acepta un nombre de columna de los usuarios, puede permitirles elegir una columna que no está indexada, lo que hará que la consulta se ejecute muy lentamente y sobrecargue la base de datos; o bien, puede permitirles elegir una columna que contenga datos que usted no quiera exponer. Excepto en escenarios realmente dinámicos, normalmente es mejor tener dos consultas para dos nombres de columna, en lugar de usar la parametrización para contraerlas en una sola consulta.

Si ha decidido que quiere construir dinámicamente su código SQL, tendrá que usar FromSqlRaw, que permite interpolar datos variables directamente en la cadena SQL, en lugar de usar un parámetro de base de datos:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

En el código anterior, el nombre de columna se inserta directamente en el código SQL mediante la interpolación de cadenas de C#. Es su responsabilidad asegurarse de que este valor de cadena es seguro y corregirlo si procede de un origen no seguro. Esto implica detectar caracteres especiales (como signos de punto y coma), comentarios y otras construcciones SQL, y escaparlos correctamente o rechazar dichas entradas.

Por otro lado, el valor de la columna se envía mediante un elemento DbParameter y, por tanto, está protegido contra la inyección de código SQL.

Advertencia

Tenga mucho cuidado al usar FromSqlRaw y asegúrese siempre de que los valores procedan de un origen seguro o se hayan corregido como corresponde. Los ataques por inyección de código SQL pueden tener consecuencias desastrosas para la aplicación.

Redacción con LINQ

Puede redactar sobre la consulta SQL inicial mediante operadores de LINQ; EF Core tratará el código SQL como una subconsulta y redactará sobre él en la base de datos. En el ejemplo siguiente se usa una consulta SQL que realiza una selección en una función con valores de tabla (TVF). Y después se redacta sobre ella con LINQ para realizar el filtrado y la ordenación.

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

La consulta anterior genera el código SQL siguiente:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

El operador Include puede usarse para cargar datos relacionados, igual que cualquier otra consulta LINQ:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

La redacción con LINQ requiere que la consulta SQL admita composición, ya que EF Core tratará el código SQL proporcionado como una subconsulta. Las consultas SQL que admiten composición suelen comenzar con la palabra clave SELECT y no pueden contener características SQL que no son válidas en una subconsulta, por ejemplo:

  • Un punto y coma final
  • En SQL Server, una sugerencia en el nivel de consulta final (por ejemplo, OPTION (HASH JOIN))
  • En SQL Server, una cláusula ORDER BY que no se usa con OFFSET 0 O BIEN TOP 100 PERCENT en la cláusula SELECT

SQL Server no permite la redacción sobre llamadas a procedimientos almacenados, por lo que cualquier intento de aplicar operadores de consulta adicionales a ese tipo de llamada producirá código SQL no válido. Use AsEnumerable o AsAsyncEnumerable justo después de FromSql o FromSqlRaw para asegurarse de que EF Core no intente redactar sobre un procedimiento almacenado.

Seguimiento de cambios

Las consultas que usan FromSql o FromSqlRaw siguen las mismas reglas de seguimiento de cambios que las demás consultas LINQ en EF Core. Por ejemplo, si la consulta proyecta tipos de entidad, se realiza un seguimiento de los resultados de forma predeterminada.

En el ejemplo siguiente se usa una consulta SQL que realiza una selección en una función con valores de tabla (TVF) y, después, deshabilita el seguimiento de cambios con la llamada a AsNoTracking:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

Consulta de tipos escalares (sin entidad)

Nota:

Esta característica se incluyó por primera vez en EF Core 7.0.

Aunque FromSql es útil para consultar entidades definidas en el modelo, SqlQuery permite consultar fácilmente tipos escalares sin entidad mediante SQL, sin necesidad de descender a API de acceso a datos de nivel inferior. Por ejemplo, la consulta siguiente captura todos los identificadores de la tabla Blogs:

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

También puede redactar operadores de LINQ sobre la consulta SQL. Aun así, dado que el código SQL se convierte en una subconsulta a cuya columna de salida debe hacer referencia el código SQL que EF agrega, debe asignar a la columna de salida el nombre Value. Por ejemplo, la consulta siguiente devuelve los identificadores que están por encima de la media de identificadores:

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql se puede usar con cualquier tipo escalar admitido por el proveedor de bases de datos. Si quiere usar un tipo que el proveedor de bases de datos no admite, puede usar la configuración anterior a la convención para definir una conversión de valor para él.

SqlQueryRaw permite la construcción dinámica de consultas SQL, al igual que FromSqlRaw en el caso de los tipos de entidad.

Ejecución de código SQL que no es de consulta

En algunos escenarios, puede ser necesario ejecutar código SQL que no devuelve ningún dato, normalmente para modificar los datos de la base de datos o para llamar a un procedimiento almacenado que no devuelve ningún conjunto de resultados. Esto se puede hacer mediante ExecuteSql:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Esto ejecuta el código SQL proporcionado y devuelve el número de filas modificadas. ExecuteSql protege contra la inyección de código SQL mediante la parametrización segura, al igual que FromSql, y ExecuteSqlRaw permite la construcción dinámica de consultas SQL, al igual que FromSqlRaw en el caso de las consultas.

Nota:

Antes de EF Core 7.0, a veces era necesario usar las API ExecuteSql para realizar una "actualización masiva" en la base de datos, como se indicó anteriormente; esto es mucho más eficaz que consultar todas las filas coincidentes y usar luego SaveChanges para modificarlas. En EF Core 7.0 se introdujeron ExecuteUpdate y ExecuteDelete, lo que hizo posible expresar operaciones de actualización masiva eficaces mediante LINQ. Se recomienda usar esas API siempre que sea posible, en lugar de ExecuteSql.

Limitaciones

Existen algunas limitaciones que debe tener en cuenta cuando se devuelven tipos de entidad a partir de consultas SQL:

  • La consulta SQL debe devolver datos para todas las propiedades del tipo de entidad.
  • Los nombres de las columnas del conjunto de resultados deben coincidir con los nombres de las columnas a los que se asignan las propiedades. Observe que este comportamiento es diferente al de EF6. EF6 omitía la asignación de propiedades a columnas en las consultas SQL, y los nombres de las columnas del conjunto de resultados tenían que coincidir con los nombres de las propiedades.
  • La consulta SQL no puede contener datos relacionados. Sin embargo, en muchos casos puede redactar sobre la consulta si usa el operador Include para devolver datos relacionados (consulte Inclusión de datos relacionados).