I am writing an application in C# using LINQ and LINQKit. I have a very large database table with company registration numbers in it.
I want to do a LINQ query which will produce the equivalent SQL:
select * from table1 where regno in('123','456')
The 'in' clause may have thousands of terms.
First I get the company registration numbers from a field such as Country.
I then add all the company registration numbers to a predicate:
var predicate = PredicateExtensions.False<table2>();
if (RegNos != null)
{
foreach (int searchTerm in RegNos)
{
int temp = searchTerm;
predicate = predicate.Or(ec => ec.regno.Equals(temp));
}
}
On Windows
Vista Professional a stack overflow exception occured after 4063 terms were added. On Windows Server 2003 a stack overflow exception occured after about 1000 terms were added. I had to solve this problem quickly for a demo.
To solve the problem I used this notation:
var predicate = PredicateExtensions.False<table2>();
if (RegNosDistinct != null)
{
predicate = predicate.Or(ec => RegNos.Contains(ec.regno));
}
My questions are:
Why does a stack overflow occur using the foreach loop?
I take it Windows Server 2003 has a much smaller stack per process\thread than NT\2000\XP\Vista\Windows 7 workstation versions of Windows.
Which is the fastest and most correct way to achieve this using LINQ and LINQKit?
It was suggested I stop using LINQ and go back to dynamic SQL or ADO.NET but I think using LINQ and LINQKit is far better for maintainability.
