Oracle has a limit of 1000 for list in an IN request like this:
SELECT id FROM table WHERE a IN (10,21,34,....)
In my coding i solved this problem this way, but this is not really performant:
Code: Select all
var matchingRvals = from expAppl in allUnioned
group expAppl by expAppl into g
where g.Count() >= applIdsPerZone.Count
select g.Key;
int AnzMatchingRvals = matchingRvals.Count();
int z = 0;
int skipper = 0;
int Differenz = 0;
int IDBlockAnz = 1000;
int maxAnz = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(AnzMatchingRvals / IDBlockAnz)));
while (z <= (int)maxAnz)
{
var query =
(
from completeAppl in dsc.LlApplCompletes
where matchingRvals.Skip(skipper).Take(IDBlockAnz).Contains(completeAppl.LlcoApplId)
select completeAppl.LlcoApplId
);
hits = query.Count() + hits;
Differenz = AnzMatchingRvals - skipper;
if (Differenz < IDBlockAnz)
{
skipper = Convert.ToInt32(skipper) + Differenz;
} else {
skipper = Convert.ToInt32(skipper) + IDBlockAnz;
}
z++;
}
return hits;
thanks
Frank