How can I get .ToUpper() (or the equivalent) to work when using .Contains() in Linq to Sql?

Question

This code:

keyword = 's';
IEnumerable<Member> searchResults = 
        (from m in members
         where m.ScreenName.ToUpper().Contains(keyword.ToUpper())
         select m).AsEnumerable();

Only returns records that have an uppercase 'S' in the ScreenName. Why is m.ScreenName.ToUpper() being ignored? How do I convert m.ScreenName to uppercase so I can perform case-insensetive checks?

Edit

This is not a duplicate. I attempted the solution in the proposed duplicate:

IEnumerable<Member> searchResults = 
       (from m in members
        where m.ScreenName.Contains(keyword, StringComparison.OrdinalIgnoreCase)
        select m).AsEnumerable();

This failed. StringComparison.OrdinalIgnoreCase is not a valid parameter for .Contains().

possible duplicate of [Case insensitive string compare in LINQ-to-SQL](http://stackoverflow.com/questions/841226/case-insensitive-string-compare-in-linq-to-sql) — BlueRaja - Danny Pflughoeft, Jan 03 '11 at 19:56
@BlueRaja and @Danny - Thanks for the link. This is not a duplicate. That method is used on string.equals. I am using the .Contains() method. Please see edit. — quakkels, Jan 03 '11 at 20:21

score 2 · Accepted Answer · answered Jan 03 '11 at 20:57

There is no way (AFAIK) to specify case sensitivity/insensitivity explicitly via Linq-to-SQL.

I would recommend modifying your database schema to use a case-insensitive collation on the column(s) you're searching.

SQL Server collation settings control things like case sensitivity, accent sensitivity (is 'cafe' the same as 'café' ?), and the rules used to perform string comparison and sorting.

Collations are selected from a list built in to SQL Server, and most of them will be called something like SQL_Latin1_General_CP1_CI_AI

The last two pairs of characters are the important bit - CI_AI means case-insensitive, accent-insensitive. I suspect you'll find the the collation on the ScreenName column is set to something like CS_AI or CS_AS. Change this, and your query should work correctly.

Jahan Zinedine · Answer 2 · 2011-01-03T20:05:03.770

1

AFAIR string comparison in SQL is case insensitive itself.

edited Jan 03 '11 at 20:05

answered Jan 03 '11 at 19:57

Jahan Zinedine

14,616
5
46
70

Then why am I getting case sensitive results? – quakkels Jan 03 '11 at 20:20
1

Probably It's because of `collation` you set in your DB. – Jahan Zinedine Jan 03 '11 at 20:26
I am finding this to be a bit frusterating. I am wondering how to make .ToUpper() work correctly. If .ToUpper() is not an option in this case then I am looking for an equivalent solution. I have not set a collation value in the database. I don't know what collation does. If it is the case that collation has been set then I am probably stuck with it that way. So, is there a way to use `.Contains()` in a case-insensitive way? – quakkels Jan 03 '11 at 20:33
1

Look at this page http://msdn.microsoft.com/en-us/library/ms144250.aspx and see your DB's collation , then you may find out the issue. – Jahan Zinedine Jan 03 '11 at 20:38
How does this page help me get `.Contains()` and `.ToUpper()` to work correctly? – quakkels Jan 03 '11 at 20:52
2

The problem has nothing to do with `.Contains`, the actual query is being done in the database and it's the database query that is being case sensitive due to collation. One test would be: If you use SQL Server Management Studio, are queries you run in it case sensitive? – vcsjones Jan 03 '11 at 20:58

How can I get .ToUpper() (or the equivalent) to work when using .Contains() in Linq to Sql?

Edit

2 Answers2