Consultas SQL sin formatoRaw SQL Queries

Entity Framework Core le permite descender hasta las consultas SQL sin formato cuando trabaja con una base de datos relacional.Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Las consultas SQL sin formato son útiles si la consulta que quiere no se puede expresar mediante LINQ.Raw SQL queries are useful if the query you want can't be expressed using LINQ. Las consultas SQL sin formato también se utilizan si el uso de una consulta LINQ genera una consulta SQL ineficaz.Raw SQL queries are also used if using a LINQ query is resulting in an inefficient SQL query. Las consultas SQL sin formato pueden devolver tipos de entidad normales o tipos de entidad sin clave que forman parte del modelo.Raw SQL queries can return regular entity types or keyless entity types that are part of your model.

Sugerencia

Puede ver un ejemplo de este artículo en GitHub.You can view this article's sample on GitHub.

Consultas SQL básicas sin formatoBasic raw SQL queries

Puede usar el método de extensión FromSqlRaw para empezar una consulta LINQ basada en una consulta SQL sin formato.You can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query. FromSqlRaw solo se puede usar en raíces de consulta, es decir, directamente en DbSet<>.FromSqlRaw can only be used on query roots, that is directly on the DbSet<>.

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

Las consultas SQL sin formato se pueden usar para ejecutar un procedimiento almacenado.Raw SQL queries can be used to execute a stored procedure.

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

Pasar parámetrosPassing parameters

Advertencia

Use siempre la parametrización para las consultas SQL sin formatoAlways use parameterization for raw SQL queries

Al indicar cualquier valor proporcionado por el usuario en una consulta SQL sin formato, debe tener cuidado para evitar ataques por inyección de código SQL.When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. Además de validar que dichos valores no contienen caracteres no válidos, use siempre la parametrización que envía los valores separados del texto SQL.In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.

En concreto, no pase nunca a FromSqlRaw o ExecuteSqlRaw una cadena concatenada o interpolada ($"") con valores proporcionados por el usuario sin validar.In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. Los métodos FromSqlInterpolated y ExecuteSqlInterpolated permiten usar la sintaxis de interpolación de cadenas de manera que se protege frente a los ataques por inyección de código SQL.The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

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.The following example passes a single parameter to a stored procedure by including a parameter placeholder in the SQL query string and providing an additional argument. Aunque esta sintaxis se pueda parecer a la de String.Format, 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}.While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

FromSqlInterpolated es similar a FromSqlRaw, pero permite usar la sintaxis de interpolación de cadenas.FromSqlInterpolated is similar to FromSqlRaw but allows you to use string interpolation syntax. Al igual que FromSqlRaw, FromSqlInterpolated solo se puede usar en raíces de consulta.Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots. Como en el ejemplo anterior, el valor se convierte a DbParameter y no es vulnerable a la inyección de código SQL.As with the previous example, the value is converted to a DbParameter and isn't vulnerable to SQL injection.

Nota

Antes de la versión 3.0, FromSqlRaw y FromSqlInterpolated eran dos sobrecargas denominadas FromSql.Prior to version 3.0, FromSqlRaw and FromSqlInterpolated were two overloads named FromSql. Para obtener más información, vea la sección sobre versiones anteriores.For more information, see the previous versions section.

var user = "johndoe";

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

También puede construir un elemento DbParameter y suministrarlo como un valor de parámetro.You can also construct a DbParameter and supply it as a parameter value. Dado que se usa un marcador de posición de parámetro SQL normal, en lugar de un marcador de posición de cadena, FromSqlRaw se puede usar de forma segura:Since a regular SQL parameter placeholder is used, rather than a string placeholder, FromSqlRaw can be safely used:

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

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

FromSqlRaw permite usar parámetros con nombre en la cadena de consulta SQL, lo que resulta útil cuando un procedimiento almacenado tiene parámetros opcionales:FromSqlRaw allows you to use named parameters in the SQL query string, which is useful when a stored procedure has optional parameters:

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

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser=@user", user)
    .ToList();

Nota

Orden de los parámetros Entity Framework Core pasa parámetros basados en el orden de la matriz de SqlParameter[].Parameter Ordering Entity Framework Core passes parameters based on the order of the SqlParameter[] array. Al pasar varios SqlParameter, el orden de la cadena SQL debe coincidir con el orden de los parámetros en la definición del procedimiento almacenado.When passing multiple SqlParameters, the ordering in the SQL string must match the order of the parameters in the stored procedure's definition. Si no lo hace, al ejecutar el procedimiento pueden producirse excepciones de conversión de tipos o un comportamiento inesperado.Failure to do this may result in type conversion exceptions and/or unexpected behavior when the procedure is executed.

Redacción con LINQComposing with LINQ

Puede redactar sobre la consulta SQL sin formato inicial mediante operadores de LINQ.You can compose on top of the initial raw SQL query using LINQ operators. EF Core la tratará como una subconsulta y redactará sobre ella en la base de datos.EF Core will treat it as subquery and compose over it in the database. En el ejemplo siguiente se usa una consulta SQL sin formato que realiza una selección en una función con valores de tabla (TVF).The following example uses a raw SQL query that selects from a Table-Valued Function (TVF). Y después se redacta sobre ella con LINQ para realizar el filtrado y la ordenación.And then composes on it using LINQ to do filtering and sorting.

var searchTerm = "Lorem ipsum";

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

La consulta anterior genera el código SQL siguiente:Above query generates following SQL:

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 método Include puede usarse para incluir datos relacionados, igual que cualquier otra consulta LINQ:The Include method can be used to include related data, just like with any other LINQ query:

var searchTerm = "Lorem ipsum";

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

La redacción con LINQ requiere que la consulta SQL sin procesar se pueda redactar, ya que EF Core tratará el código SQL proporcionado como una subconsulta.Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. Las consultas SQL que se pueden redactar empiezan con la palabra clave SELECT.SQL queries that can be composed on begin with the SELECT keyword. Es más, el código SQL que se pasa no debe contener ningún carácter ni opción que no sea válido en una subconsulta, como los siguientes:Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

  • Un punto y coma finalA trailing semicolon
  • En SQL Server, una sugerencia en el nivel de consulta final (por ejemplo, OPTION (HASH JOIN))On SQL Server, a trailing query-level hint (for example, 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 SELECTOn SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

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.SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. Use el método AsEnumerable o AsAsyncEnumerable justo después de los métodos FromSqlRaw o FromSqlInterpolated para asegurarse de que EF Core no intente redactar sobre un procedimiento almacenado.Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

Seguimiento de cambiosChange Tracking

Las consultas que usan los métodos FromSqlRaw o FromSqlInterpolated siguen las mismas reglas de seguimiento de cambios que las demás consultas LINQ en EF Core.Queries that use the FromSqlRaw or FromSqlInterpolated methods follow the exact same change tracking rules as any other LINQ query in EF Core. Por ejemplo, si la consulta proyecta tipos de entidad, se realizará un seguimiento de los resultados de forma predeterminada.For example, if the query projects entity types, the results will be tracked by default.

En el ejemplo siguiente se usa una consulta SQL sin formato 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:The following example uses a raw SQL query that selects from a Table-Valued Function (TVF), then disables change tracking with the call to AsNoTracking:

var searchTerm = "Lorem ipsum";

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

LimitacionesLimitations

Existen algunas limitaciones que debe considerar al usar las consultas SQL sin formato:There are a few limitations to be aware of when using raw SQL queries:

  • La consulta SQL debe devolver datos para todas las propiedades del tipo de entidad.The SQL query must return data for all properties of the entity type.
  • Los nombres de las columnas del conjunto de resultados deben coincidir con los nombres de las columnas a los que se asignan las propiedades.The column names in the result set must match the column names that properties are mapped to. Tenga en cuenta que este comportamiento es diferente al de EF6.Note this behavior is different from EF6. En EF6 se omitía la asignación de propiedades y columnas para las consultas SQL sin formato, y los nombres de las columnas del conjunto de resultados tenían que coincidir con los nombres de las propiedades.EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.
  • La consulta SQL no puede contener datos relacionados.The SQL query can't contain related data. 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).However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

Versiones anterioresPrevious versions

EF Core 2.2 y las versiones anteriores tenían dos sobrecargas de método denominadas FromSql, que se comportaban de la misma manera que las sobrecargas FromSqlRaw y FromSqlInterpolated más recientes.EF Core version 2.2 and earlier had two overloads of method named FromSql, which behaved in the same way as the newer FromSqlRaw and FromSqlInterpolated. Resultaba sencillo llamar de forma accidental al método de cadena sin formato cuando la intención era llamar al método de cadena interpolada y viceversa.It was easy to accidentally call the raw string method when the intent was to call the interpolated string method, and the other way around. La llamada accidental a la sobrecarga incorrecta podría generar como resultado consultas que no se parametrizaban cuando debían.Calling wrong overload accidentally could result in queries not being parameterized when they should have been.