String.Equals() not working as intended

Question

I am using LINQ to search through one of my Entity Framework tables and find a "group" based on the name. The name is a string and appears to be Unicode (says it is in the edmx). I have a method GetGroup() and I pass in a name to search for. Debugging through the code, I already have a group named "Test" in my database. Once I pass in a group named "TEST" I expect it to return the "Test" which was already in the database. It for some reason, does not find the "Test" and thinks "TEST" doesn't exist. Here is my query, I cannot see why it does not work. Please help.

"name" is the passed in the group name. My .Equals seems to only work if the gr.Name and name are the exact same. If one character is capital in one of the two strings, then the .Equals doesn't work. I have tried to use InvariantCultureIgnoreCase, and that did not seem to help. In case someone asks, the MyLeagueId and LeagueId will always match, the database is setup so there can be a group in a different league id. I do not think this is the problem.

Group g = (from gr in this.DatabaseConnection.Groups
           where gr.Name.Equals(name, StringComparison.OrdinalIgnoreCase) &&
           gr.LeagueId == this.MyLeagueId
           select gr).FirstOrDefault();

Are you sure one doesn't have a space at the end/beginning that the other one doesn't have? — KallDrexx, Feb 22 '11 at 17:59
If the Groups table is not too big, then as a test, can you try changing the query to ... from fr in this.DatabaseConnection.Groups.ToList() ... — sgmoore, Feb 22 '11 at 18:15

score 48 · Answer 1 · edited Jun 26 '17 at 14:45

48

The string comparison with StringComparison.OrdinalIgnoreCase works in memory or with IEnumerable<T>. You are trying to use it with IQueryable<T>, but the provider of your queryable does not understand it.

This works for me:

db.Users.FirstOrDefault(
     s => s.Username.Equals(username, StringComparison.OrdinalIgnoreCase)
);

edited Jun 26 '17 at 14:45

T.S.

18,195
11
58
78

answered Apr 10 '14 at 04:51

Swapnil Malap

610
1
7
14

2

static method `string.Equals` did not worked, even with the same arguments. Using instance method was solution. – Michal Minich Nov 23 '16 at 14:57
2

I wonder why I get "Method 'Boolean Equals(System.String, System.String, System.StringComparison)' has no supported translation to SQL."? – Dronz Feb 07 '17 at 09:31

score 46 · Accepted Answer · answered Feb 22 '11 at 21:24

46

When using LINQ to Entities, it will automatically convert it to LINQ to SQL. And if the database field you are doing a .Equals on does not have a collate of NOCASE (SQLite in my example) then it will always be case-sensitive. In otherwords, the database defines how to do the string comparison rather than code.

answered Feb 22 '11 at 21:24

Travyguy9

4,774
8
43
63

So is there a way to perform a case insensitive search with LINQ to Entities? – Shimmy Weitzhandler Jan 01 '12 at 02:53
Just have the DB field contain the NOCASE for the collate (for SQLite, not sure on other DBs. Im sure its similar). – Travyguy9 Jan 04 '12 at 15:44
10

I just tested `str1 == str2` and it does a case insensitive search, so I don't need the `Equals` hassle. Thanks for your input. – Shimmy Weitzhandler Jan 05 '12 at 01:10

score 14 · Answer 3 · edited Jun 26 '17 at 14:45

14

Use the String.Compare() as it can be translated to Sql.

Here are some examples of string matching in Linq, with the Sql translation as well.

edited Jun 26 '17 at 14:45

T.S.

18,195
11
58
78

answered Dec 23 '14 at 14:30

Jowen

5,203
1
43
41

1

This should be the accepted answer. Mainly for the awesome **String Matching in LINQ** link, that explains it so clearly. – alienriver49 Sep 18 '19 at 20:41
Sadly, this doesn't work in Oracle's ODP.NET as the SQL still translates to normal comparison. – Veerakran Sereerungruangkul Jul 22 '21 at 10:43
This doesn't work in MS SQL Server, again query is client evaluated. I checked by setting ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning)) in OnConfiguring method. This provides some good solutions- https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity In my case, my DB fields were Case-insensitive, so equality operator == worked fine. You can check your DB fields using- https://www.webucator.com/article/how-to-check-case-sensitivity-in-sql-server/ – Dragonknot Feb 08 '23 at 13:40

score 10 · Answer 4 · edited May 23 '17 at 12:25

10

Made some research. You can't do. The collation (the type of comparison) is defined at the column level of the table. You can't modify it through EF. If it's defined as case insensitive, then all the searches will be case-insensitive. If it's defined as case sensitive, then your only hope is ToUpper() the strings.

http://connect.microsoft.com/VisualStudio/feedback/details/435783/entity-framework-conceptual-model-doesnt-support-string-equals-via-linq

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/3810aa67-f6fe-4624-a14b-eaaa0e05ddcd

EF4 Linq Oracle11g making queries none case-sensitive

LINQ to Entities case sensitive comparison

edited May 23 '17 at 12:25

Community

1
1

answered Feb 22 '11 at 17:55

xanatos

109,618
12
197
280

Didnt work. I get this exception (not sure why...both my objects I am comparing are strings): Unable to create a constant value of type 'System.StringComparer'. Only primitive types ('such as Int32, String, and Guid') are supported in this context. – Travyguy9 Feb 22 '11 at 18:26
@Travyguy9 Changed suggestion :-) – xanatos Feb 22 '11 at 18:34
2

String.Equals gives me this error (StackTrace ends in LINQ, so LINQ doesn't like it I assume) - Incorrect number of arguments supplied for call to method 'Boolean Equals(System.String, System.String, System.StringComparison)' – Travyguy9 Feb 22 '11 at 19:32
ToUpper/ToLower work...which I find stupid..Shouldn't using IgnoreCase for my StringComparison do this for me? – Travyguy9 Feb 22 '11 at 19:36
You are without hope :-) In the end you'll have to ToUpper() both strings (I've read that it's better than ToLower). If you stay away from Turkish and some other languages you shouldn't have problem. – xanatos Feb 22 '11 at 19:36
@Travyguy9 In the CurrentCulture, have you used the form str1.Equals(str2, StringComparison.CurrentCultureIgnoreCase)? – xanatos Feb 22 '11 at 19:38
1

No, ToUpper and ToLower are totally different beasts. There are some problems with some letters. The lower case ı (it's a turkish character) when converted in upper case is equal to I (that is the upper case of european-american i). And there are probably tons of these examples. – xanatos Feb 22 '11 at 19:41
Yea I have tried all 3 StringComparison's with IgnoreCase. All failed. – Travyguy9 Feb 22 '11 at 19:45
@Travygui9 I'll add another link. Still no good news :-) http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison – xanatos Feb 24 '11 at 12:35

score 6 · Answer 5 · answered Oct 04 '12 at 19:02

6

I like TravyGuy's answer from a technical perspective. For a more direct, practical answer, try using:

string.Compare(string A, string B, StringComparison.OrdinalIgnoreCase) == 0

answered Oct 04 '12 at 19:02

AlvinfromDiaspar

6,611
13
75
140

score 2 · Answer 6 · edited Dec 19 '14 at 18:03

Try name.Equals(gr.Name, StringComparison.OrdinalIgnoreCase)

If it works then the problem could be with gr.Name.

--- Edit ---

I'm guessing that gr.Name is not of type System.string. (since String.Equals gives you an error ==> from the previous post)

give this a shot

(gr.Name as string).Equals(name, StringComparison.OrdinalIgnoreCase)

or

String.Equals((gr.Name as string), name, StringComparison.OrdinalIgnoreCase)

String.Equals() not working as intended

6 Answers6

Linked

Related