How to do case-insensitive string compare?

[leishen]

Hi,

I'm using dotConnect to Oracle 5.70.146.0, when I try following LINQ, I received exception:

Code: Select all

return (from l in context.Setups 
        where String.Equals(l.MaterialName, materialName, StringComparison.InvariantCultureIgnoreCase)
        select l.OperationName).Distinct(); 

System.InvalidOperationException was unhandled by user code
Message="Method 'Boolean Equals(System.String, System.String, System.StringComparison)' is not supported for execution as SQL."
Source="Devart.Data.Linq"
StackTrace:
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitWithParens(SqlNode node, SqlNode outer)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitBinaryOperator(ad bo)
at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitSelect(v ss)
at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitAlias(d a)
at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlVisitor.VisitIncludeScope(am node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.VisitIncludeScope(am node)
at Devart.Data.Linq.Provider.Query.SqlVisitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Visitor.Visit(SqlNode node)
at Devart.Data.Linq.Provider.Query.SqlFormatter.Format(SqlNode node, CommandType& commandType)
at Devart.Data.Linq.Provider.DataProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, IList`1 externalParameterAccessors)
at Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
at Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
at Devart.Data.Linq.DataQuery`1.i()


How can I do case-insensitive compasion without using str1.ToUpper() == str2.ToUpper()?

[StanislavK]

At the moment, the only possible way of case-insensitive string comparison is to use the ToUpper or ToLower methods. We plan to implement the support for Equals(String, String, StringComparison), but cannot provide any timeframe.

[StanislavK]

We will not support the String.Equals(String, StringComparison) overload, as there is no way to set string comparison to be case-insensitive inside a query.

However, with Oracle servers of version 10g R2 and newer, you can use the NLS_COMP and NLS_SORT session variables for this purpose:

Code: Select all

context.ExecuteCommand("alter session set NLS_COMP=ANSI");
context.ExecuteCommand("alter session set NLS_SORT=BINARY_CI");
var query = from l in context.Setups 
        where String.Equals(l.MaterialName) 
        select l.OperationName;

For more information on this, please refer to
http://download.oracle.com/docs/cd/B193 ... m#NLSPG005