25

How do I make this query work like it does in sql? In sql I can use < and > operators on strings.

I've been googling this for about 20 minutes and have found no solution yet.

I cannot convert r.ExemptionCode to an integer as it may have values like '91A,9AA,ZZZ,Z01'

from r in results
where (r.ExemptionCode > "900"  || r.ExemptionCode == "701" || r.ExemptionCode == "702" || r.ExemptionCode == "721" || r.ExemptionCode == "724")
select r
Doug Chamberlain
  • 11,192
  • 9
  • 51
  • 91

1 Answers1

43

Try using .CompareTo():

from r in results
where (r.ExemptionCode.CompareTo("900") > 0  || r.ExemptionCode == "701" || r.ExemptionCode == "702" ||     r.ExemptionCode == "721" || r.ExemptionCode == "724")
select r
TylerH
  • 20,799
  • 66
  • 75
  • 101
ThePaye
  • 1,561
  • 16
  • 19
  • Copy and pasted straight from my brain. Good Job. I figured this out after extensive use of the immediate window! XD – Doug Chamberlain Feb 21 '12 at 16:51
  • 5
    altho it didn't solve my issue based on the answer, you pointed me in the correct direction, which was in my case `String.Compare(a.version, b.version, System.StringComparison.Ordinal) > 0` - thanks +1 – jim tollan Dec 04 '13 at 14:39
  • Works with EF Core 3.1 also. If someone wonders. `SomeTables.Where(l => l.SomeField.CompareTo("A 102") >= 0 && l.SomeField.CompareTo("A 104") <= 0)` generated `SELECT [s].[someField] FROM [SomeTable] AS [s] WHERE (CASE WHEN [s].[someField] = N'A 102' THEN 0 WHEN [s].[someField] > N'A 102' THEN 1 WHEN [s].[someField] < N'A 102' THEN -1 END >= 0) AND (CASE WHEN [s].[someField] = N'A 104' THEN 0 WHEN [s].[someField] > N'A 104' THEN 1 WHEN [s].[someField] < N'A 104' THEN -1 END <= 0)` in my case. – Daniel Hillebrand Feb 13 '20 at 12:53
  • While this works, it appears less than optimal in the SQL sense. Generating a WHERE CASE statement pulls every possible record from the DB, to be filtered in the application tier. Any suggestions on a LINQ expression that generates something like `WHERE LastName BETWEEN 'Pat' AND 'Pet'` – Eric Patrick Oct 26 '20 at 01:58
  • A bit more research on my side, and this answer appears to be an excellent approach to this problem: https://stackoverflow.com/a/59095579/1088293 – Eric Patrick Oct 26 '20 at 11:38