EntityFramework – contiene la consulta de la clave compuesta

dada una lista de identificadores, puedo consultar todas las filas relevantes de la siguiente manera:

context.Table.Where(q => listOfIds.Contains(q.Id)); 

¿Pero cómo logras la misma funcionalidad cuando la tabla tiene una clave compuesta?

Este es un problema desagradable para el que no conozco ninguna solución elegante.

Supongamos que tiene estas combinaciones de teclas y solo desea seleccionar las marcadas (*).

 Id1 Id2 --- --- 1 2 * 1 3 1 6 2 2 * 2 3 * ... (many more) 

¿Cómo hacer esto es una forma en que Entity Framework es feliz? Veamos algunas posibles soluciones y veamos si son buenas.

Solución 1: Join (o Contains ) con pares

La mejor solución sería crear una lista de los pares que desea, por ejemplo Tuples, ( List> ) y unir los datos de la base de datos con esta lista:

 from entity in db.Table // db is a DbContext join pair in Tuples on new { entity.Id1, entity.Id2 } equals new { Id1 = pair.Item1, Id2 = pair.Item2 } select entity 

En LINQ a los objetos esto sería perfecto, pero, lástima, EF lanzará una excepción como

No se puede crear un valor constante de tipo ‘System.Tuple`2 (…) En este contexto solo se admiten tipos primitivos o tipos de enumeración.

que es una forma bastante torpe de decirte que no puede traducir esta statement a SQL, porque Tuples no es una lista de valores primitivos (como int o string ). 1 . Por el mismo motivo, una instrucción similar que utiliza Contains (o cualquier otra instrucción LINQ) fallaría.

Solución 2: In-memory

Por supuesto, podríamos convertir el problema en LINQ simple a objetos como ese:

 from entity in db.Table.AsEnumerable() // fetch db.Table into memory first join pair Tuples on new { entity.Id1, entity.Id2 } equals new { Id1 = pair.Item1, Id2 = pair.Item2 } select entity 

Huelga decir que esta no es una buena solución. db.Table podría contener millones de registros.

Solución 3: dos instrucciones Contains

Entonces, ofrezcamos a EF dos listas de valores primitivos, [1,2] para Id1 y [2,3] para Id2 . No queremos utilizar join (ver nota al margen), así que usemos Contains :

 from entity in db.Table where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2) select entity 

¡Pero ahora los resultados también contienen la entidad {1,3} ! Bueno, por supuesto, esta entidad coincide perfectamente con los dos predicados. Pero tengamos en cuenta que nos estamos acercando. En lugar de atraer a millones de entidades a la memoria, ahora solo obtenemos cuatro de ellas.

Solución 4: Uno Contains valores calculados

La Solución 3 falló porque las dos declaraciones Contains separado no solo filtran las combinaciones de sus valores. ¿Qué sucede si primero creamos una lista de combinaciones e intentamos unir estas combinaciones? Sabemos por la solución 1 que esta lista debe contener valores primitivos. Por ejemplo:

 var computed = ids1.Zip(ids2, (i1,i2) => i1 * i2); // [2,6] 

y la statement LINQ:

 from entity in db.Table where computed.Contains(entity.Id1 * entity.Id2) select entity 

Hay algunos problemas con este enfoque. Primero, verás que esto también devuelve la entidad {1,6} . La función de combinación (a * b) no produce valores que identifiquen un par de forma única en la base de datos. Ahora podríamos crear una lista de cadenas como ["Id1=1,Id2=2","Id1=2,Id2=3]" y hacer

 from entity in db.Table where computed.Contains("Id1=" + entity.Id1 + "," + "Id2=" + entity.Id2) select entity 

(Esto funcionaría en EF6, no en versiones anteriores).

Esto se está poniendo bastante sucio. Pero un problema más importante es que esta solución no es sargable , lo que significa que omite los índices de bases de datos en Id1 e Id2 que podrían haberse utilizado de otra manera. Esto funcionará muy mal.

Solución 5: Lo mejor de 2 y 3

Entonces, la única solución viable que puedo pensar es una combinación de Contains y un join en la memoria: primero haga la statement contains como en la solución 3. Recuerde, nos acercó mucho a lo que queríamos. Luego refine el resultado de la consulta uniendo el resultado como una lista en memoria:

 var rawSelection = from entity in db.Table where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2) select entity; var refined = from entity in rawSelection.AsEnumerable() join pair in Tuples on new { entity.Id1, entity.Id2 } equals new { Id1 = pair.Item1, Id2 = pair.Item2 } select entity; 

No es elegante, desordenado de todos modos, pero hasta ahora es la única solución 2 escalable para este problema que encontré, y se aplicó en mi propio código.

Solución 6: generar una consulta con cláusulas OR

Usando un constructor Predicate como Linqkit o alternativas, puede crear una consulta que contenga una cláusula OR para cada elemento en la lista de combinaciones. Esta podría ser una opción viable para listas realmente cortas . Con un par de cientos de elementos, la consulta comenzará a funcionar muy mal. Por lo tanto, no considero que esta sea una buena solución a menos que pueda estar 100% seguro de que siempre habrá una pequeña cantidad de elementos. Una elaboración de esta opción se puede encontrar aquí .


1 Como nota curiosa, EF crea una statement SQL cuando te unes a una lista primitiva, como

 from entity in db.Table // db is a DbContext join i in MyIntegers on entity.Id1 equals i select entity 

Pero el SQL generado es, bueno, absurdo. Un ejemplo de la vida real donde MyIntegers contiene solo 5 (!) Enteros se ve así:

 SELECT [Extent1].[CmpId] AS [CmpId], [Extent1].[Name] AS [Name], FROM [dbo].[Company] AS [Extent1] INNER JOIN (SELECT [UnionAll3].[C1] AS [C1] FROM (SELECT [UnionAll2].[C1] AS [C1] FROM (SELECT [UnionAll1].[C1] AS [C1] FROM (SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] UNION ALL SELECT 2 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1] UNION ALL SELECT 3 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2] UNION ALL SELECT 4 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3] UNION ALL SELECT 5 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4] ON [Extent1].[CmpId] = [UnionAll4].[C1] 

Hay n-1 UNION s. Por supuesto, eso no es escalable en absoluto.

Más tarde:
En algún lugar a lo largo del camino hacia EF versión 6.1.3 esto ha sido mejorado en gran medida. Los UNION han vuelto más simples y ya no están nesteds. Anteriormente, la consulta se rendiría con menos de 50 elementos en la secuencia local (excepción SQL: alguna parte de su statement SQL está anidada demasiado profundamente ). La UNION no anidada permite secuencias locales de hasta un par de miles (!) De elementos. . Todavía es lento, aunque con “muchos” elementos.

2 En cuanto a la instrucción Contains escalable: escalable contiene el método para LINQ contra un back-end SQL

en el caso de la clave compuesta puede usar otra lista de identificación y agregar una condición para eso en su código

 context.Table.Where(q => listOfIds.Contains(q.Id) && listOfIds2.Contains(q.Id2)); 

o puede usar otro truco crear una lista de sus claves al agregarlas

 listofid.add(id+id1+......) context.Table.Where(q => listOfIds.Contains(q.Id+q.id1+.......)); 

Necesita un conjunto de objetos que representan las claves que desea consultar.

 class Key { int Id1 {get;set;} int Id2 {get;set;} 

Si tiene dos listas y simplemente verifica que cada valor aparezca en su respectiva lista, obtendrá el producto cartesiano de las listas, que probablemente no sea lo que usted desea. En su lugar, debe consultar las combinaciones específicas requeridas

 List keys = // get keys; context.Table.Where(q => keys.Any(k => k.Id1 == q.Id1 && k.Id2 == q.Id2)); 

No estoy completamente seguro de que este sea un uso válido de Entity Framework; puede tener problemas para enviar el tipo de Key a la base de datos. Si eso sucede, entonces puedes ser creativo:

 var composites = keys.Select(k => p1 * k.Id1 + p2 * k.Id2).ToList(); context.Table.Where(q => composites.Contains(p1 * q.Id1 + p2 * q.Id2)); 

Puede crear una función isomórfica (los números primos son buenos para esto), algo así como un código hash, que puede usar para comparar el par de valores. Siempre que los factores multiplicativos sean coprima, este patrón será isomorfo (uno a uno), es decir, el resultado de p1*Id1 + p2*Id2 identificará de forma única los valores de Id1 e Id2 , siempre que los números primos sean elegido correctamente

Pero luego terminas en una situación en la que estás implementando conceptos complejos y alguien tendrá que apoyar esto. Probablemente sea mejor escribir un procedimiento almacenado que tome los objetos clave válidos.

Puede crear una colección de cadenas con ambas claves como esta (supongo que sus claves son de tipo int):

 var id1id2Strings = listOfIds.Select(p => p.Id1+ "-" + p.Id2); 

Entonces solo puede usar “Contiene” en su db:

 using (dbEntities context = new dbEntities()) { var rec = await context.Table1.Where(entity => id1id2Strings .Contains(entity.Id1+ "-" + entity.Id2)); return rec.ToList(); } 

En ausencia de una solución general, creo que hay dos cosas a considerar:

  1. Evite las teclas primarias de varias columnas (también facilitará las pruebas unitarias).
  2. Pero si es necesario, es probable que uno de ellos reduzca el tamaño del resultado de la consulta a O (n) donde n es el tamaño del resultado de la consulta ideal. Desde aquí, su Solución 5 de Gerd Arnold arriba.

Por ejemplo, el problema que me llevó a esta pregunta fue consultar líneas de orden, donde la clave es ID de pedido + número de línea de orden + tipo de orden, y la fuente tenía el tipo de orden implícito. Es decir, el tipo de orden era una constante, el ID del pedido reduciría el conjunto de consultas para ordenar líneas de pedidos relevantes, y normalmente habría 5 o menos de estos por pedido.

Para reformular: si tiene una clave compuesta, los cambios son que uno de ellos tiene muy pocos duplicados. Aplique la Solución 5 de arriba con eso.