MySQL Case Sensitive Search in Entity Framework

A high level abstraction provided by Entity Framework sometimes challenges developers in accessing a full set of features available via native SQL of a particular database server. This article will help you to cope with usage of collation in MySQL to control case sensitivity settings in generated queries.

Collation defines an order through the process of comparing two given character strings and deciding which should come before the other.

The MySQL Server documentation says:

“For nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands. For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.
The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default.”

There are two alternative ways to make the search be case sensitive:

• one of the operands has a case sensitive or binary collation (e.g.: it can be set via COLLATE in a column declaration)
• the COLLATE operator is used in a comparison operation

The Entity Framework implementation in dotConnect for MySQL includes the following features:

• MySqlFunctions.Collate method for including the COLLATE operator with a necessary collation in generated SQL
• config.QueryOptions.ComparisonCollation option to use the COLLATE operator with a required collation when comparing strings in “equal” and “not equal” cases
• config.QueryOptions.LikeCollation option to use the COLLATE operator with a specified collation for .Contains/.StartsWith/.EndsWith methods

Here is an example.

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);
INSERT INTO DEPT VALUES (10,'New Department','Seattle');
INSERT INTO DEPT VALUES (11,'NEW DEPARTMENT','Seattle');
INSERT INTO DEPT VALUES (12,'new department','Seattle');

SELECT * FROM DEPT WHERE DNAME LIKE 'n%'

returns 3 rows

SELECT * FROM DEPT WHERE DNAME COLLATE latin1_bin LIKE 'n%'

returns 1 row

SELECT * FROM DEPT WHERE DNAME COLLATE latin1_bin LIKE 'N%'

returns 2 rows

You can use the dbMonitor tool to enable tracing of the database activity.

Here is an example, showing how to use MySqlFunctions.Collate with column in comparison or in .Contains/.StartsWith/.EndsWith locally (in separate LINQ queries):

C#

        var monitor = new Devart.Data.MySql.MySqlMonitor() { IsActive = true };

        using (MyDbContext context = new MyDbContext()) {

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

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

            var like_query = context.Depts.Where(d => MySqlFunctions.Collate(d.DNAME, "latin1_bin").Contains("New"));
            var like_results = like_query.ToList();
        }

VB

        Dim monitor As New Devart.Data.MySql.MySqlMonitor
        monitor.IsActive = True

        Using context As New MyDbContext

            Dim equals_query = context.Depts.Where(Function(d) MySqlFunctions.Collate(d.DNAME, "latin1_bin") = "New Department")
            Dim equals_results = equals_query.ToList()

            Dim not_equals_query = context.Depts.Where(Function(d) MySqlFunctions.Collate(d.DNAME, "latin1_bin")  "New Department")
            Dim not_equals_results = not_equals_query.ToList()

            Dim like_query = context.Depts.Where(Function(d) MySqlFunctions.Collate(d.DNAME, "latin1_bin").Contains("New"))
            Dim like_results = like_query.ToList()

        End Using

If you want, you can set collation globally (in current AppDomain):

C#

        var monitor = new Devart.Data.MySql.MySqlMonitor() { IsActive = true };

        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.DNAME == "New Department");
            var equals_results = equals_query.ToList();

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

            var like_query = context.Depts.Where(d => d.DNAME.Contains("New"));
            var like_results = like_query.ToList();
        }

VB

        Dim monitor As New Devart.Data.MySql.MySqlMonitor
        monitor.IsActive = True

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

        Using context As New MyDbContext

            Dim equals_query = context.Depts.Where(Function(d) d.DNAME = "New Department")
            Dim equals_results = equals_query.ToList()

            Dim not_equals_query = context.Depts.Where(Function(d) d.DNAME  "New Department")
            Dim not_equals_results = not_equals_query.ToList()

            Dim like_query = context.Depts.Where(Function(d) d.DNAME.Contains("New"))
            Dim like_results = like_query.ToList()
        End Using