Checking if usernames exists, case-sensitive

Question

public bool CheckUsername(string username)
{
    bool exists=false;

    var query =
        from t in db.Coaches
        where String.Equals(t.user_name,username)
        select t;


    List<Coach> coaches = query.ToList();
    if (coaches.Count!=0)
    {
        exists = true;
    }
    else
    {
        exists = false;
    }
    return exists;
}

This is my function. If for example the username james exists I want the users to still be able to create another user with username James, JAMES, ... etc for as far as I know String.equals() is case-sensitive but my function does not work. Any idea why this isn't working? I tried using compare but this does not work because it returns an int.

"my function does not work" doesn't tell us anything. Additionally, the second half of your code is equivalent to `return query.Any();`. Indeed, your whole method could be converted to `return db.Coaches.Any(t => t.user_name == username);` — Jon Skeet, Jan 23 '15 at 16:01
@JonSkeet actually it works , but it does not give back what I want. the comparison is invalid. I want that "james" != "James" — nutellaFreak, Jan 23 '15 at 16:04
If it doesn't do what you want, then it *doesn't* work - but you haven't clearly described what *does* happen. — Jon Skeet, Jan 23 '15 at 16:05
assuming "db" is EF, here's explanation how to do it: http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison — Visar, Jan 23 '15 at 16:08
@JonSkeet I'm sorry for the misunderstanding, in the future I will describe my problems better. — nutellaFreak, Jan 23 '15 at 16:12

score 3 · Accepted Answer · answered Jan 23 '15 at 16:09

String.equals() is case sensitive, but EF will translate that into SQL, which may not be case-sensitive depending on the collation of that column (or your database if the column does not have a collation specified).

You can solve this on the server side by setting the collation of that column to be case-sensitive

ALTER TABLE Coaches
ALTER COLUMN user_name VARCHAR(20)  // or whatever the size of your username column is
COLLATE Latin1_General_CS_AS

Or on the client side by pulling back all records that match a case-insensitive search and then filtering in Linq-To-Objects:

var query =
    from t in db.Coaches
    where String.Equals(t.user_name,username)
    select t;

// use `AsEnumerable()` to change to linq-to-objects
exists = query.AsEnumerable()
              .Any(i ==> i.user_name == username);

So long as you don't have thousands of users with the same username in different casing you shouldn't have too big of an impact by filtering in linq-to-objects.

Note that changing the collation of the column will affect any query that filters or sorts on username, so do plenty of testing in a separate environment before making that change.

score 1 · Answer 2 · answered Jan 23 '15 at 16:04

1

My guess is that while String.Equals may be generally case-sensitive, the db is set to not be case sensitive in its queries. You may need to modify the database to set the field as case-sensitive.

answered Jan 23 '15 at 16:04

jack

303
3
12

score 0 · Answer 3 · answered Jan 23 '15 at 16:06

0

You have a spelling issue. Change the following:

excists = true;

To

exists = true;

answered Jan 23 '15 at 16:06

Kurt Boyer

23
4

unfortunately this mistake is not made in my real code, thanks for clearing that up – nutellaFreak Jan 23 '15 at 16:08
I was wondering why intellisense wasn't picking that up for you. :) – Kurt Boyer Jan 23 '15 at 16:11

Checking if usernames exists, case-sensitive

3 Answers3