LinqKit stack overflow exception using predicate builder

Question

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 (RegNos != 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.

Answer 1

You have a couple of issues here. First, L2S under the covers uses sp_execsql, a SQL UDF. SQL UDFs can be passed a maximum of 2100 parameters, and IIRC, constants and comparands are always emitted as parameters. So even if the expression evaluator hadn't died, the resultant query wouldn't have run. The expression evaluator likely died because PredicateBuilder nests added expressions, and the L2S expression evaluator is recursively walking the expression tree. Perhaps you could stuff the large filter list into a temp table and join against it? I believe the designer supports named temp tables, or you could use a table-valued sproc to insert the filter values, then use L2S to do the heavy lifting.