Home Dashboard Directory Help
Search

Use an xml type parameter in order to exceed the 2100 parameter limit in Linq to SQL when filtering via a local collection by kainhart


Status: 

Closed
 as Won't Fix Help for as Won't Fix


15
0
Sign in
to vote
Type: Suggestion
ID: 475984
Opened: 7/21/2009 6:49:55 AM
Access Restriction: Public
1
Workaround(s)
view

Description

When using the Contains method of a local collection/array within a Linq to SQL expression there is a maximum size supported for that collection of 2100 items. This is due to how the expression is translated to T-SQL by using individual parameters as shown below.

SELECT [t0].[Employee ID]
FROM [Employees] AS [t0]
WHERE [t0].[First Name] IN (@p0, @p1, @p2)

So instead of this we might see the following query instead

SELECT [t0].[Employee ID]
FROM [Employees] AS [t0]
WHERE [t0].[First Name] IN (select T.c.value('.', 'varchar(max)') from @p0.nodes('/list/value') AS T(c))

which is valid given that the parameter @p0 is defined as shown in the example below.

DECLARE @p0 AS XML

SET @p0 =
'<list>
    <value>Jeff</value>
    <value>Alan</value>
    <value>Fred</value>
</list>'


A lot of people seem to be using the solution presented by LINQKit however to me it seems more of a hack since it requires the linq query developer to explicitly break up their queries into separate chunks.

http://www.albahari.com/nutshell/linqkit.aspx

I believe the real solution is to naively support a way to carry non-atomic parameters over to SQL Server when using the Contains method within a Linq expression either by using the suggested method or any other more capable method. By using XML we should have a way to produce typed data that is still safer than plain text concatenation and in my tests the proposed solution seems to yield acceptable performance.
Details
Sign in to post a comment.
Posted by kainhart on 7/28/2009 at 12:59 PM
Well, with a Table Valued parameter you exclude both SQL Server 2005 and 2000. At least with the xml data type you exclude only SQL Server 2000 and I'm guessing even in that case there may be some workarounds using openxml with a parameter of data type TEXT. Since it is a known fact that Linq to SQL inspects the database to learn more about the target database version, this knowledge can be used to let Linq to SQL choose to use Table Valued parameters for SQL Server 2008, xml data type for SQL Server 2005, or text/varchar for for SQL Server 2000. BTW, I would bet that ADO.NET table parameter support probably uses XML serialization underneath anyway.

If the provider were more extensible for Linq to SQL we could add this functionality ourselves until it is implemented but at this point we have neither extensibility or support for lists larger than 2100 items which is frankly just not acceptable.

If this functionality isn't going to make the .NET Framework 4.0 release then I would strongly urge you to consider making sure it is part of the next service pack. In the short term, opening up access to the Linq to SQL provider to allow overriding functionality will help us and others to more elegantly bypass it's shortcomings. At least making Linq to SQL provider extensible should be easy enough to fit it in for .NET 4.0 which is still in Beta. Otherwise If no efforts are done to correct this it will prompt us to either find or create a different Linq provider or to abandon our attempts to use Linq to SQL which would be a big shame as we have really grown to like this technology.

BTW, if this issue is one of those things that lower priority for Linq to SQL because of a shift of emphasis to Entity Framework then any information indicating that it will or will not be addressed in EF would also be very useful.

NOTE: It is pretty widely known now that Linq to SQL originally was designed in a way that allowed the provider to be extended however the general consensus is that it was closed off for whatever reason shortly before it was released.

http://blogs.msdn.com/mattwar/archive/2008/05/04/mocks-nix-an-extensible-linq-to-sql-datacontext.aspx
Posted by Microsoft on 7/28/2009 at 11:50 AM
Thanks for sending this suggestion along. Yes it won't work today because of the limitation you mention. In the future, we probably want to address this not by using XML but by taking advantage of Table Value Parameters perhaps.

We won't be able to address this issue in the next release however as we are now closing down new work and stabilizing the product.

Thanks.
LINQ to SQL Team
Posted by Microsoft on 7/28/2009 at 2:24 AM
Thanks for reporting this issue. We are routing this suggestion to the product unit who works on that specific feature area. The team will review this suggestion and make a decision on whether they will fix it or not for the next release.

Thank you,
Visual Studio Product Team
Sign in to post a workaround.
Posted by kainhart on 7/21/2009 at 6:51 AM
There is a workaround available by using LinqKit however I think it is a hack that we shouldn't have to rely on.