IndexOf not recognized by LINQ to Entities

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

IndexOf not recognized by LINQ to Entities

Post by labate » Tue 07 Dec 2010 10:06

Hello,

I would like to find persons where the first name contains a specified string in a case insensitive way.
For example, if I want to find all persons who match "greg" I will find "Gregory", "Gregor", "Nagrege", etc.

Unfortunately, it seems that the Linq method Contains is case sensitive. By reading some posts, I finally use IndexOf.

Code: Select all

var query = from p in ObjectContext.Persons select p;

if (!String.IsNullOrWhiteSpace(firstName))
    {
    query = query.Where(p => p.FirstName.IndexOf(firstName, 0, StringComparison.OrdinalIgnoreCase) != -1);
    }
Then when the query is executed, I got the following error:
System.NotSupportedException was unhandled by user code
Message=LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String, Int32, System.StringComparison)' method, and this method cannot be translated into a store expression.
Source=System.Data.Entity
StackTrace:
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.NotTranslator.TypedTranslate(ExpressionConverter parent, UnaryExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.NotEqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.Convert()
at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
at Polyright.PersonManager.Server.Services.PersonManagerDomainService.FindPersonsBySimpleCriterias(String firstName, String lastName, Nullable`1 categoryId) in C:\Phoenix\trunk\PersonManager\Polyright.PersonManager.Server.Services\PersonManagerDomainService.cs:line 864
at FindPersonsBySimpleCriterias(DomainService , Object[] )
at System.ServiceModel.DomainServices.Server.ReflectionDomainServiceDescriptionProvider.ReflectionDomainOperationEntry.Invoke(DomainService domainService, Object[] parameters)
at System.ServiceModel.DomainServices.Server.DomainOperationEntry.Invoke(DomainService domainService, Object[] parameters, Int32& totalCount)
at System.ServiceModel.DomainServices.Server.DomainService.Query(QueryDescription queryDescription, IEnumerable`1& validationErrors, Int32& totalCount)
InnerException:
I verified that IndexOf is well supported by devart as mentioned in documentation "Entity Framework Canonical Functions" (http://www.devart.com/dotconnect/oracle ... tions.html)

Have I missed something, or is there some usages of IndexOf not supported?

Any advice about how to match string criteria in case insensitive way?
Thanks.
Top
AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 08 Dec 2010 15:49

As you can see from the call stack, the error arises in the LINQ to Entities engine. Such IndexOf overload is not supported in LINQ to Entities.
A simpler solution for your problem is to use the following code:

Code: Select all

query = query.Where(p => p.FirstName.ToUpper().Contains(firstName.ToUpper()));
Top
labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Post by labate » Wed 22 Dec 2010 17:45

Hello,

I'm not satisfied with ToUpper() that does not help with accent insensitive search.

Here my solution :
Execute the following specific store commands:

Code: Select all

ctx.ExecuteStoreCommand("alter session set NLS_COMP=LINGUISTIC");
ctx.ExecuteStoreCommand("alter session set NLS_SORT=BINARY_AI");
Sure they are Oracle specific commands. I inject them in my code as extensions and execute them according to the database type.

Hope that helps!
Top
AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 27 Dec 2010 12:49

Thank you for sharing your solution.
Top
Post Reply

Return to “Entity Framework support”