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:

1. Why does a stack overflow occur using the foreach loop?

2. 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.

3. 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.