Linq to SQL Operations and Collections

Question

I have a dictionary that I would like to use as an IN clause in a SQL query.

I have a Linq-To-SQL query where I would like to use these dictionary keys to check the fields in the query strings.

eg.

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

In fact, it looks like

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

where DictionaryKeyswill be the extension of the Keys to their own table.

Sorry, 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 deal with the problem in another solution.

Edit: I am connecting to SQL 2005. This seems to be the problem of the connection provider, therefore, since Marc's proposal to translate to List does not work for me.

Any ideas?

Answer 1

:

var keys = MyDictionary.Keys.ToList();

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