Working around the LINQ to SQL 2100 max parameter constraint in Where Contains

Question

The Problem: I have a DataContext generated using SQLMetal on an SQL Server database. The database has TableA which contains entities with Int64 identifiers. My queries need to handle cases where I am querying for all elements with IDs in some set. As the data set has grown, this set occasional contains more than 2100 IDs.

I realize this question is similar to others that have been asked on the topic, but I am looking for help building an extension method to work around the issue.

Related questions:
Avoiding the 2100 parameter limit in LINQ to SQL
Hitting the 2100 parameter limit (SQL Server) when using Contains()

My code looks something like this:

var ids = new List<long>{ 1, 2, 3, /*...,*/ 2101};
var database = new MyDatabaseClass(connection)
var items = database
 .TableA
 .Where(x=>ids.Contains(x.RecordID))
 .ToList();

And produces this error:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

I anticipate running into this issue a lot as various data sets grow and I would like to create a generic extension that I can use for any table. The idea is to break up the query into smaller Where Contains queries and then aggregate the results. Here is one of my attempts to show what I am thinking:

public static List<TSource> WhereMemberInUniverse<TSource, TUniverse>(this IQueryable<TSource> source, Func<TSource, TUniverse> memberSelector, IEnumerable<TUniverse> universe)
{
    var distinctUniverse = universe.Distinct().ToList();
    int batchSize = 2000;

    var result = new List<TSource>();

    for (int i = 0; i < distinctUniverse.Count; i += batchSize)
    {
        var universeSlice = distinctUniverse.Skip(i).Take(batchSize);
        var partialRes = source
            .Where(x => universeSlice.Contains(memberSelector(x)));
        result.AddRange(partialRes);
    }

    return result;
}

The calling code would be modified to:

var ids = new List<long>{ 1, 2, 3, /*...,*/ 2101};
var database = new MyDatabaseClass(connection)
var items = database
.TableA
.WhereMemberInUniverse(x=>x.RecordID, ids);

Right now this is failing on the 'universeSlice.Contains' line:

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

It seems that every attempt I make ends in a similar SQL translation error.

Have you tried the XML approach? [SQL Server Central](https://www.sqlservercentral.com/Forums/Topic352589-149-1.aspx) — Mad Myche, May 16 '17 at 17:23

score 5 · Accepted Answer · answered May 16 '17 at 17:47

The unsupported construct is the memberSelector(x) invocation inside the LINQ query expression tree.

To make LINQ query translatable (and this applies basically to any IQueryable provider), you need to change the parameter type

Func<TSource, TUniverse> memberSelector

to

Expression<Func<TSource, TUniverse>> memberSelector

and then build the

x => universeSlice.Contains(memberSelector(x))

dynamically using the System.Linq.Expressions.Expression class methods:

public static List<TSource> WhereMemberInUniverse<TSource, TUniverse>(this IQueryable<TSource> source, Expression<Func<TSource, TUniverse>> memberSelector, IEnumerable<TUniverse> universe)
{
    var distinctUniverse = universe.Distinct().ToList();
    int batchSize = 2000;

    var result = new List<TSource>();

    for (int i = 0; i < distinctUniverse.Count; i += batchSize)
    {
        var universeSlice = distinctUniverse.Skip(i).Take(batchSize);
        //x => universeSlice.Contains(memberSelector(x))
        var predicate = Expression.Lambda<Func<TSource, bool>>(
            Expression.Call(
                typeof(Enumerable), "Contains", new Type[] { typeof(TUniverse) },
                Expression.Constant(universeSlice), memberSelector.Body
            ),
            memberSelector.Parameters
        );
        var partialRes = source.Where(predicate);
        result.AddRange(partialRes);
    }

    return result;
}

Note that using something like LINQKit can make this a lot easier. — NetMage, May 16 '17 at 18:46
I had a feeling the answer might have something to do with expressions. This is exactly what I was looking for. Thanks Ivan! — c31983, May 16 '17 at 18:50

score 0 · Answer 2 · edited May 23 '17 at 12:18

Depending on the version of SQL Server you're using, you should probably look into Table Valued Parameters:

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

First off, you'd need to create the type in your SQL Server, something like:

CREATE TYPE [dbo].[MyIdTable] AS TABLE(
    [Id] [int] NOT NULL
)

Then you can create a System.Data.DataTable and populate it with your IDs:

var table = new DataTable("MyIdTable");
table.Columns.Add("Id", typeof(int));
foreach (var id in ids)
    table.Rows.Add(id);

And send it over as a parameter to your SQL/Stored Procedure. However, TVPs aren't supported in L2SQL, as far as I know. So you'll probably need to dip down into raw ADO.net. Here's a Stack Overflow question that can point you in the right direction.

score -1 · Answer 3 · answered Apr 04 '19 at 10:52

I ran into similar and found this lazy method of using AsEnumerable() which worked for me, but i'm told it loads the entire table into memory so may not be acceptable.

 .TableA
 .AsEnumerable()          
 .Where(x=>ids.Contains(x.RecordID))
 .ToList();

Working around the LINQ to SQL 2100 max parameter constraint in Where Contains

3 Answers3