Case Insensitive Array.Contains in Linq Query

Question

I'm trying to use Array.Contains on a string array within a Linq query:

var otherMatchingDevices = from d in selectedDevices
                           from c in mldb.Companies
                           where d.CompanyID == c.CompanyID && c.Deleted == 0
                           where searchTerms.Contains(d.Name.ToString(), StringComparer.CurrentCultureIgnoreCase) || searchTerms.Contains(c.CompanyName.ToString(), StringComparer.CurrentCultureIgnoreCase)
                           select d;

When the query is evaluated it crashes with "Unsupported overload used for query operator 'Contains'.

I tested this code using the StringComparer and it works fine and prints out "fOO":

string[] sList = { "fOO", "bar" };
string[] array = { "foo" };
List<string> stringlist = sList.ToList();
var qry = from s in stringlist
          where array.Contains(s, StringComparer.CurrentCultureIgnoreCase)
          select s;
if (qry.Count() > 0) Console.WriteLine(qry.First().ToString());

Can anyone show me how to use a case insensitive Array.Contains within a Linq query? I do NOT want to convert the original string ToUpper() or ToLower() as it is expensive and it changes the original data.

Are you using LINQ2SQL or LINQ2Entities? If so, then that's why it's not supported. — Michael Dunlap, May 26 '15 at 16:31
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, May 26 '15 at 16:33
`ToUpper` is probably not that expensive, why do you think so? — Magnus, May 26 '15 at 16:41
@Magnus Each time we call ToUpper() it creates a temporary string that is created and managed by GC, which takes more time and uses more memory. I also hate changing the original source data from the original state in the database, its confusing to me. — IanK.CO, May 26 '15 at 18:50
@IanK.CO You really should do some tests and see if it really is an issue. — Magnus, May 26 '15 at 20:45

haim770 · Answer 1 · 2015-05-26T17:02:35.510

4

Your first snippet is invoked using Linq to SQL, this means that it will eventually be translated into SQL. So, whether comparison will be case-sensitive or not depends on the COLLATION of your table column. That's why Linq throws the exception, because it cannot guarantee case-sensitivity.

Your second query snippet is performed using Linq to Objects, hence string equality can be enforced, since the actual string is already in-memory.

edited May 26 '15 at 17:02

answered May 26 '15 at 16:34

haim770

48,394
7
105
133

Thanks, that illustrates why the Contains method can't be used on the Linq to SQL class, also the collation of the table didn't occur to me. Thanks @haim770 – IanK.CO May 26 '15 at 18:45

score 2 · Answer 2 · answered May 26 '15 at 16:36

I tested this code using the StringComparer and it works fine and prints out "fOO":

LINQ to Objects is different than LINQ to SQL/Entities. The latter needs to convert your query into a SQL expression, hence it doesn't understand what StringComparsion is.

You can use AsEnumerable() on your query to bring the data in-memory:

var otherMatchingDevices = (from d in selectedDevices
                            from c in mldb.Companies
                            where d.CompanyID == c.CompanyID && c.Deleted == 0)
                            .AsEnumerable()
                            .Where(searchTerms.Contains(d.Name.ToString(), 
                                               StringComparer.CurrentCultureIgnoreCase) || 
                                   searchTerms.Contains(c.CompanyName.ToString(), 
                                               StringComparer.CurrentCultureIgnoreCase))
                            .ToArray()

If one is going to do the filtering in memory (big warning on that as it might be quite slow) I would suggest putting `searchTerms` into a non case sensative hashset. `HashSet(StringComparer.OrdinalIgnoreCase)` — Magnus, May 26 '15 at 16:47
The code here doesn't work but the answer is useful, I get the basic idea and I see the problem now. Thanks @Magnus — IanK.CO, May 26 '15 at 18:41

Case Insensitive Array.Contains in Linq Query

2 Answers2