SELECT not case sensitive

SELECT not case sensitive

Postby 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?
Miros
 
Posts: 45
Joined: Thu 20 Jan 2011 10:12

Re: SELECT not case sensitive

Postby Shalex » Tue 28 Apr 2015 14:57

https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.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
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Re: SELECT not case sensitive

Postby 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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL