Linq to Entities Expression Tree - Comparing String Values as Integers

Question

How can I build a SQL Server compatible expression tree that can compare a string as if it were an integer?

My table contains phone numbers (e.g. "01234000000") and I want my dynamically created query to return a range of phone numbers.

The SQL I want is something like this:

SELECT PhoneNumber 
FROM MyTable 
WHERE PhoneNumber BETWEEN '01234000000' AND '01234000099'

which even though PhoneNumber is a VARCHAR it's perfectly valid SQL and returns the results as you'd expect.

I've tried a couple of things (upper bound omitted for clarity):

Expression.GreaterThanOrEqual(prop, "01234000000") // doesn't work on strings

Expression.GreaterThanOrEqual(
    Expression.Convert(prop, typeof(Int64)), 
    Expression.Constant(1234000000, typeof(Int64))) // not supported by the SQL Query Provider

Is there any other way to achieve this? If not, then is it possible to extend the Query Provider to add this in myself and how do I do this?

Answer 1

From what I know, SQL isn't really doing a numeric compare, it's doing a alpha compare. But since you're working with phone numbers which are all the same length, that difference should be immaterial.

You can do the following though from within EF:

var lowerBound = "01234000000";
var upperBound = "01234000099";
var results = MyTable
    .Where(t => t.PhoneNumber.CompareTo(lowerBound) >= 0 && t.PhoneNumber.CompareTo(upperBound) <= 0);

According to LinqPad, that generates the following SQL:

DECLARE @p0 VarChar(1000) = '01234000000'
DECLARE @p1 VarChar(1000) = '01234000099'

SELECT [t0].[PhoneNumber]
FROM [MyTable] AS [t0]
WHERE ([t0].[PhoneNumber] >= @p0) AND ([t0].[PhoneNumber] <= @p1)