SELECT not case sensitive

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Miros
Posts: 45
Joined: Thu 20 Jan 2011 10:12
Contact:

SELECT not case sensitive

Post by Miros » Fri 24 Apr 2015 08:24

When running the following query (lowercase "john") on MySQL directly using SQL:
SELECT * FROM `User` WHERE `Name` = 'john';

- Or LINQ on Devart Entity Model in C#:

Code: Select all

entities.Users.SingleOrDefault(user => user.Name = "john");
I get a user with Name = "John" (capital J). Obviously our MySql server must be configured somehow to behave like this.

My question is: Is it possible to force case sensitivity using LINQ with the Devart Entity Model?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SELECT not case sensitive

Post by Shalex » Tue 28 Apr 2015 14:57

https://dev.mysql.com/doc/refman/5.7/en ... ivity.html:

Code: Select all

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);
INSERT INTO DEPT VALUES (10,'Accounting_0','New York');
INSERT INTO DEPT VALUES (11,'Accounting_1','NEW YORK');
INSERT INTO DEPT VALUES (12,'Accounting_2','new york');

SELECT * FROM dept WHERE LOC LIKE 'n%'
returns 3 rows

SELECT * FROM dept WHERE LOC COLLATE latin1_bin LIKE 'n%'
returns 1 rows

SELECT * FROM dept WHERE LOC COLLATE latin1_bin LIKE 'N%'
returns 2 rows
Starting from the next build of dotConnect for MySQL, the following features will be available:
  • The MySqlFunctions.Collate method for including the COLLATE operator with a necessary collation in generated SQL is added
  • The config.QueryOptions.ComparisonCollation option is implemented to use the COLLATE operator with a required collation when comparing strings in "equal" and "not equal" cases
  • The config.QueryOptions.LikeCollation option is added to use the COLLATE operator with a specified collation for .Contains/.StartsWith/.EndsWith methods
The usage of MySqlFunctions.Collate with column in comparison or in .Contains/.StartsWith/.EndsWith locally (in separate LINQ queries):

Code: Select all

      using (MyDbContext context = new MyDbContext()) {

        var equals_query = context.Depts.Where(d => MySqlFunctions.Collate(d.Loc, "latin1_bin") == "New York");
        var equals_results = equals_query.ToList();

        var not_equals_query = context.Depts.Where(d => MySqlFunctions.Collate(d.Loc, "latin1_bin") != "New York");
        var not_equals_results = not_equals_query.ToList();

        var like_query = context.Depts.Where(d => MySqlFunctions.Collate(d.Loc, "latin1_bin").Contains("New"));
        var like_results = like_query.ToList();
      }
Setting MySqlFunctions.Collate globally (in current AppDomain):

Code: Select all

      var config = Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfig.Instance;
      config.QueryOptions.LikeCollation = "latin1_bin";
      config.QueryOptions.ComparisonCollation = "latin1_bin";

      using (MyDbContext context = new MyDbContext()) {

        var equals_query = context.Depts.Where(d => d.Loc == "New York");
        var equals_results = equals_query.ToList();

        var not_equals_query = context.Depts.Where(d => d.Loc != "New York");
        var not_equals_results = not_equals_query.ToList();

        var like_query = context.Depts.Where(d => d.Loc.Contains("New"));
        var like_results = like_query.ToList();
      }
We will notify you when the corresponding build of dotConnect for MySQL is available for download.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SELECT not case sensitive

Post by Shalex » Thu 07 May 2015 13:08

The new build of dotConnect for MySQL 8.3.407 is available for download now: http://forums.devart.com/viewtopic.php?f=2&t=31742. Please try it and notify us about the result.

Post Reply