Linq to SQL and Collection operations

Question

I have a Dictionary which I'd like to use like an IN clause within a SQL query.

I have a Linq-To-SQL query where I would like to use this Dictionary's Keys to check fields in the rows for the query.

E.g.

bool result = DataContext.Table.Any(res => MyDictionary.ContainsKey(res.field1));

In effect this is similar to

    exists(select * from Table where field1 in (select id from DictionaryKeys))

where DictionaryKeys would be an expansion of the Keys into their own table.

Unfortunately I get

System.NotSupportedException was unhandled
Message="Method 'Boolean ContainsKey(System.String)' has no supported translation to SQL."
Source="System.Data.Linq"

I understand the error, but I'm struggling to think around the problem to a different solution.

Edit: I'm connecting to SQL 2005. Looks like this is a connection provider issue, then, because Marc's suggestion to translate to a List doesn't work for me.

Any ideas?

Answer 1

Try putting the keys into a simple list and using Contains:

var keys = MyDictionary.Keys.ToList();

DataContext.Table.Any(res => keys.Contains(res.field1));

Answer 2

i am having a similar problem and cant use a List because if i do a Contains on a list which has more than 2100 elements it throws error . So i want to use a dictionary