I went in to a trouble some time back playing with LINQ.
What kind of trouble am I talking about?
Lets see the code:
int[] values = { 1, 4, 6, 3 };
foreach (var item in values)
{
var query = (from r in dc.MarketCodes
where r.ID == item
select r).Single();
//... do something with the result
}
It's a typical example of querying a database using a table of indexes.
Nice, easy and clean.
Having reading a programming forum I’ve got an advice to rather use this example:
int[]values = { 1, 4, 6, 3 };
var query = from r in dc.MarketCodes
where values.Contains(r.ID)
select r;
Is it not fantastic? I think it really, really is.
I loved it since I have seen it first time. It's shorter, doesn't require a loop and it looks very sexi.
Unfortunately using .Contains() may put us in trouble because of the way the C# compiler will translate the LINQ query to T-SQL.
The content of the “where values.Contains(r.ID)” will be translated to something more or less looking like this:
“where r.ID IN(@p1, @p2, @p3…)”
It doesn’t look bad, just standard T-SQL which will be passed to the database engine.
It has some hidden limitations I’ve discovered when my application crashed after passed large amount of values in the “values” table.
I went to google for help and found that T-SQL has limitations about the amount of parameters in the query. When trying to pass more than 2100 parameters we will get an exception:
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.
Unfortunately I cannot tell how many values will be in the “parameters table” at every time in my application, which forces me to use less pretty (first) version of the query again.
Other words, it is nice to use LINQ and its syntax but this same it’s good to know how C# compiler will translate it to T-SQL.
.Contains() looks cool but it can be dangerous!
Subscribe