Issue with Case Sensitivity in Urls.Key Generation

First and foremost, I would like to extend my sincere thanks and appreciation for developing the Tedd.ShortUrl package! Here's an important issue I found.

Description

When using dbContext.Database.Migrate() to create tables in Tedd.ShortUrl, the Urls.Key column is not created as case sensitive. This leads to an issue where different keys that only vary in letter casing return the same record from the SQL database. For example, KzZzkh and kzZzkh are treated as the same key, which is not intended behavior.

Steps to Reproduce

Execute dbContext.Database.Migrate() to create the database schema.
Insert records in the Urls table with keys that differ only in their casing (e.g., KzZzkh and kzZzkh).

Perform a lookup for these keys using the following query:

var item = await _dbContext.Urls.FirstOrDefaultAsync(u => u.Key == key);

Observe that both keys return the same record.

Current Behavior

The SQL database treats keys KzZzkh and kzZzkh as identical due to case insensitivity in the Urls.Key column.

Expected Behavior

The database should treat KzZzkh and kzZzkh as distinct keys due to their different casings.

Potential Solution

I was able to resolve this issue manually using the following SQL script:

-- Drop the existing index
DROP INDEX IX_Urls_Key ON dbo.Urls;

-- Alter the column to use a case-sensitive collation
ALTER TABLE dbo.Urls
ALTER COLUMN [Key] nvarchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL;

-- Recreate the index
CREATE UNIQUE NONCLUSTERED INDEX IX_Urls_Key ON dbo.Urls
(
    [Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE LOCKS = ON) ON [PRIMARY];

Additionally, to integrate this solution into the migrationBuilder.CreateTable call, the following code can be added after the CreateTable call:

migrationBuilder.AlterColumn<string>(
    name: "Key",
    table: "Urls",
    type: "nvarchar(10)",
    nullable: false,
    collation: "SQL_Latin1_General_CP1_CS_AS",
    oldClrType: typeof(string),
    oldType: "nvarchar(10)"
);

This alteration ensures that the 'Key' column is created with case sensitivity, which is not the default behavior in MSSQL.

tedd / Tedd.ShortUrl