How to generate case insensitive LIKE comparision with LINQ?

How to generate case insensitive LIKE comparision with LINQ?

Postby itcore@vit.ch » 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 itcore@vit.ch on Thu 17 Feb 2011 05:22, edited 1 time in total.
itcore@vit.ch
 
Posts: 43
Joined: Wed 17 Sep 2008 11:31

Postby AndreyR » Wed 27 May 2009 08:25

We will investigate the possibility of adding the case-insensitive search option to the Contains() method.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

How about SqlMethods.Like?

Postby edstaffin » Mon 27 Jul 2009 15:02

Is this or will this be supported?
edstaffin
 
Posts: 43
Joined: Mon 13 Oct 2008 13:23

Postby AndreyR » Tue 28 Jul 2009 07:35

I will let you know as soon as the investigation is finished.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Another possible alternative

Postby 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
?
edstaffin
 
Posts: 43
Joined: Mon 13 Oct 2008 13:23

Postby AndreyR » Tue 15 Dec 2009 10:08

We have added the support for case-insensitive comparison in LINQ to MySQL.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
AngelV
 
Posts: 2
Joined: Thu 02 Sep 2010 05:33

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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()));
AngelV
 
Posts: 2
Joined: Thu 02 Sep 2010 05:33

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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
johnb
 
Posts: 6
Joined: Tue 25 May 2010 15:02

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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.
johnb
 
Posts: 6
Joined: Tue 25 May 2010 15:02

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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
johnb
 
Posts: 6
Joined: Tue 25 May 2010 15:02

Next

Return to LinqConnect (LINQ to SQL support)