How to perform case insenitive search in linq

Question

_{Friday, October 18, 2013 3:31 AM}

Hi,

I have implemented 'Like' filtering in asp net dynamic data like below.

Protected Function Query(ByVal searchTerm As String, ByVal source As IQueryable) As MethodCallExpression
        Dim searchFilter As ConstantExpression = Nothing
        Dim parameter As ParameterExpression = Expression.Parameter(source.ElementType)
        Dim prop As MemberExpression = Expression.Property(parameter, Column.Name)

        Dim method As MethodInfo = Nothing
        Dim methodExp = Nothing
        Dim expLambda = Nothing
        If Nullable.GetUnderlyingType(prop.Type) IsNot Nothing Then
            prop = Expression.Property(prop, "Value")
        End If
        If searchTerm.StartsWith("%") And searchTerm.EndsWith("%") Then
            searchFilter = Expression.Constant(searchTerm.Replace("%", ""))
            method = GetType(String).GetMethod("Contains", New Type() {GetType(String)})
            methodExp = Expression.Call(prop, method, searchFilter)
        ElseIf searchTerm.StartsWith("%") Then
            searchFilter = Expression.Constant(searchTerm.Replace("%", ""))
            method = GetType(String).GetMethod("EndsWith", New Type() {GetType(String)})
            methodExp = Expression.Call(prop, method, searchFilter)
        ElseIf searchTerm.EndsWith("%") Then
            searchFilter = Expression.Constant(searchTerm.Replace("%", ""))
            method = GetType(String).GetMethod("StartsWith", New Type() {GetType(String)})
            methodExp = Expression.Call(prop, method, searchFilter)
        ElseIf searchTerm.Contains("%") Then
            Dim index As Integer = searchTerm.IndexOf("%")
            Dim leftString As ConstantExpression = Expression.Constant(searchTerm.Substring(0, index))
            Dim rightString As ConstantExpression = Expression.Constant(searchTerm.Substring(index + 1))

            methodExp = Expression.And(Expression.Call(prop, GetType(String).GetMethod("StartsWith", New Type() {GetType(String)}), leftString), Expression.Call(prop, GetType(String).GetMethod("EndsWith", New Type() {GetType(String)}), rightString))
           
        End If
        expLambda = Expression.Lambda(methodExp, parameter)
        Dim resultExpression As MethodCallExpression = Expression.[Call](GetType(Queryable), "Where", New Type() {source.ElementType}, New Expression() {source.Expression, Expression.Quote(expLambda)})
        Return resultExpression
    End Function

The above code is working fine if i provide the value in required case like 'C%' or 'c%'. Is is possible to make it case insensitive something like, if i provide 'c%' or 'C%' it should return the same result.

The comparison in database should be like Upper(columnname) like upper('c%')

Thanks,

Praveen

All replies (3)

_{Friday, October 18, 2013 4:03 AM ✅Answered}

Try either Expression.Constant(value.ToUpper()) or add StringComparison.OrdinalIgnoreCase

http://stackoverflow.com/questions/7437494/case-insensitive-dynamic-search-for-linq-to-entities
http://stackoverflow.com/questions/7754018/how-to-build-case-insensitive-strong-typed-linq-query-in-c

_{Friday, October 18, 2013 5:34 AM}

Hi smirnov,

I have checked the links that you posted.. I am new to Linq querying, i am not able to change my code according the solutions provided in the above links. Can you make the necessary changes in the above to code to make it work with Oracle database..It is working fine(case-insensitve search) with sql server,but the same code is not working with oracle database.

Thanks,

praveen.

_{Monday, October 21, 2013 1:59 AM}

Hi Praveen,

I suggest you to convert the LINQ to T-SQL statement, and then execute it in Oracle database.

Now, maybe you can find the reason of your problem.

About convert LINQ to sql please refer to:
http://forums.asp.net/t/1697144.aspx

Hope it help.

Best Regards,
Terry Guo