45

I want a solution to this problem that does not involve ToUpper or ToLower, as I use in the code below;

var upper = term.ToUpper();
using (var db = this.DataContext)
{
    return db.Counties.Where(x => x.CountyName.ToUpper().Contains(upper)).ToList();
}

I am using entitly framework so the C# solution of using StringComparison.CurrentCultureIgnoreCase does not work. It does work for Equals, EndsWith and StartsWith, but not Contains.

Cœur
  • 37,241
  • 25
  • 195
  • 267
arame3333
  • 9,887
  • 26
  • 122
  • 205
  • Perhaps this answer is one you can employ: http://stackoverflow.com/a/444818/3312 – Jesse C. Slicer Jan 18 '13 at 14:50
  • possible duplicate of [LINQ Contains Case Insensitive](http://stackoverflow.com/questions/3360772/linq-contains-case-insensitive) or [Case insensitive contains(string)] (http://stackoverflow.com/questions/444798/case-insensitive-containsstring) – Jesse C. Slicer Jan 18 '13 at 14:50
  • 2
    Neither of them apply. Those solutions do not work in entity framework as I tried to point out in the second paragraph of the question. – arame3333 Jan 18 '13 at 15:39
  • Sorry bout that. How's about this: http://stackoverflow.com/a/3843382/3312 ? It's the reverse, but the solution is the same - change the collation on the column in the database. – Jesse C. Slicer Jan 18 '13 at 15:48
  • 9
    I think you are wrong to close this question as an exact duplicate. I appreciate that it may look like it is, but the solutions do not work in Entity Framework, that was why I asked it. – arame3333 Jan 19 '13 at 20:01
  • Did you ever figure out the solution to this? – tofutim Jan 20 '14 at 17:15
  • 6
    I have voted to reopen this question as it asks about Entity Framework specifically. The implementation of `String.Contains` is different for different providers, for example Linq2Sql is always case insensitive. – dav_i Feb 05 '14 at 11:17
  • Have you considered using Dynamic Linq for this? See http://blog.falafel.com/implement-case-insensitive-string-comparisons-with-dynamic-linq/ for more details – B2K Nov 19 '15 at 16:59
  • Possible duplicate of [Entity Framework and Case Insensitive String Search](http://stackoverflow.com/questions/7415267/entity-framework-and-case-insensitive-string-search) – Ehsan Sajjad Jul 16 '16 at 18:06

3 Answers3

8

I know that this is not related directly to EF, but only solution I can think of is to alter DB table column collation to be Case Insensitive e.g.:

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME nvarchar(100) COLLATE Latin1_General_CI_AS NULL

CI - case insensitive / CS - case sensitive

AS - accent sensitive / AI - accent insensitive (can also be useful)

If you can't change collation of table column you can use direct query from EF with forcing collation

select * 
from table
where country collate Latin1_General_CI_AS != @country
Tomas
  • 675
  • 8
  • 17
6

I use EF6 and Sql Server and Contains is mapped to LIKE '%@p0%' which is case insensitive in my case. So in my case:

db.Counties.Where(x => x.CountyName.Contains(term)).ToList();

works as needed. More info in Sjoerd answer.

Community
  • 1
  • 1
Vladislav Kostenko
  • 1,155
  • 11
  • 18
  • 20
    In your case means only that you have configured Case Insensitive DB collation. Nothing more, nothing less. – Tomas Sep 06 '15 at 20:30
  • 1
    In my case, I can run your query and it is case sensitive. So I think @Tomas has it figured, But would be awesome if EF could add a feature to specify. Cause one could want case sensitive in 1 query and then case insensitive in another. – Zapnologica Jul 17 '16 at 11:04
  • 2
    misleading answer – ihor.eth Feb 11 '21 at 21:48
3

Just add .ToLower() from upper

 using (var db = this.DataContext)
            {
                return db.Counties
                       .Where(x => x
                       .CountyName.ToLower()
                       .Contains(upper.ToLower())).ToList();
            }
Chris Owens
  • 5,076
  • 12
  • 61
  • 131
spajce
  • 7,044
  • 5
  • 29
  • 44