Usando WHERE IN de SQL en Entity Framework (y Linq) con C#

A veces necesitas hacer una consulta basándote en datos que no estarás seguro tener porque simplemente es tu usuario quien los eligirá y pues, no eres adivino ¿verdad?

Vamos a basarnos en la base de datos de Northwind para el siguiente ejemplo (si no la tienes, checa el video de como obtener las bases de datos de pruebas Northwind y pubs). En esta base de datos tenemos Proveedores (Suppliers) que ofrecen Productos (Products). ¿Qué pasaría si necesitaramos saber los productos de un proveedor en específico? Si ese proveedor específico fuera Exotic Liquids el código Transact-SQL sería algo tan simple como esto:

SELECT *
FROM   products
WHERE  supplierid = (SELECT supplierid
                     FROM   suppliers
                     WHERE  companyname = ‘Exotic Liquids’) 

Ya se que también lo podemos poner con INNER JOIN pero estoy tratando de demostrar un punto, aguarda un poco.

Si la interfaz gráfica lo permite, y si el usuario quiere saber productos de más de un proveedor podemos hacer algo igual de sencillo usando WHERE IN. Imagínate un ListBox donde el usuario selecciona más de un proveedor de cual escoger productos, o una caja de texto con autocompletado. En este caso estamos obteniendo los productos de los proveedores que empiezan con la letra E:

SELECT *
FROM   products
WHERE  supplierid IN (SELECT supplierid
                      FROM   suppliers
                      WHERE  companyname LIKE ‘E%’) 

Donde usamos un campo de la tabla para que encuentre todos los valores que cumplan lo que tenemos entre paréntesis. Básico ¿no? pero ¿como hacemos para llevar esta sentencia SQL a Entity Framework?

Si estás usando Entity Framework 4.0, es muy similar a Linq2SQL, donde puedes usar el método Contains de un arreglo y podrás usar los IDs que ocupas para hacer el filtrado de elementos:

  //Si usamos Linq2SQL usar
  //NorthwindDataContext nc = new NorthwindDataContext();
  NorthwindEntities ne = new NorthwindEntities();

  int[] supplierIDs = { 1, 27 };

  var products = from p in ne.Products
          where (supplierIDs.Contains((int)p.SupplierID))
          select p;


Aquí dos cosas que aclarar, primero, se hace un cast al tipo de dato int para asegurar que los SupplierID coincidan. La otra es que habíamos quedado que no podemos saber cuales proveedores pudiera escojer el usuario, aunque aquí se forzó al proveedor con supplierID 1 y 27 (que son los de Exotic Liquids y Escargots Nouveaux, sí, ambos empiezan con E).

Entonces, ¿cómo hacerlo más dinámico usando Linq to Entities o Linq2SQL? Vaciamos en una variable la consulta, pero exclusivamente con las llaves primarias que nos interesan, y ese resultado lo convertimos a un arreglo, que sería el que finalmente usamos para la consulta final:

  //Si usamos Linq2SQL usar
  //NorthwindDataContext nc = new NorthwindDataContext();
  NorthwindEntities ne = new NorthwindEntities();

  var suppliers = from s in ne.Suppliers
          where (s.CompanyName.StartsWith("E"))
          select s.SupplierID;

  int[] supplierIDs = suppliers.ToArray();

  var products = from p in ne.Products
          where (supplierIDs.Contains((int)p.SupplierID))
          select p;

La clave aquí es usar en la consulta solo el campo que nos interesa (SupplierID) y convertir el resultado en arreglo para poder pasarlo a la segunda consulta ya que con el Contains lo tendremos resuelto. El ejemplo es con proveedores cuyo nombre empiece con E, pero puedes hacerlo aún más dinámico obteniendo ese dato de una caja de texto, un dropdownlist o lo que se te ocurra.

Consideraciones:

En Entity Framework 3.5 no podemos usar Contains, para hacerlo puedes usar el workaround propuesto por Shimmy en StackOverflow.

Puedes repasar como preparar tu ambiente de desarrollo con Entity Framework con el video Búsqueda en ADO.NET Entity Framework con C#.

La otra es que siempre son bienvenidos los comentarios para mejorar el código o ver que otras aplicaciones tendrían para esta manera de consultar datos que aunque muy común si trabajamos con Transact-SQL en Linq no es del todo clara.

Fuentes:

• Creating IN queries with Linq to SQL
• Converting query to string array

* El código fuente fue coloreado con Source Code Highlighter.