Case Sensitive Search

In This Topic

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:

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

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

MySqlFunctions.Collate method for including the COLLATE operator with a necessary collation in generated SQL
config.QueryOptions.ComparisonCollation and config.QueryOptions.LikeCollation options, which set collation globally (in current AppDomain) for the COLLATE operator in in "equal"/"not equal" cases and in .Contains/.StartsWith/.EndsWith methods respectively.

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.

        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 Basic


        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:

        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 Basic


        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