bug in linq Contains statement - is there a fix or workaround?

Question

I found a bug in the Contains statement in Linq (not sure if it is really in Linq or Linq to SQL) and want to know if anyone else has seen this and if there is a fix or workaround.

If the querysource you do the contains with has more than 10 items in it, it does not pass the items correctly to the SQL query. It is hard to explain what it does, an example will show it best.

If you look at the raw query, the parameters look like this:

@P0 = 'aaa'
@P1 = 'bbb'
@P2 = 'ccc'
... [@P3 through @P9]
@P10 = '111'
@P11 = '222'
... [@p12 through @P19]
@P20 = 'sss'
... [@P21 through @P99]
@P100 = 'qqq'

when the values are passed into the final query (all parameters resolved) it has resolved the parameters as if these were the values passed:

@P0 = 'aaa'
@P1 = 'bbb'
@P2 = 'ccc'
...
@P10 = 'bbb'0
@P11 = 'bbb'1
...
@P20 = 'ccc'0
...
@P100 = 'bbb'00

So it looks like the parameter resolving looks at the first digit only after the @P and resolves that, then adds on anything left at the end of the parameter name.

At least that is what the Sql Server Query Visualizer plugin to Visual Studio shows the query doing.

Really strange.

So if any one has advice please share. Thanks!

Update: I have rewrote the original linq statement to where I now use a join instead of the Contains, but would still like to know if there is a way around this issue.

Answer 1

The more I look at it, and after running more tests, I'm thinking the bug may be in the Sql Server Query Visualizer plugin for Visual Studio, not actually in Linq to SQL itself. So it is not nearly as bad a situation as I thought - the query will return the right results, but you can't trust what the Visualizer is showing. Not great, but better than what I thought was going on.

Answer 2

Could you post the linq query and the database schema, so we can play with it ourselves?

Answer 3

Try actually looking at the output from your datacontext before you pass judgement.

DataContext.Log() will give you the generated SQL.