dotConnect for MySQL Documentation
Case Sensitive Search
support@devart.com

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. In this topic we will show how to use collation in MySQL in order to control case sensitivity settings in Entity Framework-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:

dotConnect for MySQL offers the following ways to access this functionality from Entity Framework:

Here are examples of using this functionality:

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);
INSERT INTO DEPT VALUES (15,'New Department','Denver');
INSERT INTO DEPT VALUES (16,'NEW DEPARTMENT','Denver');
INSERT INTO DEPT VALUES (17,'new department','Denver');
	

The following SELECT statement returns three rows:

SELECT * FROM DEPT WHERE DNAME LIKE 'n%'

The following SELECT statement returns one row:

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

And the following one returns two rows:

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

Run the dbMonitor tool to enable tracing of the database activity and see the queries, generated by our Entity Framework examples.

The following example demonstrates using the MySqlFunctions.Collate method in comparison or in the .Contains/.StartsWith/.EndsWith methods.

C#csharpCopy Code
        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();
        }
Visual BasicCopy Code
        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

And the following example uses config.QueryOptions.ComparisonCollation and config.QueryOptions.LikeCollation options to set the collation globally:

C#csharpCopy Code
        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();
        }
Visual BasicCopy Code
        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

See Also

Entity Framework Support  | Query Options