CODEWITHSUNDEEP
c#linqlinq-to-sqlcase-insensitive
yeomandev
yeomandev

Reputation: 11806

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

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().

Upvotes: 0

Views: 2115

Answers (2)

Dylan Beattie
Dylan Beattie

Reputation: 54150

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.

Upvotes: 2

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

AFAIR string comparison in SQL is case insensitive itself.

Upvotes: 1

Related Questions