If searching by name, do I need to make the column (string) case sensitive for EF Core query if I ha-CodePudding

If I'm doing a simple search by a user name ex. Mike.

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName == userName);

and the UserName column is indexed, does it matter if I receive the value in a different case (ex. MIKE, MIke, mike)?

Will case insensitivity effect the performance?

Or should I create a normalized column (ex. MIKE, PAUL, BOB) and then take my input and do a userName.ToUpper()?

ex.

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.NormalizedUserName == userName.ToUpper());

CodePudding user response：

In the most applications I've seen, a username is usually not case sensitive, only a password.

if you have a very large user table the query like this

var user = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName.ToLower == userName.ToLower());

will affect performance, since you need to convert each usename to lower.

So IMHO it is a good idea to convert username to lower case when you save a new or update existing user, but you don't need to keep a special column for this. Then you can use the code like this

var user = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName == userName.ToLower());

CodePudding user response：

The behaviour will depend entirely on the collation option selected for the database. SQL Server by default will use a Case-Insensitive (CI) collation so when you write a query against the database, whether EF or manually, the string comparisons will be case-insensitive. However, the minute you go to run your application against a Case-sensitive collation (CS) database then your application will fail to match the strings. (I.e. PostgreSQL I believe uses CS by default, or by running against a _CS Collation SQL Server database.) This was a bit of a surprise on one client site where the development database was _CI while the production server was using a _CS collation.

Leaving SQL Server (or similar DB) to handle case-insensitive searching is fine so long as you ensure the behaviour is documented and mark that the Case-Insensitive collation is a requirement for the system. Performance-wise I don't know of any implied cost of letting SQL Server do the case-insensitive comparison. I would expect that writing all of your queries like:

.FirstOrDefaultAsync(p => p.UserName.ToLower() == userName.ToLower());

... would actually incur a small-to-significant performance cost and potentially negate index usage and potentially other in-build performance measures for query execution in the database.

Otherwise, if the case-insensitivity is a requirement then your application should ensure that case-insensitive fields are always stored consistently in upper or lower case and searched with the same forced case accordingly. If external systems/queries can potentially alter data, the concern here would be that your code would expect a consistent case which the database itself is not enforcing. A check constraint can catch attempts to insert/update mixed-case, or you can use an Instead Of Insert trigger to substitute mixed case /w upper or lower case. Of course, options here would depend entirely on your RDBMS.

CodePudding user response：

Entity Framework by default does not make any collation specification in the generated SQL. Therefore, a query such as

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName == userName);

will simply get translated to something like

SELECT TOP 1 u.*
FROM Users u
WHERE u.UserName = @userName;

There are case-sensitive and case-insensitive collations. The collation can be set on each column differently, although without specifying explicitly when creating it, it would take the database default collation.

Meanwhile, the collation of the variable is that of the current database. But it has lower precedence when being compared to a column reference. So it would all depend on the collation of the column.

On the other hand, a query such as

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.NormalizedUserName == userName.ToUpper());

becomes

SELECT TOP 1 u.*
FROM Users u
WHERE u.NormalizedUserName = UPPER(@userName);

and therefore the case-sensitivity of the collation makes no difference. There are other collation properties which could still be affected though.

Note that you also loose information by normalizing like this, which may or may not be what you want. Collation rules alone do not generally strip case information. This is why it's generally better to just set a case-insensitive collation for such a column, and be done.

Whatever you do, do not do this

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName.ToUpper() == userName.ToUpper());

it would result in

SELECT TOP 1 u.*
FROM Users u
WHERE UPPER(u.UserName) = UPPER(@userName);

which cannot use indexes, and is an all-round bad idea.