How to generate case insensitive LIKE comparision with LINQ?

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
[email protected]
Posts: 43
Joined: Wed 17 Sep 2008 11:31

How to generate case insensitive LIKE comparision with LINQ?

Post by [email protected] » Tue 26 May 2009 15:22

Hello

The following LINQ statement...

Code: Select all

 (from m in PPCustomer.Mailings
where m.Subject.Contains("test") 
select m).Count();
creates this SQL code:

Code: Select all

SELECT COUNT(*) AS C1
FROM tbl_mailings t1
WHERE  BINARY t1.`Subject` LIKE '%test%'
How is it possible to generate SQL code without the BINARY keyword in order to enable case insensitive search.. like this:

Code: Select all

SELECT COUNT(*) AS C1
FROM tbl_mailings t1
WHERE  t1.`Subject` LIKE '%test%'
My suggestion would be this..

Code: Select all

 (from m in PPCustomer.Mailings
where m.Subject.Contains("test", StringComparer.CurrentCultureIgnoreCase) 
select m).Count();
but unfortunately it's not possible to pass a StringComparer to the Contains() method. Any ideas?

Best regards
________
VFR800
Last edited by [email protected] on Thu 17 Feb 2011 05:22, edited 1 time in total.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 27 May 2009 08:25

We will investigate the possibility of adding the case-insensitive search option to the Contains() method.

edstaffin
Posts: 43
Joined: Mon 13 Oct 2008 13:23

How about SqlMethods.Like?

Post by edstaffin » Mon 27 Jul 2009 15:02

Is this or will this be supported?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 28 Jul 2009 07:35

I will let you know as soon as the investigation is finished.

edstaffin
Posts: 43
Joined: Mon 13 Oct 2008 13:23

Another possible alternative

Post by edstaffin » Tue 28 Jul 2009 17:45

How about just using
from m in PPCustomer.Mailings
where m.Subject.ToUpper.Contains(Cstr("test")).ToUpper
select m
?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 15 Dec 2009 10:08

We have added the support for case-insensitive comparison in LINQ to MySQL.

AngelV
Posts: 2
Joined: Thu 02 Sep 2010 05:33

Post by AngelV » Thu 02 Sep 2010 05:48

AndreyR wrote:We have added the support for case-insensitive comparison in LINQ to MySQL.
Hello

I have the same problem and I have tried to find ways to use this type of comparison but can not find it, how access to this or how I can use it?. Have you an example?

I have dotConnect for MySQL 5.80.146.0

Thank you.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 02 Sep 2010 15:34

At the moment, the comparison is case-sensitive/case-insensitive depending on the settings in the database. I.e., as MySQL servers are case-insensitive by default, strings are compared case-insensitively too. If the server is set to be case-sensitive, the simplest way to make your query case-insensitive is to use the ToLower/ToUpper method on the strings used:

Code: Select all

var query = from item in MyDataContext.Tables
	    where item.StringColumn.ToLower() == "Value".ToLower()
	    select item;
Feel free to contact us if something is unclear.

AngelV
Posts: 2
Joined: Thu 02 Sep 2010 05:33

Post by AngelV » Tue 07 Sep 2010 17:11

StanislavK wrote:At the moment, the comparison is case-sensitive/case-insensitive depending on the settings in the database. I.e., as MySQL servers are case-insensitive by default, strings are compared case-insensitively too. If the server is set to be case-sensitive, the simplest way to make your query case-insensitive is to use the ToLower/ToUpper method on the strings used:

Code: Select all

var query = from item in MyDataContext.Tables
	    where item.StringColumn.ToLower() == "Value".ToLower()
	    select item;
Feel free to contact us if something is unclear.
I thought that your solution would simply implicate to omit in your routines
the parameter "BINARY" so that the function LIKE could perform alone,
so that's ok, I just implemented the following to keep using the LIKE
function itself. Thanks

Code: Select all

query = query.Where(t => t.Department.ToLower().Contains(name.ToLower()));

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 08 Sep 2010 14:03

Glad to hear the issue is resolved.
Feel free to contact us if you encounter any other problems with our components.

johnb
Posts: 6
Joined: Tue 25 May 2010 15:02

Post by johnb » Sun 10 Oct 2010 11:47

Hi AndreyR
You mentioned that in a previous post that implemented contains to allow case insensitive searches. I am unsure i can enable this as it always seems to select BINARY in ther SQL. Is there a trick to enabling this. StartsWith work well when you put the string comparision to caseinsenstive.

Thanks

John

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 11 Oct 2010 12:08

At the moment, string comparison like 'string1 == string2' depends on the DBMS used. E.g., for MySQL it is case-insensitive. The String.Contains method, however, always treats strings case-sensitively.

To perform case-insensitive search including the LIKE operator, one needs to cast both strings being compared to either upper or lower registry, e.g.

Code: Select all

from item in MyDataContext.Depts where item.Dname.ToLower().Contains(stringParameter.ToLower()) select item;
Please tell us if this helps.

johnb
Posts: 6
Joined: Tue 25 May 2010 15:02

Post by johnb » Tue 12 Oct 2010 19:47

Thats very unfortunate as a ToLower does a whole table scan and the table I am using is a big table. I am allowing the user to prefilter based up[on text enetered, the enetered value is not case sensistive and it is data like names - "mark", "Mark" & "MARK" should always be returned regardless of what is enetered - i.e sql query which has LIKE "%mArk%" should return all "marks" regardless of case.

When I checked the generated sql from a Contains it puts the word BINARY in the sql. It did the same with StartWith the same thing until I said case insensistve using the second option then the binary disappered. Is it not possible to so the same.

All that I am after is the server not to treat it as a Binary search (remove the BINARY keyword) to apply the sql corrcetly to get the desired result as you would when you execute a query normally through a query interface.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 13 Oct 2010 15:55

We have implemented the possibility of control whether the binary comparison will or will not be used. For the LIKE clause, it is now possible to use the String.IndexOf(string, StringComparison) overload with the StringComparison.OrdinalIgnoreCase second argument to disable binary comparison. In the String.Equals(string, StringComparison) method, the StringComparison.Ordinal value can be used to force using BINARY.

These changes will be available in the nearest build which we plan to release in several days.

johnb
Posts: 6
Joined: Tue 25 May 2010 15:02

Post by johnb » Thu 14 Oct 2010 11:34

Hi StanislavK
Thanks for the reply and look forward to the update as my cleint was asking why when the do a search it was case sensitive.

Thanks again

John

Post Reply