Geek Answers Handbook

LINQ String String Comparison

I have a column varchar(5)(named name) in my table (named codes). This SQL query is what I would like to execute through EF, without first converting mine IQueryableto IEnumerable(in memory):

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

I tried to execute the request using this method , for example:

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

However, CompareTo cannot be translated into sql and an exception is thrown. For CompareTo to work, you must have IEnumerable, i.e. all records have already been pulled from the database into memory. When I try to accomplish 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 possibility that I can think of is to use .SqlQuery () on my db.Codes DbSet to execute the actual select statement using> = and <=.

- -sql-, ?

EDIT: .CompareTo( [0]), . EF sql. , , .CompareTo() /.

+4
1

CompareTo . Entity Framework >, <, >= <=.

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

:

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

SQL-, :

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

, , , Linq. , . :

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

:

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

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

More articles:


All Articles