String "nfnfn" matches "nFnFN" and returns true - I want it to return false

Question

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 suppose, this code is being translated into some database query. In this case, database collation comes into play. If this collation is case-insensitive, you have no chance to distinguish these two strings from your example at database side. The only way - is to change collation to case-sensitive, but this will affect all comparisons made on DB side. — Andrey Korneyev, Sep 24 '15 at 13:19
possible duplicate of [How to do a case sensitive search in WHERE clause (I'm using SQL Server)?](http://stackoverflow.com/questions/1831105/how-to-do-a-case-sensitive-search-in-where-clause-im-using-sql-server) — xZ6a33YaYEfmv, Sep 24 '15 at 13:19
This will be impossible unless you can override the database collation somehow, because that's where this matching is happening. Your LINQ is being converted into SQL, hence the server is processing the matching logic. — code4life, Sep 24 '15 at 13:23
It isn't an exact duplicate as this question deals with entity framework and the "duplicate" does not. — Bradley Uffner, Sep 24 '15 at 13:23
@AndyKorneyev So that means the whole DB would become Case-Sensitive? Not sure if I would like that. Going to look more into it. — Joakim Carlsson, Sep 24 '15 at 13:23
Entity Framework doesn't support collation out of the box, but this article shows how to add it. https://milinaudara.wordpress.com/2015/02/04/case-sensitive-search-using-entity-framework-with-custom-annotation/ — Bradley Uffner, Sep 24 '15 at 13:24
@JoakimCarlsson well, if you can force your ORM to apply collation to the query you can compare strings on db side like `where x = y collate your_case_sensitive_collation` — Andrey Korneyev, Sep 24 '15 at 13:27
@BradleyUffner Did the OP state anywhere they're using Entity-Framework? It's a reasonable guess, but not necessarily correct. — CodesInChaos, Sep 24 '15 at 13:40
@CodesInChaos I think it's more than reasonable considering he's using Linq off a variable named `db` with a direct property that looks like a `DbSet` and is asking about sql server and case sensitive comparison on that line. Also, the fact that he was very interested in the link I posted about adding collation to Entity Framework. — Bradley Uffner, Sep 24 '15 at 14:11

CodesInChaos · Accepted Answer · 2015-12-08T11:35:33.927

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.

Change the database definition to use a case sensitive collation.
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
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.

This is the correct approach. 'cause if we try to force `COLLATE` using the .net `ObjectQuery` then it will throw an error when no matching records are found. Otherwise, I don't know of any other way apart from this. — Piyush, Sep 24 '15 at 14:16
@CodesInChaos I was talking about the first approach described here, http://stackoverflow.com/a/3843382/2223043 It will throw an error if no matching records are found. — Piyush, Sep 25 '15 at 05:50

score -1 · Answer 2 · answered Sep 24 '15 at 13:25

-1

You need to do a case sensitive search like the following:

public bool Consultant(string test, string test2)
{
    return db.Consultants.Any(x => String.Compare(x.Test, test, false) == 0 && String.Compare(x.Test2, test2, false) == 0);
}

answered Sep 24 '15 at 13:25

Piyush

830
8
19

@ieaglle Why do you think that it will not work? The comparison is being done at the front end using C# so it should be legit. – Piyush Sep 24 '15 at 13:35
1

@P.K. No, the comparison is *not* being done by the application. It's being interpreted by the EF query provider, which won't know how to translate it into SQL so it'll throw an exception. – Servy Sep 24 '15 at 13:37
@P.K. Assuming `Consultants` is an `IQueryable`, the ORM will attempt to translate your expression to SQL. Only some expressions can be translated, your might not. – CodesInChaos Sep 24 '15 at 13:38
@Servy I just tried the above approach and there were no errors! The code compiled and executed successfully. So there's no syntax error here. However, the output was not correct! It was giving the same result as has been posted by the OP. So it's not a correct approach. – Piyush Sep 24 '15 at 14:21
@CodesInChaos Please follow my above comment. – Piyush Sep 24 '15 at 14:22
@P.K. Then you weren't querying an EF queryable. By the sounds of it you were querying an in-memory collection. – Servy Sep 24 '15 at 14:24
@Servy First, I used EF (6) designer from database option to connect to AdventureWorks-12 database. It is definitely an EF queyable. Second, if you query an in memory collection then the above code will generate the correct output (case sensitive search) which, as I mentioned, is not the case. – Piyush Sep 25 '15 at 05:47

String "nfnfn" matches "nFnFN" and returns true - I want it to return false

2 Answers2