Comment puis-je utiliser Nhibernate pour récupérer des données alors que le "WHERE IN()" a des milliers de valeurs? (trop de paramètres dans le sql)

le problème : Nhibernate analyse chaque valeur dans le sql "WHERE IN ()" comme paramètres et MS SQL server ne supporte pas assez de paramètres (plus de 2000).

j'utilise Nhibernate avec Linq pour extraire mes données du serveur SQL et j'ai besoin de charger beaucoup d'entités basées sur des ID déjà connus.

mon code ressemble à quelque chose comme ceci: 

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

qui donnent un sql comme ceci: 

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

S'il y a trop de valeurs dans knownIds , alors ce code va jeter une Exception en raison des nombreux paramètres que NHibernate utilise.

je pense que la meilleure solution serait si je pouvais faire NHibernate utiliser seulement un paramètre pour l'ensemble " où en ()", mais je ne sais pas comment faire cela: 

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

je serai heureux d'entendre toutes les idées de la façon de résoudre cela-soit en étendant le fournisseur de LINQ ou en d'autres moyens. Une solution est de faire simple la requête x fois (knownIds.Count / 1000), mais je veux plutôt une solution générique qui fonctionnerait pour toutes mes entités.

j'ai essayé de chercher à étendre le fournisseur LINQ en cherchant google et Stackoverflow, mais je ne peux pas trouver une solution et je n'ai aucune expérience avec HQL ou le treebuilder. Voici quelques-uns des sites où j'ai été:

mise à JOUR: Je sais que ce n'est pas une bonne pratique d'avoir autant de valeurs dans la clause, mais je ne connais pas de meilleure solution pour ce que je veux faire.

Envisagez une entreprise où tous les clients paient pour les services de l'entreprise une fois par mois. La société ne gère pas les paiements elle-même, mais a une autre société pour collecter l'argent. Une fois par mois, la société reçoit un dossier contenant l'état de ces paiements: s'ils ont été payés ou non. Le fichier contient seulement L'ID du paiement spécifique, et non L'ID du client. Une entreprise avec 3000 clients mensuels, fera alors 3000 paiements LogP chaque mois, où le statut doit être mis à jour. Après 1 an, il y aura environ 36.000 Logpaiements, donc simplement les charger tous ne semble pas être une bonne solution non plus.

MA SOLUTION: Merci à tous pour l'utile réponses. Finalement, j'ai choisi d'utiliser une combinaison des réponses. Pour ce cas précis, J'ai fait quelque chose comme quatrième suggéré, car cela augmenterait beaucoup la performance. Cependant, j'ai également mis en œuvre la méthode générique Stefan Steinegger a suggéré, parce que j'aime que je peux le faire, si c'est ce que je veux vraiment. En outre, Je ne veux pas que mon programme crash avec une exception, donc dans le futur je vais aussi utiliser cette méthode ContainsAlot comme une sauvegarde.

20
demandé sur Community 2011-06-09 17:16:37

7 réponses

Voir cette question similaire: NHibernate Restrictions.Avec des centaines de valeur

j'ai généralement mis en place plusieurs requêtes, qui obtiennent toutes par exemple 1000 entrées. Il suffit de diviser votre tableau d'ID en plusieurs morceaux.

quelque chose comme ça: 

// 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;

Générique de la mise en œuvre à l'aide de critères (seulement le filtrage d'une propriété unique, qui est un cas courant): 

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;
}

À utiliser comme ceci: 

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);
12
répondu Stefan Steinegger 2017-05-23 12:34:54

http://ayende.com/blog/2583/nhibernates-xml-in a une solution possible, en passant les paramètres en XML (malheureusement la plupart des liens dans la page sont brisés..)

2
répondu stuartd 2011-06-09 13:31:08

où IN ne devrait pas être la norme et ne devrait être utilisé que dans des cas spécifiques et limités. Si vous l'utilisez beaucoup, cela indique probablement un problème avec votre modèle de données. Ce que je ferais probablement dans votre cas est d'obtenir toutes les entités de la base de données dans une charge paresseuse, et alors que je itère à travers les Id que j'ai, les retirer de la collection d'entités. De cette façon, la performance hit est distribué à travers de nombreuses requêtes et vous ne frappez pas L'où dans threshold.

juste pour noter, si les IDs représenteront la plupart des entités plutôt qu'un petit sous-ensemble (c'est-à-dire que vous savez que vous allez finir par les obtenir tous, ou la plupart, de toute façon) alors ne pas paresseux-charge.

modifier sur la base de votre mise à jour

si vous parlez d'environ 36 000 enregistrements après 1 an, mais vous avez affaire à des charges seulement dans les temps récents, alors chargez impatiemment les enregistrements récents dont vous vous souciez. Je ferais quelque chose comme: créez un critère pour charger les enregistrements du passé... les mois? Alors j'aurai tous les enregistrements dont j'aurais besoin, les apparier avec les ID dans le fichier via le code, et bingo-bango-bongo.

la table va certainement augmenter en taille au fil du temps donc il n'est pas logique de toujours tout retirer, mais si vous avez une façon de dire "je me soucie seulement de ces enregistrements" alors SQL peut faire cette contrainte pour vous.

2
répondu Fourth 2011-06-10 11:26:44

le seul endroit où j'ai vu du code comme ça avant où les ID s'étendaient sur des milliers, c'était là où cette liste D'ID venait d'être chargée à partir de la base de données comme une requête séparée. Il devrait plutôt être créé en tant que DetachedCriteria , puis consommé en utilisant une requête de critères Subqueries.PropertyNotIn ou PropertyIn (plutôt que LINQ).

une Autre façon de regarder ce genre de chose est - 2100 paramètres se sent comme une limite arbitraire. Je suis sûr que SQL Server pourrait être modifié pour accepter plus de paramètres (mais je suis sûr qu'une requête Connect se fermerait presque immédiatement), ou vous pouvez utiliser des solutions de rechange (comme envoyer XML, ou pré-peupler une table) pour passer ces nombreux paramètres. Mais si vous atteignez cette limite, ne devriez-vous pas prendre du recul et considérer qu'il y a autre chose de cassé dans ce que vous faites?

1
répondu Damien_The_Unbeliever 2011-06-09 14:02:40

vous ne pouvez pas faire la liste dans un seul paramètre (par exemple un tableau), parce que cela n'est pas supporté par SQL. Le seul moyen que je connaisse pour avoir plus de 1000 éléments dans la liste IN est d'y mettre une requête secondaire.

Cela dit, une solution serait de mettre les ID connus dans une table temporaire et de changer votre déclaration NHibernate pour utiliser cette table de sorte qu'il en résultera une sous-requête dans la déclaration SQL.

0
répondu Daniel Hilgarth 2011-06-09 13:28:27

je recommande certainement l'utilisation de la table temporaire pour ce genre de données.

, Vous obtenez la capacité de vérifier si les paramètres sont corrects, en interrogeant cette table temporaire. Et vous pouvez avoir des contraintes de clé étrangère, donc vous êtes cuit avant les mauvaises identifications. Et vous pouvez avoir l'histoire dans votre base de données.

0
répondu Euphoric 2011-06-10 07:31:27

j'ai fait face au même problème dans Oracle qui ne permet pas plus de 1000 éléments à l'intérieur en condition ainsi. L'erreur est: "ORA-01795: nombre maximum d'expressions dans la liste des 1000". Voici ma solution: 

    //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>();
    }

première partie est une méthode d'extension à IEnumerable, pour diviser une grande liste en listes de taille fixe. La deuxième partie utilise des critères de NHibernate pour générer dynamiquement multiple dans des conditions pour être plus tard JOINT AVEC OU conditions.

0
répondu ozanmut 2017-12-05 12:42:11