How to generate case insensitive LIKE comparision with LINQ?

[StanislavK]

We have released the 5.80.180 build of dotConnect for MySQL with the support for String.IndexOf(string, StringComparison). You can download it from
http://www.devart.com/dotconnect/mysql/download.html
(the trial version only) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information about the improvements and fixes available in dotConnect for MySQL 5.80.180, please refer to
http://www.devart.com/forums/viewtopic.php?t=19238

The new 1.0.42 build of LinqConnect is available as well, its trial version is available at
http://www.devart.com/linqconnect/download.html

For the detailed information about LinqConnect 1.0.42, please see
http://www.devart.com/forums/viewtopic.php?t=19244

[johnb]

Hi StanislavK

I tried the code you suggested but it dosent generate a like statement as expected. The SQl generate is

Code: Select all

WHERE (INSTR(t10.org_name, :p0)-1)  0  ORDER BY 

the line I used is

Code: Select all

            _JobModelViewCollection = _JobModelViewCollection.Where(Function(j) j.Client.IndexOf(ClientName, StringComparison.OrdinalIgnoreCase))

Am I doing soeming wrong or is the expected behaviuor because the query returns nothing which is not the case.

JOhn

[StanislavK]

In the query you've specified, Visual Basic implicitly converts the result of IndexOf() to boolean, hence it is the same as "j.Client.IndexOf(...) 0", meaning that the Client field does not start with the ClientName literal.

Only queries like "j.Client.IndexOf(...) >= 0" are translated using the LIKE operator; please try executing

Code: Select all

_JobModelViewCollection.Where(Function(j) j.Client.IndexOf
       (ClientName, StringComparison.OrdinalIgnoreCase) >= 0)

and tell us if this helps.

[johnb]

Hi StanislavK

Thanks for that it generated the corrcet sqL with a LIKE.

Quick question, how come this wasnt implemented using Contains which is a bit less obscure and consistent with StartWith etc?

Thanks again

John

[StanislavK]

Thank you for your suggestion, we will consider supporting the String.StartsWith(string, StringComparison) overload.

As for the String.Contains() method, the problem is that it has no overloads with a StringComparison argument.

[StanislavK]

You can use StringComparison.CurrentCultureIgnoreCase instead of StringComparison.OrdinalIgnoreCase as the second argument in the StartsWith/EndsWith methods to generate a command with case-insensitive LIKE.

[unclelem]

We seem to have run into this problem again. (note that I've read the above discussion and understand it)

Running a simple query:

Code: Select all

var query = from it in session.GetTable<Doc>()
                   where it.Id.StartsWith(prefix, StringComparison.CurrentCultureIgnoreCase)
                   select it;

generates the following SQL

Code: Select all

SELECT t1.Id, ...
FROM Docs t1
WHERE BINARY t1.Id LIKE :p0 LIMIT :p1 , :p2
-- p0: Input VarChar (Size = 7; DbType = AnsiString) [Docs/6%]
...
-- Context: Devart.Data.MySql.Linq.Provider.MySqlDataProvider Mapping: FluentMappingSource Build 4.5.816.0

Where Id is the primary key of Doc - a varchar[40]

Code: Select all

entity.FullTableName("Docs").PrimaryKey(p => p.Id);
entity.Property(p => p.Id).ServerDataType("VARCHAR(40) NOT NULL");

Unfortunately this query performs a full table scan instead of using the already-existing primary index.
Supposedly using StringComparison.CurrentCultureIgnoreCase should eliminate the BINARY keyword here, but it apparently does not.

Did something break?
Could it be somehow confused because the DB default charset is utf8 ?

I can fix this by creating another (FULLTEXT) index, but I would much rather not, as the table is huge.

Any suggestion would be welcome.

Edit:
... and to answer my own question (and yes, I think it is a bug - likely in your query generator):

My class defines Id as a string. However if I change my class definition to String (capitalized) it works as expected. Since string is an alias of String, I would expect either to work, however only one of them does.

If nothing else, I hope this saves somebody a bunch of debugging.

Thanks

[MariiaI]

We couldn't reproduce this error in our environment with the latest build of LinqConnect 4.5.881. We are sending you a small test project to the e-mail address you provided in your forum profile. Please check that is not blocked by your mail filter. Please test it and notify us about the results.
If this doesn't help, please modify it so that the issue could be reproduced and send it back to us, or send us your sample project (with the necessary DDL/DML scripts).