How to make contains method case insensitive in linq to sql

Question

I want to use like type operator in linq to sql, i used contains method to achieve it, but it is case sensitive, suggest me something.

The contains method is case sensitive but I want my query to be case insensitive . I tried to use IndexOf() method also but it is giving exception.

public static List<string> GetDecodedStaticMsg(string filterMsg)
 {
     try
     {
         var q1 = (from decodemsg in Em.StaticMessages
                   where decodemsg.Message.Contains(filterMsg)
                   select
                   decodemsg.MessageId
                   ).ToList();

                  return q1;

     }
     catch (Exception)
     {
                return null;
     }
 }

I believe it's dependent on your database's collation. If your database uses a case-sensitive collation, then your string comparisons will be case-sensitive. — Paul Suart, Apr 11 '19 at 09:09
How about `decodemsg.Message.ToLower().Contains(filterMsg.ToLower())`? — Aleks Andreev, Apr 11 '19 at 09:10
Because to performance issue , its not allowed me to convert my string to lower or upper case , suggest me some other effective way — Apoorva Asthana, Apr 11 '19 at 09:12

score 1 · Answer 1 · answered Apr 11 '19 at 09:15

1

You can use the CompareInfo member from the CultureInfo class, try this :

public static List<string> GetDecodedStaticMsg(string filterMsg)
{
     try
     {
         var q1 = (from decodemsg in Em.StaticMessages
                   where culture.CompareInfo.IndexOf(decodemsg.Message, filterMsg, CompareOptions.IgnoreCase) >= 0
                   select
                   decodemsg.MessageId
                   ).ToList();

                  return q1;

     }
     catch (Exception)
     {
                return null;
     }
}

Reference : https://stackoverflow.com/a/15464440/3759822

answered Apr 11 '19 at 09:15

Fourat

2,366
4
38
53

I doubt this would work for any Linq to SQL provider. – Magnus Apr 11 '19 at 09:20
It is not working as indexof() is not allowed , i shared it in question itself – Apoorva Asthana Apr 11 '19 at 10:12

score 0 · Answer 2 · answered Apr 11 '19 at 09:22

We could use SqlMethods.Like for doing case insensitive search

public static List<string> GetDecodedStaticMsg(string filterMsg)
 {
     try
     {
         var q1 = (from decodemsg in Em.StaticMessages
                   where SqlMethods.Like(decodemsg.Message, $"%{filterMsg}%")  //Changes here
                   select
                   decodemsg.MessageId
                   ).ToList();

                  return q1;

     }
     catch (Exception)
     {
                return null;
     }
 }

How to make contains method case insensitive in linq to sql

2 Answers2