How to ignore case with LINQ-to-SQL?

Question

I'm having problems with getting data using LINQ-to-SQL. I use the following piece of code to look up a user for our web app (user name is email address):

var referenceUser = 
    db.ReferenceUsers
      .SingleOrDefault(rf => rf.Email == values["emailAddress"]);

If I type test@test.com I get a ReferenceUser however if I type tESt@tESt.com I don't. How can I get LINQ to ignore the case when selecting a user?

Take a look at http://stackoverflow.com/questions/841226/case-insensitive-string-compare-in-linq-to-sql — Brandon Bodnar, Feb 04 '10 at 16:26
How about enabling case insensitivity for that column on the database? We unfortunately still live in times where such fine tuning is necessary. The database has to know if a column is case insensitive or not when enabling indexing for it. — herzmeister, Feb 04 '10 at 16:35

codekaizen · Accepted Answer · 2013-09-03T18:47:41.340

15

Does:

var referenceUser = 
    db.ReferenceUsers.SingleOrDefault(
        rf => rf.Email.ToUpper() == values["emailAddress"].ToUpper());

work?

The ToUpper() should be translated into the correct SQL to run as a database query, and then return both results.

edited Sep 03 '13 at 18:47

answered Feb 04 '10 at 16:23

codekaizen

26,990
7
84
140

That's what I would do if I didn't know there was an ignore case flag, but +1 for idea. – David Brunelle Feb 04 '10 at 16:25
1

@David - how you compare matters in LINQ since the lambda expression sent to SingleOrDefault is translated. This way is sure to be translated into SQL. – codekaizen Feb 04 '10 at 16:28
Works good. However for other string compares that aren't in lambdas should i stick with ToLower() or should I use StringComparison.OrdinalIgnoreCase ?? – Feb 04 '10 at 16:31
@Mike - right, if you're running on the client side, you're better to use: `String.Equals(s1, s2, true)` or a `System.StringComparer` property which compares the way you want. Further, I think you'd rather use `InvariantCultureIgnoreCase` or `CurrentCultureIgnoreCase` instead of `OrdinalIgnoreCase` since the latter compares codepoint-to-codepoint instead of character-to-character. – codekaizen Feb 04 '10 at 17:00
2

Just be aware that calling .ToLower on the db column will ensure that even if it is indexed, SQL Server will not be able to use that index. Changing collation to a case-insensitive collation would be a better choice if the table is large enough to make a table scan a bad thing. – KristoferA Aug 02 '10 at 03:30
2

Due to the weirdness of Unicode, ToUpper() is preferred to ToLower() (there are several Unicode characters that do not have lower-case variants), though there is still an ambiguity (at least) in that Turkish upper-case i is İ (U+0130: Capital I with dot above), to match their lower case I: ı (U+0131, Small Dotless I), but that is not possible to solve without locale-specific comparisons.... – Simon Buchan Sep 08 '11 at 00:05

score 5 · Answer 2 · answered Feb 04 '10 at 16:24

5

var referenceUser = db.ReferenceUsers.SingleOrDefault(rf => string.Compare(rf.Email, values["emailAddress"],true)==0);

Where the "true" is whether to ignore case or not

answered Feb 04 '10 at 16:24

Program.X

7,250
12
49
83

Just as a point of consideration, I don't think this would run on the server, though. I recall using this pattern and not getting the query to be interpreted correctly. – codekaizen Feb 04 '10 at 16:26
5

Apparently by the way this answer is voted up, the SO readership is quite comfortable with .Net idioms, but less so with LINQ on IQueryable. – codekaizen Feb 04 '10 at 16:31

score 0 · Answer 3 · edited Aug 02 '10 at 01:08

0

This is how I did it. Might give you a hint. I have a List with Profiles and I want to search for profilenames containing "aR", "Ar" or "AR".

List<Profile> profileList = CreateProfile();
string search = "aR".ToLower();

profileList = (from p in profileList where p.Name.ToLower().Contains(search)
        orderby p.Name select p).ToList();

edited Aug 02 '10 at 01:08

sth

222,467
53
283
367

answered Aug 01 '10 at 18:44

Skylerr

1

score -1 · Answer 4 · edited Oct 08 '15 at 10:10

-1

listTasks.ItemsSource
    = taskList.Where( x => x.TaskDescription.ToLower()
                            .ToString()
                            .StartsWith(txtSearch.Text.Trim()
                            .ToLower()
                            .ToString()));

edited Oct 08 '15 at 10:10

Codor

17,447
9
29
56

answered Oct 08 '15 at 09:43

Santhosh Naini

21
5

How to ignore case with LINQ-to-SQL?

4 Answers4