Getting 2100 parameter limit in LINQ

Question

I am getting 2100 parameter limit error in a LINQ to SQL query. Here is the procedure which returns the list of id's. Parameter l has over 5000 id's.

    Public Function GetByID(ByVal l As List(Of Int32)) As List(Of OfficeAccess)
    Return (From d In db.OfficeAccess
            Where l.Contains(d.ID)
            Order By d.ID Ascending Select d).ToList()
    End Function

I have tried the solution given in the second part of Hitting the 2100 parameter limit (SQL Server) when using Contains() but I'd need another solution without manually building SQL query string and this doesn't work:

 Dim ids = String.Join(" ", l.ToArray())
    Return(From d In db.OfficeAccess
           Where ids.IndexOf(Convert.ToString(d.ID)) != -1
           Order By d.ID Ascending Select d).ToList()

Updated

I have used the following code and works fine.

    Dim l As New List(Of OfficeAccess)
    Dim f As Int32, t As List(Of Int32)


    While (ids.Count > 0)
        If (ids.Count < 2000) Then f = ids.Count Else f = 2000
        t = ids.GetRange(0, f)
        l.AddRange((From d In db.OfficeAccess Where t.Contains(d.ID) Select d).ToList())
        ids.RemoveRange(0, f)
    End While

I'd partition list in sub-lists then you can concatenate each query result enumeration and, if required, make it a list — Adriano Repetti, Aug 04 '15 at 11:32

Adriano Repetti · Answer 1 · 2015-08-04T12:23:40.060

You may want to partition your original list into smaller chunks. Let's say each one of 2000 elements, this will affect performance (especially if SQL Server version doesn't support OFFSET n and FETCH NEXT m ROWS) but it'll solve your problem.

Function Partition(ByVal l As List(Of Integer)) As List(Of OfficeAccess)
    Const size As Integer = 2000

    Dim result As List(Of OfficeAccess) = new List(Of OfficeAccess)

    Dim index As Integer = 1
    Dim partition As List(Of Integer) = (
        From item In l
        Take size
    ).ToList()

    While partition.Any()
        result.AddRange(
            From d In db.OfficeAccess
            Where partition.Contains(d.ID)
            Order By d.ID Ascending
            Select d
        )

        partition = (
            From item in l
            Skip index * size
            Take size
        ).ToList()

        index += 1
    End While

    Return result
End Function

Please note code is untested then be ready to syntax errors!

Note: in this example I accumulate partial results into result list but you may concatenate each enumeration with Enumerable.Concat() (declaring result as IEnumerable(Of OfficeAccess) and performing ToList() once: Return result.ToList(). It shouldn't make any (visible) difference unless you have a huge number of records (because, at least, you avoid to resize result for each bulk insertion) or unless LINQ is smart enough to optimize (somehow) full resulting query (as concatenation of multiple queries).

_{I'd avoid, if possible, to manually build SQL string (also to keep code reusable for other data types and easy to update and refactor) but, from linked post, you should try code from one of answers instead of not working code from question...}

could you please check updated code in my question, it is working fine. Do you think its a right way of doing it. — user1263981, Aug 04 '15 at 12:53
@user1263981 yes, if you can modify input collection with id's then it's pretty effective! — Adriano Repetti, Aug 04 '15 at 14:14

Getting 2100 parameter limit in LINQ

1 Answers1