Linq function like .Net string.CompareOrdinal

Question

I need to compare strings using the string.CompareOrdinal(...) inside a linq query.

string max;
string min;

var res = db.Table
            .Where(c => string.CompareOrdinal(c.Id,  min) >= 0)
            .Where(c => string.CompareOrdinal(c.Id,  max) <= 0)
            .ToList();

The code throws a exception:

LINQ ti Entities does not recongnize the method 'Int32 CompareOrdinal(System.String, System.String)' method, and this method cannot be translated into a store expression.

There are a lot of data in the table, so I really need the where clause.

Is there a way around this?

Update

I'm not trying to deside if two strings are equal - case sensitive or not.

I'm trying to deside whether a string is within a range. So the quistion is

Is there a way to do that - so that is works with L2E?

Obviously, I cannot use the string.CompareOrdinal

have you tried something similar.. I mean.. this one may help http://stackoverflow.com/questions/332670/simple-linq-to-sql-has-no-supported-translation-to-sql — Hiren Pandya, Apr 03 '13 at 10:06
I think that code should give a compile error "No overload for method 'CompareOrdinal' takes 1 arguments" — Colonel Panic, Apr 03 '13 at 10:06
possible duplicate of [linq to entities case sensitive comparison](http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison) — Konamiman, Apr 03 '13 at 10:11
FYI, here is a link that specified all the non-compatible method of String http://msdn.microsoft.com/en-us/library/bb882672(v=vs.90).aspx — Cyril Gandon, Apr 03 '13 at 15:19

Jens Kloster · Accepted Answer · 2013-11-13T09:06:57.407

A colleague of mine found a workaround using string.Compare instead of string.CompareOrdinal

string min = "a";
string max = "z";

 var res = db.Table
         .Where(c => string.Compare(c.Id, min, StringComparison.OrdinalIgnoreCase) >= 0)
         .Where(c => string.Compare(c.Id, max, StringComparison.OrdinalIgnoreCase) <= 0)
         .ToList();

this is the generated SQL:

SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Table] AS [Extent1]
WHERE ([Extent1].[Id] >= 'a') AND ([Extent1].[Id] <= 'z')

score 0 · Answer 2 · answered Jul 29 '13 at 19:19

0

If Id is a string, this solution works, so it looks like Id is an int. Int cannot be compared to a string. Change Id to a string or min/max to an int to make it work (and use a simple < and > between the int values).

By the way: it would save 1 iteration to check for min and max in 1 where function.

answered Jul 29 '13 at 19:19

Alex Siepman

2,499
23
31

Thanks for your answer. If I was using Linq To Objects (L2O) the above statement *would work*. However in L2E the provider cannot translate the statement into any SQL. L2E uses deffered loading. By the time the query hits the database, the two `Where` clauses have been combined. – Jens Kloster Jul 29 '13 at 20:16

Linq function like .Net string.CompareOrdinal

2 Answers2

Linked