Working with LINQ to SQL 2100 max parameter constraints in Where Contained
Problem: I have a DataContext generated using SQLMetal on a SQL Server database. The database has table A that contains objects with Int64 identifiers. My queries should handle cases where I am querying for all elements with ids in some set. As the dataset grows, this random set contains over 2100 IDs.
I realize this question is similar to others that have been asked on this topic, but I am looking for help in creating an extension method to solve this problem.
Related questions:
Avoiding the 2100 parameter limit in LINQ to SQL
Reaching 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 it throws this error:
The remote procedure (RPC) tabular data (TDS) inbound protocol flow is not correct. Too many parameters were supplied in this RPC request. Maximum 2100.
I expect to run into this problem as different datasets grow and I would like to create a generic extension that I can use for any table. The idea is to break the request down into smaller ones. Where contains queries, 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 will be changed to:
var ids = new List<long>{ 1, 2, 3, /*...,*/ 2101};
var database = new MyDatabaseClass(connection)
var items = database
.TableA
.WhereMemberInUniverse(x=>x.RecordID, ids);
This currently does not work on the 'universeSlice.Contains' line:
Method 'System.Object DynamicInvoke (System.Object [])' does not support translation to SQL.
It seems like every attempt I make ends up with a similar SQL translation error.
source to share
An unsupported construct is a call memberSelector(x)
within an expression tree of a LINQ query.
To make a LINQ query translatable (and this applies mostly to any provider IQueryable
), you need to change the parameter type
Func<TSource, TUniverse> memberSelector
to
Expression<Func<TSource, TUniverse>> memberSelector
and then build
x => universeSlice.Contains(memberSelector(x))
dynamically using methods System.Linq.Expressions.Expression
:
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;
}
source to share
Depending on the version of SQL Server you are using, you should probably look at Table Parameters :
Table parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple traversals or special server-side logic to process the data. You can use table parameters to encapsulate strings of data into a client application and send data to the server in a single parameterized command. The incoming data lines are stored in a table variable that can be used with Transact-SQL.
First of all, you need to create a type on your SQL Server, for example:
CREATE TYPE [dbo].[MyIdTable] AS TABLE(
[Id] [int] NOT NULL
)
Then you can create System.Data.DataTable
and fill 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 as a parameter to SQL / Stored Procedure. However, TVPs are not supported in L2SQL as far as I know. As such, you probably have to dive into raw ADO.net. Here is a stack overflow question that might point you in the right direction.
source to share