string.equals function not working correctly c#

Question

I am stuck in a very simple problem, I am just comparing two strings in c# inside an LINQ query's where clause. But it's not working on case sensitivity. I am tired. Here's my code

public class User
{
    [Key()]
    public long ID { get; set; }

    [Required]
    public string Fname { get; set; }

    [Required]
    [Display(Name = "Last Name")]
    public string Lname { get; set; }

    [DataType(DataType.PhoneNumber)]
    public string Ph_Num { get; set; }

    public string CNIC { get; set; }

    [Required]
    [Display(Name="Username")]

    public string Username { get; set; }
    [Required]

    public string Password { get; set; }

    public string Designation { get; set; }

    public DateTime CreatedDate { get; set; }
}

and my cs code look like

public ActionResult Index(string username, string password)
    {
        if (username != "" && password != "")
        {
            //checking is user already exists

            //Here problem arise...

            var query = db.Users.Where(i => i.Username.Equals(username)).ToList();
            User res = null;
            if(query.Count == 1)
            {
                res = query.First();
            }
            if (res != null)
            {
                //My remaining code
            }
        }
        return View("Index");
    }

Answer 1

When comparing 2 string values inside a LINQ lamda expression, It does a case sensitive comparison by default. But if you do not wish to have this behaviour, you can use a different overload of Equals method which takes a comparison type enum as the second argument, and you can pass the type of comparison you want (Case sensitive or Case Insensitive). For case insensitive comparisons you may pass StringComparison.CurrentCultureIgnoreCase

var resultsWithCaseIgnored = someEntityList.Where(i => i.Name
                      .Equals("scott",StringComparison.CurrentCultureIgnoreCase)).ToList();

But when you do a LINQ to SQL statement where it is going to generate a SQL statement and executes it, the generated SQL will be same for both the overloads.

The case sensitivity of where clauses in your SQL statements depends on the collation settings you have on your db server / your db / your specific column.

You can verify your current setting by executing a sql statement like

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

When i ran this in my sql server, the result i got was "SQL_Latin1_General_CP1_CI_AS". The CI part indicates, it is case insensitive. That means when i do where UserName='scott' and where UserName='SCOTT', both will give me same result.

If you want to do a case sensitive check, there are ways to update your collation to be one which is case sensitive. But updating your db is not always a safe thing ( think about the implications)

What you can do is, query your db table, get the results to your C# objects then do a where clause ( which will be case sensitive).

var users= db.Users.Where(i => i.Username.Equals(username)).ToList();

// do a case sensitive check on users
var user = users.FirstOrDefault(s=>s.Username.Equals(username));
if(user!=null)
{
  // user has the User object
}
else
{ 
  // no user matching our CASE SENSITIVE UserName check.
}

Answer 2

The problem is NOT in C# ... the problem it's in your SGBD. For example, in SQL Server check what culture settings you have. If it is something containing "CI" then everything will be compared in insensitive way. That's because what you see it's just an expression tree that is translated into plain SQL and executed ON your SGBD. More details: https://msdn.microsoft.com/en-us/library/ms180175.aspx

Note: you can trick this by calling "ToUpper" or "ToLower" in your LINQ query:

public ActionResult Index(string username, string password)
    {
        if (username != "" && password != "")
        {
            //checking is user already exists

            //Here problem arise...
            userName = userName.ToUpperInvariant ();
            var query = db.Users.Where(i => i.Username.ToUpper ().Equals(username)).ToList();
            User res = null;
            if(query.Count == 1)
            {
                res = query.First();
            }
            if (res != null)
            {
                //My remaining code
            }
        }
        return View("Index");
    }

This will be translated in SQL into something like UPPER (UserName) = 'BLABLA'

Answer 3

The problem is that your Linq query is being translated to SQL and thus the settings in your DB will determine if the comparison is case sensitive or case insensitive. Since you want case sensitive, but are getting case insensitive I would suggest doing a second filter in Linq-to-Objects by using AsEnumerable

var query = db.Users.Where(i => i.Username == username)
                    .AsEnumerable()
                    .Where(i => i.Username == username)
                    .ToList();

Basically this will ensure that the second Where will be executed in your code versus being translated to SQL. And since string comparison in C# is case sensitive you should get what you want. Note you would still want to keep the first Where to ensure that you don't return all the rows of the table from the DB which could be costly. You'll just return rows that match in a case insensitive manner which will include any case sensitive matches as well.