sql server - linked - Usar una cláusula IN con ExecuteQuery de LINQ-to-SQL

openquery sql server

Question

LINQ to SQL hizo un trabajo horrible traduciendo una de mis consultas, así que la reescribí a mano. El problema es que la reescritura implica necesariamente una cláusula IN , y no puedo por mi propia cuenta averiguar cómo pasar una colección a ExecuteQuery para ese fin. Lo único que se me ocurre, lo que he sugerido aquí, es usar string.Format en toda la cadena de consulta para abarcarlo, pero eso evitará que la consulta termine en el caché de consultas.

¿Cuál es la forma correcta de hacer esto?

NOTA : Tenga en cuenta que estoy usando SQL sin formato pasado a ExecuteQuery . Lo dije en la primera oración. No es útil decirme que use Contains , a menos que sepa una forma de mezclar Contains con SQL sin formato.

Answer 1

Parámetros con valores de tabla

En Cheezburger.com, a menudo necesitamos pasar una lista de AssetIDs o UserIDs a un procedimiento almacenado o una consulta de base de datos.

La mala manera: SQL dinámico

Una forma de pasar esta lista era usar SQL dinámico.

 IEnumerable<long> assetIDs = GetAssetIDs();
 var myQuery = "SELECT Name FROM Asset WHERE AssetID IN (" + assetIDs.Join(",") + ")";
 return Config.GetDatabase().ExecEnumerableSql(dr=>dr.GetString("Name"), myQuery);

Esto es algo muy malo de hacer:

1. El SQL dinámico ofrece a los atacantes una debilidad al facilitar los ataques de inyección de SQL.
   Como usualmente estamos concatenando números juntos, esto es altamente improbable, pero si comienzas a concatenar cadenas, todo lo que hace falta es que un usuario escriba '';DROP TABLE Asset;SELECT '' y nuestro sitio está muerto.
2. Los procedimientos almacenados no pueden tener SQL dinámico, por lo que la consulta debe almacenarse en código en lugar de en el esquema DB.
3. Cada vez que ejecutamos esta consulta, el plan de consulta debe ser recalculado. Esto puede ser muy costoso para consultas complicadas.

Sin embargo, tiene la ventaja de que no es necesaria una descodificación adicional en el lado de la base de datos, ya que el analizador de consultas encuentra los AssetID.

La buena manera: Parámetros con valores de tabla

SQL Server 2008 agrega una nueva capacidad: los usuarios pueden definir un tipo de base de datos con valores de tabla. La mayoría de los otros tipos son escalares (solo devuelven un valor), pero los tipos con valores de tabla pueden contener múltiples valores, siempre que los valores sean tabulares.

Hemos definido tres tipos: varchar_array , int_array y bigint_array .

CREATE TYPE bigint_array AS TABLE (Id bigint NOT NULL PRIMARY KEY)

Tanto los procedimientos almacenados como las consultas SQL definidas mediante programación pueden usar estos tipos de valores de tabla.

  IEnumerable<long> assetIDs = GetAssetIDs();
  return Config.GetDatabase().ExecEnumerableSql(dr=>dr.GetString("Name"),
      "SELECT Name FROM Asset WHERE AssetID IN (SELECT Id FROM @AssetIDs)", 
      new Parameter("@AssetIDs", assetIDs));

Ventajas

1. Se puede usar tanto en procedimientos almacenados como en SQL programático sin mucho esfuerzo
2. No es vulnerable a la inyección de SQL
3. Consultas estables y cacheables
4. No bloquea la tabla de esquema
5. No limitado a 8k de datos
6. Menos trabajo realizado por el servidor de BD y las aplicaciones Mine, ya que no hay concatenación ni descodificación de cadenas CSV.
7. Las estadísticas de "uso típico" pueden derivarse del analizador de consultas, que puede conducir a un rendimiento aún mejor.

Desventajas

1. Solo funciona en SQL Server 2008 y superior.
2. Los rumores de que los TVP están precompuestos en su totalidad antes de la ejecución de la consulta, lo que significa que el servidor puede rechazar grandes TVP. La investigación adicional de este rumor está en curso.

Otras lecturas

Este artículo es un gran recurso para aprender más sobre TVP.

Answer 2

Si no puede usar los parámetros con valores de tabla, esta opción es un poco más rápida que la opción xml y le permite mantenerse alejado de sql dinámico: pase la lista de valores como un parámetro de cadena y analice la cadena delimitada nuevamente. valores en su consulta. Consulte este artículo para obtener instrucciones sobre cómo realizar el análisis de manera eficiente.

Answer 3

Tengo una sospecha furtiva de que está en SQL Server 2005. Los parámetros con valores de tabla no se agregaron hasta 2008, pero aún puede usar el tipo de datos XML para pasar conjuntos entre el cliente y el servidor.

Answer 4

Esto funciona para SQL Server 2005 (y posterior):

create procedure IGetAListOfValues
   @Ids xml -- This will recevie a List of values
as
begin
    -- You can load then in a temp table or use it as a subquery:
    create table #Ids (Id int);
    INSERT INTO #Ids
    SELECT DISTINCT params.p.value(''.'',''int'') 
    FROM @Ids.nodes(''/params/p'') as params(p);
    ...
end

Debe invocar este procedimiento con un parámetro como este:

exec IGetAListOfValues
@Ids = ''<params> <p>1</p> <p>2</p> </params>'' -- xml parameter

La función de nodos usa una expresión xPath. En este caso, es /params/p y es por eso que el XML usa <params> como raíz y <p> como elemento.

La función de valor convierte el texto dentro de cada elemento p en int, pero puede usarlo fácilmente con otros tipos de datos. En esta muestra hay una DISTINCIÓN para evitar valores repetidos, pero, por supuesto, puede eliminarla según lo que desee lograr.

Tengo un método auxiliar (extensión) que convierte un IEnumerable<T> en una cadena que se parece a la que se muestra en el ejemplo de ejecución. Es fácil crear uno, y hacer que haga el trabajo por usted cada vez que lo necesite. (Debe probar el tipo de datos de T y convertir a una cadena adecuada que pueda analizarse en el lado del Servidor SQL). De esta forma, su código C # es más limpio y sus SP siguen el mismo patrón para recibir los parámetros (puede pasar tantas listas como sea necesario).

Una ventaja es que no necesita hacer nada especial en su base de datos para que funcione.

Por supuesto, no es necesario crear una tabla temporal como se hace en mi ejemplo, pero puede usar la consulta directamente como una subconsulta dentro de un predicado IN

    WHERE MyTableId IN (SELECT DISTINCT params.p.value(''.'',''int'') 
    FROM @Ids.nodes(''/params/p'') as params(p) )

Answer 5

No estoy 100% seguro de entender correctamente el problema, pero ExecuteQuery de LinqToSql tiene una sobrecarga de parámetros, y se supone que la consulta usará un formato similar a string.Format.

El uso de esta sobrecarga es seguro contra la inyección SQL, y detrás de las escenas, LinqToSql lo transporta para usar sp_executesql con parámetros.

Aquí hay un ejemplo:

string sql = "SELECT * FROM city WHERE city LIKE {0}";
db.ExecuteQuery(sql, "Lon%"); //Note that we don''t need the single quotes 

De esta forma, uno puede usar el beneficio de las consultas parametrizadas, incluso si usa sql dinámico.

Sin embargo, cuando se trata de usar IN con un número dinámico de parámetros, hay dos opciones:

1. Construya la cadena de forma dinámica y luego pase los valores como una matriz, como en:

   string sql = "SELECT * FROM city WHERE zip IN (";
   List<string> placeholders = new List<string>();
   for(int i = 0; i < zips.Length;i++)
   {
         placeholders.Add("{"+i.ToString()+"}");
   }
   sql += string.Join(",",placeholders.ToArray());
   sql += ")";
   db.ExecuteQuery(sql, zips.ToArray());

2. Podemos utilizar un enfoque más compacto mediante el uso de los métodos de extensión Linq, como en

   string sql = "SELECT * FROM city WHERE zip IN ("+
     string.Join("," , zips.Select(z => "{" + zips.IndexOf(f).ToString() + "}"))
   +")";
   db.ExecuteQuery(sql, zips.ToArray());