String & ldquo; nfnfn & rdquo; matches & ldquo; nFnFN & rdquo; and returns true - I want it to return false


I have code that checks for 2 strings on a DB. The issue is it will ignore casing so abc123 would be the same as ABC123 I would not like that. I want the user to enter the strings exactly.

This my code:

public bool Consultant(string test, string test2)
    return db.Consultants.Any(x => x.Test == test && x.Test2 == test2);

Not using a query, the method is generating one for me.

I assume this query gets translated to SQL and that the database uses a case insensitive collation.

There are several ways to address this problem.

  1. Change the database definition to use a case sensitive collation.
  2. Pass the collation as part of the query

    Your ORM might not support this and it might prevent the use of an index if the index uses a different collation.

    Bradley Uffner linked a way to do this for EF: Case sensitive search using Entity Framework and Custom Annotation

  3. Pre-filter in the database (case insensitive) to cut down the number of results efficiently. Then as a second step filter strictly using LINQ-to-objects.

    public bool Consultant(string test, string test2)
        var candidates = db.Consultants.Where(x => x.Test == test && x.Test2 == test2);
        return candidates.AsEnumerable().Any(x => x.Test == test && x.Test2 == test2);

    Don't forget to add comments if you use this approach, since this is hardly intuitive.

    This way is usually inferior to the alternatives, so your should prefer them if they're available.