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.