CODEWITHSUNDEEP
entity-framework
arame3333
arame3333

Reputation: 10193

Entity Framework - case insensitive Contains?

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.

Upvotes: 47

Views: 37438

Answers (3)

spajce
spajce

Reputation: 7082

Just add .ToLower() from upper

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

Upvotes: 3

Tomas
Tomas

Reputation: 755

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

Upvotes: 9

Vladislav Kostenko
Vladislav Kostenko

Reputation: 1205

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.

Upvotes: 7

Related Questions