15

"Method 'Boolean Contains(System.String)' has no supported translation to SQL."

query is IsQueryable but this stopped working:

foreach (string s in collection1)
{
       if (s.Length > 0)
                {
                    query = query.Where(m => m.collection2.Contains(s));

                }
}

UPDATE: it works when i make query "ienumerable" instead of iqueryable. What would be the way to get same result using linq instead of iterating through loop?

zsharp
  • 13,656
  • 29
  • 86
  • 152
  • Can you post what your query is before it goes into this loop? – Andy White Mar 09 '09 at 05:44
  • Using pure LINQ does not change a thing -- it also has the same problem. For reason unknown, if LINQ detects "strange" collection as HashSet it does not use it as IEnumerable, user has to convert his/her collection directly to IEnumerable -- then Containts is correctly translated to SQL (IN I assume). – greenoldman Jan 21 '11 at 09:28

4 Answers4

26

Try this:

query = query.Where(m => m.collection2.ToList().Contains(s));
                                       ^^^^^^^^
Frank Krueger
  • 69,552
  • 46
  • 163
  • 208
Sunrise
  • 1,455
  • 2
  • 16
  • 13
  • 3
    +1 - It seems that to get LINQ to SQL to generate a SQL 'IN' clause, the *compile time* type of your collection must be a *List*. In my tests, I had an instance of List, but the query saw it as an IList and I got the error mentioned in the question. However, ensuring that the query saw it as a List at compile time fixed it. – Alex Humphrey Feb 09 '12 at 10:07
2

Take a look at this answer from stackoverflow.

It looks like the resulting query would need access to something that the database has no way of reaching, because the info is in memory.

Community
  • 1
  • 1
Klinger
  • 4,900
  • 1
  • 30
  • 35
  • Nevertheless the data in memory, can be transferred to SQL Server, as any other query. Contains should translated to IN. – greenoldman Jan 21 '11 at 09:25
1

Since m.collection2 is in the database, don't use Contains. Use Any

m.collection2.Any(x => x == s)
Amy B
  • 108,202
  • 21
  • 135
  • 185
0

It looks like the error you are seeing is coming from the collection collection 2. Have you tried wrappering the m.collection2 in another function which returns true or false? Is this LINQ syntax?

ewalk
  • 1,438
  • 1
  • 10
  • 15