Page 1 of 1
SELECT not case sensitive
Posted: Fri 24 Apr 2015 08:24
by Miros
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?
Re: SELECT not case sensitive
Posted: Tue 28 Apr 2015 14:57
by Shalex
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.
Re: SELECT not case sensitive
Posted: Thu 07 May 2015 13:08
by Shalex
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.