Quantity limit in database query

Question

public IList<DtoContrato> ConsulteListaPorListaDeIds(List<Guid> listaIds)
{
   return Conversor(Persistencia().Where(x => listaIds.Contains(x.Id)));
}

My question is, if the list of ids has 100,000 records for example, can nhibernate divide this list into several of 1000? because we know that in oracle clause IN only supports 1000 different records.

What would be the most performative way to perform this consultation?

It can generate some error knowing that the Persistence can be connected to both SQL and ORACLE?

It would be performatic to use:?

.Or(x => parteDaLista1(x.Id)).Or(x => parteDaLista2(x.Id)).Or(x => parteDaLista3(x.Id))

Has Amount restriction of Or that nhibernate supports before generating bank error?

Answer 1

Yes, Oracle limits that 1000 records are in the clause in.

No, nhibernate cannot "manage" to divide the list into several of 1000. It does nothing "special" with the in, only passes the bank information.

This treatment has to be manual. If you really need to use more than 1000 elements in the clause in, you have two options:

1. Break the list into "pieces" of 1000 records, as you quoted:

.Or(x => parteDaLista1(x.Id)).Or(x => parteDaLista2(x.Id)).Or(x => parteDaLista3(x.Id))

2. Insert Ids into a global temporary table and solving everything through a select with joins. This will allow you to use Foreign Key Constraints, in addition to validating for example the Ids, in addition to a kind of history of them in your database. Indexing would be much better.