.Any() not working as expected when letter case involved

Question

I have a very simple Linq-to-SQL query that returns a boolean:

using (MyDataContext TheDC = new MyModelDataContext())
{
    return !TheDC.SomeTable.Any(l => l.UserID == SomeLong && l.ColumnName == SomeString);
}

The problem is that when the comparison involves strings that differ by the case of some letters, it returns false. For instance, if the table contains testString and SomeString is TestString, it returns false.

How can I rewrite it?

...so information missing is the type of database and the collation of column being queried. — spender, Mar 13 '18 at 15:30
@BanForFun ...and lose the benefit of any indexes on that column. — spender, Mar 13 '18 at 15:31
@spender some databases, like postegresql, allow to define index on functions (so index on 'lower(ColumnName)'), so it's not that clear. — Evk, Mar 13 '18 at 15:38
@Steve: it's set at the DB level to SQL_Latin1_General_CP1_CI_AS — frenchie, Mar 13 '18 at 15:40
@Evk IIRC linq2sql only works on SQL server, so in the absence of more info, that's my assumption. — spender, Mar 13 '18 at 15:43
@frenchie That's a case-insensitive, accent-sensitive collation, so no lead so far. Anything on the column? https://learn.microsoft.com/en-us/sql/relational-databases/collations/view-collation-information — spender, Mar 13 '18 at 15:44
You're probably going to have to come up with a reproducible test case to move this forward any further... :( — spender, Mar 13 '18 at 15:48
ToLower can not pass the turkey test, be carefull about that. https://stackoverflow.com/a/234751/3580273 — Khatibzadeh, Mar 13 '18 at 16:10
Funny that 100 times upvoted accepted answer in duplicate question does not actually answers at all, since doesn't work with linq to sql (as I understand). — Evk, Mar 13 '18 at 16:43
Indeed, the answer linked as a duplicate doesn't work at all — frenchie, Mar 13 '18 at 19:32
Well anyway, I found a work around that doesn't require this anymore. — frenchie, Mar 19 '18 at 21:40

score -1 · Answer 1 · answered Mar 13 '18 at 15:49

-1

You have the Equal funcion

in your case it's should look like this:

using (MyDataContext TheDC = new MyModelDataContext())
{
    return !TheDC.SomeTable.Any(l => l.UserID == SomeLong && l.ColumnNameEquals(SomeString, StringComparison.InvariantCultureIgnoreCase));
}

answered Mar 13 '18 at 15:49

Tom Mendelson

625
4
10

This is just a variation on the other **strongly** downvoted answers, and probably doesn't make it through the linq2sql translation to SQL. Are you sure you want to post this? – spender Mar 13 '18 at 15:52
1

@spender Unlike other answers this simply doesn't do a case insensitive comparison. LINQ to sql knows how to interpret this code, it just ignores the comparison operator and uses the DB's collation settings regardless. So this answer is actually bad for very different reasons than the other answers. – Servy Mar 13 '18 at 15:54
i'm pretty sure it's the best answer to this weird case. btw, this question marked as duplicated, i recommend you to watch the answer over there :) – Tom Mendelson Mar 13 '18 at 15:58
@TomMendelson Why do you think an answer that doesn't actually work is the best answer? – Servy Mar 13 '18 at 16:00

score -4 · Answer 2 · answered Mar 13 '18 at 15:32

-4

Would you like to try this code?

using (MyDataContext TheDC = new MyModelDataContext())
{
    return !TheDC.SomeTable.Any(l => l.UserID == SomeLong && l.ColumnName.ToLower() == SomeString.ToLower());
}

answered Mar 13 '18 at 15:32

Minos

218
2
7

an awesome way to destroy index – Steve Mar 13 '18 at 15:33
wow. Of course, if you think about performance, index or full text search should be considered. And it is not copied. The answer was only slow. – Minos Mar 13 '18 at 15:37
index/full text search will get destroyed completely if you are doing .ToLower(). – Steve Mar 13 '18 at 15:48
I agree with your answer. I just thought that the questioner would have asked the C# code rather than the DB. – Minos Mar 13 '18 at 15:57
I think the answer to this question about performance may be an over-answer to the question. Of course, simple answer is to add a lower-cased column to the DB. – Minos Mar 13 '18 at 16:06

score -4 · Answer 3 · answered Mar 13 '18 at 15:33

-4

I'd try, tolower() and for good measure check the db collate.

l.ColumnName.ToLower() == SomeString.ToLower()

answered Mar 13 '18 at 15:33

Rickey

7,830
1
19
12

Check the other answer and act quickly... – spender Mar 13 '18 at 15:34
Really? Is everyone copying my comment? – BanForFun Mar 13 '18 at 15:34
No, just you posted faster – Rickey Mar 13 '18 at 15:34
Yeah, but they should see the comments (and the already existing answer) before they do the exact same – BanForFun Mar 13 '18 at 15:35
pretty sure someone with 18k rep would have known this method. But he has a very valid reason not to do it: Index – Steve Mar 13 '18 at 15:36

.Any() not working as expected when letter case involved

3 Answers3