[Fixed]How to overcome the linq contains limitation.Too many parameters were provided in this RPC request. the maximum is 2100

When we want to filter the data from the Linq to Sql query with the collection every one is trying to use the Contains() operator as follows. Linq to Sql will try to generate a sql query to represent the whole expression. This means that it will try to pass in the collection as parameters to a sql query. If there are too many parameters the query will hit the parameter limit (2100) and it cannot be executed. If there are not that many parameters, then you can use a contains expression which will be converted to an "IN" expression in sql. Sample code as follows

List<int> contactIds=Contact.GetContactIds()
//count of contactIds is morethan 2100
var contacts = Context.Contacts.Where(c => contactIds.Contains(c.ContactId)).ToList();

If the parameter limit exeeds then the following exception throws.

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.

To avoid this many developers will try to join the in memory collection with database table as follows. 

List<int> contactIds=Contact.GetContactIds()
//count of contactIds is morethan 2100
var contacts = Context.Contacts.Join(contactIds, c => c.ContactId, ci => ci, (c, ci) => c).ToList();

When developer trying this the following exception throws.

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

This means that the linq to sql generates sql query and executes on the database server. Our in memory collection will not support to execute on the database server. so will get the above exception.
The easiest resolution is to convert the LINQ to SQL table into an IEnumerable list, which can be done as follows.

List<int> contactIds=Contact.GetContactIds()
//count of contactIds is morethan 2100
var contacts = Context.Contacts.AsEnumerable().Join(contactIds, c => c.ContactId, ci => ci, (c, ci) => c).ToList();

I hope this solution helps you.

SQL Server does not handle comparison of NText, Text, Xml, or Imagedata types.

When insert XML to an XML field in SQL server via Linq to SQL it all works fine, But  when you try  to update this XML field using LINQ, you will  get this exception:

System.NotSupportedException: SQL Server does not handle comparison of NText, Text, Xml, or Image data types

Inorder to fix this issue just open the dbml file with xml editor and set the updatecheck to false as follows:

<column canbenull="true" dbtype="Xml" name="PermissionsXml" type="System.Xml.Linq.XElement" updatecheck="Never"></column>

Querying xml using LINQ

LINQ to XML

Suppose that instead of hard-coding our suppliers and products, we’d used the following XML file:

<?xml version="1.0"?>
<Data>
<Products>
<Product SupplierID="1">
<Name> West Side Story</Name>
<Price>9.99</Price>
</Product>
<Product SupplierID="2">
<Name>Assassins</Name>
<Price>14.99</Price>
</Product>
<Product SupplierID="1">
<Name>Frogs</Name>
<Price>9.99</Price>
</Product>
</Products>
<Suppliers>
<Supplier SupplierID="1">
<Name>Solely Sondheim</name>
</Supplier>
<Supplier SupplierID="2">
<Name>CD-by-CD-by-Sondheim</name>
</Supplier>
</Suppliers>
</Data>

The file is simple enough, but what’s the best way of extracting the data from it? How do we query it? Join on it?. The following listing shows how much work we have to do in LINQ to XML.

XDocument doc = XDocument.Load("data.xml");
var filtered = from p in doc.Descendants("Product")
join s in doc.Descendants("Supplier")
on (int)p.Attribute("SupplierID")
equals (int)s.Attribute("SupplierID")
where (decimal)p.Element("Price") > 10
orderby (string)s.Element("Name"),
(string)p.Element("Name")
select new
{
SupplierName = (string)s.Element("Name"),
ProductName = (string)p.Element("Name")
};
foreach (var v in filtered)
{
Console.WriteLine("Supplier={0}; Product={1}",
v.SupplierName, v.ProductName);
}

How to Get the Index Position of the LINQ Query Results

Select  and SelectMany  expose an overload that surfaces the index position (starting at zero) for each returned element in the Select projection. It is surfaced as an overloaded parameter argument of the selector lambda expression and is only accessible using the extension method query syntax(Lamda Expression). The following sample code demonstrates how to access and use the index position value in a Select projection.

List<CallLog> callLog = CallLog.SampleData();
var q = callLog.GroupBy(g => g.Number)
.OrderByDescending(g => g.Count())
.Select((g, index) => new
{
number = g.Key,
rank = index + 1, //one is added because it is zero based index
count = g.Count()
});

Generating xml using linq query

The sample code for generating xml using linq query as follows

List<Contact> contacts = Contact.SampleData();
List<CallLog> callLog = CallLog.SampleData();
var q = from call in callLog
where call.Incoming == true
group call by call.Number into g
join contact in contacts on
g.Key equals contact.Phone
orderby contact.LastName, contact.FirstName
select new XElement(”contact”,
new XElement(”lastName”,
contact.LastName),
new XElement(”firstName”,
contact.FirstName),
new XElement(”count”,
g.Count()),
new XElement(”totalDuration”,
g.Sum(c => c.Duration)),
new XElement(”averageDuration”,
g.Average(c => c.Duration))
);
// create the XML document and add the items in query q
XDocument doc = new XDocument(
new XComment(”Summarized Incoming Call Stats”),
new XElement(”contacts”, q)
);
Console.WriteLine(doc.ToString());

output for the above query is as follows

<!—Summarized Incoming Call Stats—> 
<contacts>
<contact> 
<lastName>Gottshall</lastName> 
<firstName>Barney</firstName> 
<count>4</count> 
<totalDuration>31</totalDuration> 
<averageDuration>7.75</averageDuration>
</contact> 
... (records cut for brevity) 
<contact>
<lastName>Valdes</lastName> 
<firstName>Armando</firstName> 
<count>2</count> 
<totalDuration>20</totalDuration> 
<averageDuration>10</averageDuration>
</contact> 
</contacts>