c# - query - ¿Cómo puedo usar Nhibernate para recuperar datos cuando "WHERE IN()" tiene miles de valores?(demasiados parámetros en el sql)

nhibernate sql query string

Question

El problema : Nhibernate analiza cada valor en el "WHERE IN ()" como parámetros y MS SQL Server no admite suficientes parámetros (más de 2000).

Estoy usando Nhibernate con Linq para recuperar mis datos del servidor SQL y necesito cargar una gran cantidad de entidades basadas en ID ya conocidas.

Mi código se ve algo como esto:

int[] knownIds = GetIDsFromFile();
var loadedEntities = _Repository.GetAll()
                                .Where(x => knownIds.Contains(x.ID))
                                .ToList();

Que dan un sql como este:

SELECT id, name FROM MyTable 
WHERE id IN (1 /* @p0 */,2 /* @p1 */,3 /* @p2 */,4 /* @p3 */, 5 /* @p4 */)

Si hay demasiados valores en knownIds , entonces este código lanzará una excepción debido a los muchos parámetros que usa NHibernate.

Creo que la mejor solución sería si pudiera hacer que NHibernate use solo 1 parámetro para todo el "WHERE IN ()", pero no sé cómo hacerlo:

SELECT id, name FROM MyTable WHERE id IN (1, 2, 3, 4, 5 /* @p0 */)

Me complacerá escuchar cualquier idea sobre cómo resolver esto, ya sea mediante la extensión del proveedor de LINQ o por otros medios. Una solución es hacer la consulta x veces (conocidoIds.Count / 1000), pero más bien quiero una solución genérica que funcione para todas mis entidades.

He intentado ver cómo extender el proveedor de LINQ buscando en Google y Stackoverflow, sin embargo, no puedo encontrar una solución y no tengo ninguna experiencia con HQL o con treebuilder. Estos son algunos de los sitios en los que he estado:

• Extensión de LINQ al proveedor de Nhibernate, en combinación con el problema de Dynamic LINQ
• Extensión del proveedor de NHibernate LINQ
• Código fuente: NHibernate / Linq / Functions / QueryableGenerator.cs

ACTUALIZACIÓN: Sé que no es una buena práctica tener tantos valores en la cláusula IN, pero no conozco una mejor solución para lo que quiero hacer.
Considere una compañía donde todos los clientes pagan por los servicios de la compañía una vez cada mes. La compañía no maneja los pagos por sí misma, pero tiene otra compañía para recolectar el dinero. Una vez al mes, la compañía recibe un archivo que contiene el estado de estos pagos: si se han pagado o no. El archivo solo contiene el ID del pago específico, y no el ID del cliente. Una empresa con 3000 clientes mensuales realizará 3000 LogPayments cada mes, donde el estado debe actualizarse. Después de 1 año habrá alrededor de 36,000 LogPayments, así que solo cargarlos no parece ser una buena solución.

MI SOLUCIÓN: Gracias por todas las respuestas útiles. Al final, decidí usar una combinación de las respuestas. Para este caso específico hice algo como lo que Fourth sugirió, ya que eso aumentaría mucho el rendimiento. Sin embargo, también he implementado el método genérico que sugirió Stefan Steinegger, porque me gusta que puedo hacer esto, si eso es lo que realmente quiero. Además, no quiero que mi programa falle con una excepción, así que en el futuro también usaré este método ContainsAlot como salvaguarda.

Answer 1

Definitivamente recomendaría el uso de tablas temporales para este tipo de datos.

Obtendrá la capacidad de verificar si los parámetros son correctos, consultando esta tabla temporal. Y puede tener restricciones de clave externa, por lo que está preparado antes de las identificaciones incorrectas. Y puedes tener el historial de ellos en tu base de datos.

Answer 2

El único lugar donde he visto un código como ese antes donde los Id se extendían a miles, era donde esa lista de ID se acababa de cargar desde la base de datos como una consulta separada. En su lugar, debe crearse como DetachedCriteria y luego consumirlo mediante una consulta de criterios Subqueries.PropertyNotIn o PropertyIn (en lugar de LINQ).

Otra forma de ver este tipo de cosas es: 2100 parámetros se siente como un límite arbitrario. Estoy seguro de que SQL Server podría modificarse para aceptar más parámetros (pero estoy seguro de que una solicitud de conexión se cerraría casi de inmediato), o puede usar soluciones alternativas (como enviar XML o rellenar previamente una tabla) para pasar esta muchos parametros Pero si está alcanzando este límite, ¿no debería dar un paso atrás y considerar que hay algo más roto en lo que está haciendo?

Answer 3

Me enfrenté al mismo problema en Oracle, que no permite más de 1000 elementos dentro de la condición IN también. El error es: "ORA-01795: el número máximo de expresiones en una lista es 1000". Aquí está mi solución:

    //partition an IEnumerable into fixed size IEnumerables
    public static IEnumerable<IEnumerable<T>> Partition<T>(this IEnumerable<T> source, int partitionSize)
    {
        return source
            .Select((value, index) => new { Index = index, Value = value })
            .GroupBy(i => i.Index / partitionSize)
            .Select(i => i.Select(i2 => i2.Value));
    }

    public IEnumerable<T> Get(List<long> listOfIDs)
    {
        var partitionedList = listOfIDs.Partition(1000).ToList();
        List<ICriterion> criterions = new List<ICriterion>();
        foreach (var ids in partitionedList)
        {
            criterions.Add(Restrictions.In("Id", ids.ToArray()));
        }
        var criterion = criterions.Aggregate(Restrictions.Or);
        var criteria = session.CreateCriteria<T>().Add(criterion);
        return criteria.Future<T>();
    }

La primera parte es un método de extensión a IEnumerable, para particionar una lista grande en listas de tamaño fijo. La segunda parte utiliza los criterios de NHibernate para generar dinámicamente múltiples condiciones de IN que luego se unirán con las condiciones de OR.

Answer 4

No puede hacer que la lista IN sea solo un parámetro (por ejemplo, una matriz), porque no es compatible con SQL. La única forma que conozco de tener más de 1000 elementos en la lista de IN es poner una sub consulta allí.
Dicho esto, una solución alternativa sería colocar los identificadores conocidos en una tabla temporal y cambiar su declaración NHibernate para que use esta tabla, de modo que resulte en una subconsulta en la declaración SQL.

Answer 5

Vea esta pregunta similar: NHibernate Restrictions.In con cientos de valor

Por lo general, configuro varias consultas, todas las cuales obtienen por ejemplo 1000 entradas. Simplemente divida la matriz de identificadores en varias partes.

Algo como esto:

// only flush the session once. I have a using syntax to disable
// autoflush within a limited scope (without direct access to the
// session from the business logic)
session.Flush();
session.FlushMode = FlushMode.Never;

for (int i = 0; i < knownIds; i += 1000)
{
  var page = knownIds.Skip(i).Take(1000).ToArray();
  loadedEntities.AddRange(
    Repository.GetAll()
      .Where(x => page.Contains(x.ID)));
}

session.FlushMode = FlushMode.Auto;

Implementación genérica utilizando criterios (solo filtrando una sola propiedad, que es un caso común):

public IList<T> GetMany<TEntity, TProp>(
  Expression<Func<TEntity, TProp>> property,
  IEnumerable<TProp> values)
{
    string propertyName = ((System.Linq.Expressions.MemberExpression)property.Body).Member.Name;

    List<T> loadedEntities = new List<T>();

    // only flush the session once. 
    session.Flush();
    var previousFlushMode = session.FlushMode;
    session.FlushMode = FlushMode.Never;

    for (int i = 0; i < knownIds; i += 1000)
    {
      var page = knownIds.Skip(i).Take(1000).ToArray();

      loadedEntities.AddRange(session
        .CreateCriteria(typeof(T))
        .Add(Restriction.PropertyIn(propertyName, page)
        .List<TEntity>();
    }

    session.FlushMode = previousFlushMode;
    return loadedEntities;
}

Para ser utilizado de esta manera:

int[] ids = new [] {1, 2, 3, 4, 5 ....};
var entities = GetMany((MyEntity x) => x.Id, ids);

string[] names = new [] {"A", "B", "C", "D" ... };
var users = GetMany((User x) => x.Name, names);

Answer 6

WHERE IN no debe ser la norma y debe usarse solo en casos específicos y limitados. Si lo usa mucho, es probable que indique un problema con su modelo de datos. Lo que probablemente haría en su caso es obtener TODAS las entidades de la base de datos en una carga lenta, y luego, a medida que recorro las identificaciones que tengo, las saque de la colección de entidades. De esta manera, el impacto de rendimiento se distribuye entre muchas consultas y no se alcanza el umbral WHERE IN.

Solo para tener en cuenta, si las ID representan la mayoría de las entidades en lugar de un pequeño subconjunto (es decir, usted sabe que las terminará obteniendo todas, o la mayoría, de todos modos), entonces no haga una carga lenta.

Edición basada en tu actualización

Si está hablando de 36,000 registros después de 1 año, PERO solo está lidiando con cargas en el tiempo reciente, entonces cargue con entusiasmo los registros recientes que le interesan. Haría algo como: crear un criterio para cargar los registros del último ... ¿mes? Luego tendré todos los registros que pueda necesitar, hacerlos coincidir con las ID del archivo a través del código y bingo-bango-bongo.

La tabla definitivamente aumentará de tamaño con el tiempo, por lo que no tiene sentido retirar todo todo, pero si tiene una forma de decir "solo me preocupan estos registros", entonces SQL puede hacer esa restricción por usted.

Answer 7

http://ayende.com/blog/2583/nhibernates-xml-in tiene una posible solución, pasando los parámetros como XML (desafortunadamente, la mayoría de los enlaces en la página están rotos ...)