4

I am storing ZIP codes in my database and trying to convert the following T-SQL to Linq to Entity:

SELECT *
FROM Regions
WHERE EndZip >= '12345'

I'm trying something like the following code:

var result = this.DbContext.Regions.Where(e => e.EndZip.CompareTo("12345") >= 0);

but I get an EntityCommandExecutionException: "The binary operator GreaterThanOrEqual is not defined for the types 'System.String' and 'System.String'."

I was able to do this if I convert my IQueryable to an IEnumerable, but I would like this query to be executed in SQL for performance reasons.

Does anyone know how I can compare strings in Linq to Entity?

jawang35
  • 187
  • 2
  • 11
  • Are you trying to find Zip Codes greater than a given Zip Code? Trying to understand the goal. – CmdrTallen Nov 20 '14 at 18:38
  • That's correct. We are storing records that have ZIP code ranges and trying to find records for which the given ZIP code falls in the records' ranges. – jawang35 Nov 20 '14 at 18:39
  • I believe your actual problem is in trying to use the `CompareTo` method of `String`, which is not supported by LINQ to Entities. – galenus Nov 20 '14 at 18:40
  • I get that String.CompareTo is not supported by LINQ to Entities but is there another way to do this? It works if I convert it to an Enumerable but I would rather have the query run on the database for performance reasons. – jawang35 Nov 20 '14 at 18:41
  • I modified the code a little to show the goal. This isn't production code obviously. – jawang35 Nov 20 '14 at 18:47
  • You have indicated this is LINQ2SQL. Maybe something like [this][1]? [1]: http://stackoverflow.com/questions/24814278/how-to-implement-lessthan-etc-when-building-expressions-on-strings – bigtlb Nov 20 '14 at 19:05
  • Works for me - maybe the feature was added after you posted the question. – John Sep 01 '16 at 08:43

3 Answers3

2

There is a restricted set of Functions supported in Linq to Entities. String comparisons are limited.

Linq to entity Docu then see inside links Supported Linq to Entity functions

The intro says most of it

This section provides information about the Language-Integrated Query (LINQ) standard query operators that are supported or unsupported in LINQ to Entities queries. Many of the LINQ standard query operators have an overloaded version that accepts an integer argument. The integer argument corresponds to a zero-based index in the sequence that is being operated on, an IEqualityComparer, or IComparer. Unless otherwise specified, these overloaded versions of the LINQ standard query operators are not supported, and attempting to use them will throw an exception.

yoozer8
  • 7,361
  • 7
  • 58
  • 93
phil soady
  • 11,043
  • 5
  • 50
  • 95
0

While I would, in general, consider treating a ZipCode as a number (instead of as a string), being a particular heinous thing to do, in this case, it seems to work:

from e in DbContext.Regions
where Convert.ToInt32(a.EndZip) >= 12345
select e

UPDATE:

Since there will be Canadian postal code (which we'll assume we do not want in our ">= 12345" search:

from e in DbContext.Regions
where DbContext.IsNumeric(a.EndZip) && Convert.ToInt32(a.EndZip) >= 12345
select e

To get that to work, you'll need let Linq know about IsNumeric

partial class MyDataContext
{
    [Function(Name = "ISNUMERIC", IsComposable = true)]
    public int IsNumeric(string input)
    {
        throw new NotImplementedException(); // this won't get called
    }
}

Now, of course, if you want to involve the Canadian codes in the search (e.g. >= 'H0H 0H0'), then you have a whole new problem, which I don't see any obvious answer to, but if you can limit yourself to segmenting just by the first character (i.e. >= '20000'), then you could do something like this:

    from e in DbContext.Regions
    where e.EndZip[0] >= '2'
    select e;
James Curran
  • 101,701
  • 37
  • 181
  • 258
  • Unfortunately we also store some international postal codes such as Canadian which include letters :T – jawang35 Nov 20 '14 at 21:46
0

Using string.CompareTo does work, at least with the setup I used (Linq v4.0.30319, Microsoft SQL Server Standard v12.0.5540, Entity Framework v6.1.3):

from e in DbContext.Regions
where e.EndZip.CompareTo("12345") <= 0
select e

However, in my experience, it only works if you use CompareTo with the entire field (at least, it doesn't work together with SubString).

Therefore, this method will not work with British zip codes.

Henrik M Madsen
  • 37
  • 1
  • 5