LINQ To Entities Contains Case In-Sensitive Searching

Question

i am trying to query my resultset like this in linq to entities;

var categoriesList = _catRepo.GetAllCategories();


 filteredCategories = categoriesList.Where(c=> c.CategoryName.Contains("for"));

However, i don't get any result becuase the CategoryName is For(Upper Case) in the database. I have also checked the sql server collation and it is set to _CI_AS. I have no idea how to use contains to filter case insensitive string? I want basically if someone type like;

 filteredCategories = categoriesList.Where(c=> c.CategoryName.Contains("for"));

OR

filteredCategories = categoriesList.Where(c=> c.CategoryName.Contains("For"));

The result should be the same

Does this answer the question? http://stackoverflow.com/questions/3360772/linq-contains-case-insensitive — Matt Roberts, Mar 12 '13 at 13:46
I think that normally works with the collation you say. Are you sure that `categoriesList` is a DB query and not an already queried in memory list (in which case your expression won't be translated to sql). Also, if you trace the db queries at that point, what SQL is being generated? — Jon Egerton, Mar 12 '13 at 13:48
What type is `filteredCategories`? Can you show the variable declaration? — jeroenh, Mar 12 '13 at 13:49
Hmm, theres a few suggestions in that link, so I'm not sure what you mean by by _it_ doesn't work for me.. — Matt Roberts, Mar 12 '13 at 13:49
Does `GetAllCategores` return an IQueryable or an IEnumerable? — Matt Roberts, Mar 12 '13 at 13:51
Right, so this is NOTHING to do with the DB. You're fetching all the entities back from the DB and you want to do an in-memory filter of them. — Matt Roberts, Mar 12 '13 at 13:53

Alex Kovanev · Accepted Answer · 2013-03-12T14:06:17.793

18

Try this

filteredCategories = categoriesList.Where(c=>
 c.CategoryName.IndexOf("for", StringComparison.OrdinalIgnoreCase) >= 0)

Contains method works as shown below

public bool Contains(string value)
{
   return this.IndexOf(value, StringComparison.Ordinal) >= 0;
}

edited Mar 12 '13 at 14:06

answered Mar 12 '13 at 13:49

Alex Kovanev

1,858
1
16
29

10

This won't work if working directly on EF objects. Can't translate `IndexOf()` – Rufix Sep 25 '13 at 13:01
15

Doesn't Work with Entity Framework 5. LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String, System.StringComparison)' method, and this method cannot be translated into a store expression – Jatin Nov 24 '13 at 12:51

score 7 · Answer 2 · answered Mar 12 '13 at 13:57

7

The previous IndexOf answer should work. Because you are loading all the entities from the database and then doing an in-memory (linq to objects) filter on it, you are not doing anything on the database at all.

This should also work (from the post I referenced)

filteredCategories = categoriesList.Where(c=> c.CategoryName.ToLower().Contains("for"));

Just an aside, if you have a lot of categories, then you might want to filter them on the database, rather than fetch all from the db and then filter them in memory..

answered Mar 12 '13 at 13:57

Matt Roberts

26,371
31
103
180

Contains will call IndexOf anyways – Alex Kovanev Mar 12 '13 at 14:02
@Shymep Yup I know, I was just offering this one up for completeness :) – Matt Roberts Mar 12 '13 at 14:05
2

This doesn't pass the Turkey Test http://www.moserware.com/2008/02/does-your-code-pass-turkey-test.html – Odys Mar 13 '14 at 01:11

LINQ To Entities Contains Case In-Sensitive Searching

2 Answers2

Linked