Case sensitive transaction type hint

C# developers are used to the fact the language is case sensitive.

    private void MyMethod()
    {
        // body 1
    }
    private void myMethod()
    {
        // body 2
    }

The above code is valid because the name of the two methods differs. 'm' is not the same as 'M'.

    string str1 = "Tesco";
    string str2 = "TESCO";

    int result1 = string.Compare(str1, str2); // case sensitive
    int result2 = string.Compare(str1, 
        str2, StringComparison.CurrentCultureIgnoreCase); // case insensitive

Moreover, if you compare two strings then the default comparison is also case sensitive. An additional parameter is needed for case insensitive comparison.

That's why it was a surprise when I wanted to add a new Transaction type hint it failed. The hint "TESCO" was already in the table but I wanted to add "Tesco". Sometimes it makes sense to differentiate hints based on case. For example, when I buy in a Hungarian Tesco then the comment contains "TESCO" and the category should be "Expense.Living.Food.Ingredients" but when I buy in Tesco in another country then the comment contains "Tesco" and the category should be "Expense.Living.Entertainment.Free time, holiday".

The hint column has a unique constraint in the database. It means that the table can not have two rows with the same hint. But "TESCO" and "Tesco" are different, aren't they?

No, they are not according to the MS Sql Server. When a table column is created a collation should be given. The collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. If no collation is given, this is what I have done sor far, then the default collation is used "SQL_Latin1_General_CP1_CI_AS" where CI means case insensitive.

There are two questions now. Can we change the collation of the given column, can entity framework do the same?

  ALTER TABLE YourTableName
  ALTER COLUMN YourColumnName VARCHAR(255) COLLATE NewCollationName;

The answer for the first question is yes. The script above can change the collation of a specific column but you have to drop and recreate the unique index as well.

The answer is also yes for the second question but it was not straightforward for me. Entity framework correctly figures out that the unique index must be dropped before changing the collation and then recreated.

Update-Database -context MainDbContext

Unfortunately, the Update-Database script failed. The error message said the index does not exist or there is no permission for its change. It turned out that the index name was IX_Hint instead of IX_TransactionTypeHint_Hint. I have a feeling that at some point I thought IX_TransactionTypeHint_Hint is too verbose for index name and I changed it manually.

 public partial class DropHintIndex : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropIndex(
                // name: "IX_TransactionTypeHint_Hint",
                name: "IX_Hint",
                table: "TransactionTypeHint");
        }

The migration file had to be changed and after that the database update was successful.

Case sensitive transaction type hint

Contact Form