Quantity limit on database query

Question

Quantity limit on database query

Navigation

#1 by (1 votes)

1

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 100mil records for example, can nhibernate split this list into several of 1000? because we know that in the oracle IN clause, it only supports 1000 different records.

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

Can you generate an error knowing that Persistence can be connected to both SQL and ORACLE?

Would it be performative to use:?

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

Is there a restriction on the amount of Or that nhibernate supports before generating error in the database?

c# nhibernate asp.net-mvc fluent-nhibernate

asked by anonymous 24.12.2015 / 10:33

1 answer

Web Hosting Questions [closed] Logcat window in Webstorm 10

score 1 · Accepted Answer

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

No, nHibernate can not "divide" the list into several 1000's. It does nothing "in particular" with in , only passes the database information.

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

Break the list into "pieces" of 1000 records, as you mentioned:

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

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