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.

returns 3 rows

returns 1 row

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#

VB

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

C#

VB
Leave a Comment