EntityFramework LINQ string order comparison

Question

I have a varchar(5) column (named name) in my table (named codes). This SQL query is what I'd like to accomplish via EF, without first converting my IQueryable to IEnumerable (in memory):

SELECT * FROM codes WHERE name >= 'J0000' AND name <= 'J9999'

I've tried performing a query using this method, like this:

var results = db.Codes.Where(c=>c.Name.CompareTo("J0000") >=0 
    &&  c.Name.CompareTo("J9999") <=0)

However the CompareTo can't be translated into sql and an exception is thrown. In order for CompareTo to work, you have to have an IEnumerable, meaning all the records are already pulled from the database into memory. When try to execute this I get:

{"The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities."}

With stack trace:

   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.NotSupportedTranslator.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()

My only other option that I can think of is to use .SqlQuery() on my db.Codes DbSet in order to execute the actual select statement using >= and <=.

Are there any non-sql options that will allow me to perform a query like this?

EDIT: In my actual code I was doing a .CompareTo(codes[0]) where codes was an array of string. This array was breaking the EF sql translation. My solution was to get the string value into a new string variable first, and then pass the new string variable to .CompareTo() instead of the array variable / index specified.

Answer 1

You can use CompareTo to compare your strings. Entity Framework converts these into >, <, >= and <=.

var results = db.Codes.Where(c => c.Name.CompareTo("J0000") >= 0);

Or using this syntax:

var results = from c in db.Codes
              where c.Name.CompareTo("J0000") >= 0
              select c;

This will produce SQL output similar to this:

WHERE [Extent1].[Name] >= N'J0000'

EDIT

After you gave your error message it seems you are using an indexed property instead of string literals in your Linq query. To fix this, copy the values to temporary variables. So this:

var results = db.Codes.Where(c=>    c.Name.CompareTo(somearray[0]) >=0 
                                 && c.Name.CompareTo(somearray[1]) <=0)

Becomes this:

var lowerBound = somearray[0];
var upperBound = somearray[1];

var results = db.Codes.Where(c=>    c.Name.CompareTo(lowerBound) >=0 
                                 && c.Name.CompareTo(upperBound) <=0)