My Software Notes

Useful things I discover

Entity Framework: Is Contains() (aka ‘Like’) case insensitive for SQL Server queries?

with 3 comments

Just a note to self.

The Contains method on String when used in an Entity Framework “where” clause is case insensitive (if the server is set to ignore case).

Example:

Assume the “NoteContent” column on the table “Notes” is a varchar column. If I run the following Linq query against the database where db is a DbContext then I get the same result every time.

var num = db.Notes.Where(n => n.NoteContent.Contains(“NoTe”)).Count();

var num = db.Notes.Where(n => n.NoteContent.Contains(“NOTE”)).Count();

var num = db.Notes.Where(n => n.NoteContent.Contains(“note”)).Count();

All produce the same value for “num”.

I’m making this note to self because I keep forgetting and because I keep seeing people do this:

var someString = “whatever”;

var num = db.Notes.Where(n => n.NoteContent.ToUpper().Contains(someString.ToUpper())).Count();

You will need to do this if the underlying SQL Server has been set to be case sensitive.

Thanks to the people who added comments, correcting my original post that said it didn’t matter.

Advertisements

Written by gsdwriter

December 2, 2014 at 9:53 am

Posted in .NET, Database, LINQ

3 Responses

Subscribe to comments with RSS.

  1. Need CaseSensitiveeeee ! 🙂

    psygnosisxxx

    August 7, 2015 at 7:26 am

  2. Your note is wrong. Case sensitivity depends on the collation of the table. I have the problem of an case insensitive MSSQL table where I sometimes want case sensitivity from a Linq query. In SQL I can say “… LIKE ‘%xxxxx%’ COLLATE Latin1_General_BIN” but it seems that only this .ToUpper() stuff could/should work.

    TomTom

    May 10, 2017 at 3:13 am

  3. Excellent point! So, we should use the ToUpper method if there is any doubt about the case sensitivity settings of the underlying SQL Server.

    gsdwriter

    May 14, 2017 at 9:41 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: