How build dynamic where clause with string argument

Question

I try to buid a method to add a where clause to a Linq-to-SQL request (return an IQueryable). I try several methods but always use ToString, Indexof... but this result is a sql request take all element and the filter made in linq. I see request in SQL Server profiler. I want a method to do it with result is a sql request with where include inside

I work in Visual Studio 2017 with SQL Server 2016. I code in vb.net

I see an interesting thing in linq dynamic library. But I can't to adapt to my situation

 <Extension()> _
    Public Function Where(ByVal source As IQueryable, ByVal predicate As String, ByVal ParamArray values() As Object) As IQueryable
        If source Is Nothing Then Throw New ArgumentNullException("source")
        If predicate Is Nothing Then Throw New ArgumentNullException("predicate")
        Dim lambda As LambdaExpression = DynamicExpression.ParseLambda(source.ElementType, GetType(Boolean), predicate, values)
        Return source.Provider.CreateQuery( _
            Expression.Call( _
                GetType(Queryable), "Where", _
                New Type() {source.ElementType}, _
                source.Expression, Expression.Quote(lambda)))
    End Function

But I don't need all this complex strucutre. It's some years I buid my utilities. But Need to upgrade it. Here my code of my utilities

<Extension()>
Public Function Where(ByVal source As IQueryable, ByVal predicate As String) As IQueryable
    Dim param = Expression.Parameter(GetType(String), "x")
    Return source.Provider.CreateQuery(
            Expression.Call(
                GetType(Queryable), "Where",
                New Type() {source.ElementType},
                source.Expression, Expression.Quote(Expression.Lambda(Expression.Constant(predicate), param))))
End Function


Public Function TFOAppliqueFiltreTri(Of T, MaClassDatas As Class)(Origins As IQueryable(Of T), ByVal MesDonnees As TableFullOption.PagerTabEnCours(Of MaClassDatas)) As IQueryable(of T)
    Dim retour As New TableFullOption.LstRetour

    'Colonne de filtre
    Dim strWh As String = ""
    Dim Filtredrecords As IQueryable(Of T)
    For Each Sort In MesDonnees.MesOptions
                       
                Dim colName = Sort.ColName
                If strWh.Length > 0 Then strWh = strWh & " AND "
                strWh = strWh & String.Format(colName & " like '%{0}%'", Sort.Search)
                           
    Next
    If strWh.Length > 0 Then
        Filtredrecords = Origins.Where(strWh) '<- Here call Where
    Else
        Filtredrecords = Origins
    End If

   
  
    Return Filtredrecords 
End Function

I get this error:

Aucune méthode générique 'Where' sur le type 'System.Linq.Queryable' n'est compatible avec les arguments de type et les arguments fournis..

Then my problem is to write correctly lambda expression. My predicate argument is : Column1 like '%aaa%'. I want rewrite where method of dynamicLinq to accept string argument :Column1 like '%aaa%' directly

Thanks for your help

Answer 1

Finally after lot of reading in google and few feelings and certainly lot of chance.

 Public Function Where(Of TEntity)(source As IQueryable(Of TEntity), searchColumn As List(Of String), searchValue As String) As IQueryable(Of TEntity)
    Dim cond As Expression = Nothing
    Dim ParamExpr = Expression.Parameter(GetType(TEntity), "x")
    Dim conCat2 = GetType(String).GetMethod("Concat", New Type() {GetType(String), GetType(String)})
    Dim conCat4 = GetType(String).GetMethod("Concat", New Type() {GetType(String), GetType(String), GetType(String), GetType(String)})
    Dim Delim = Expression.Constant("/")
    Dim DateName = GetType(SqlFunctions).GetMethod("DateName", New Type() {GetType(String), GetType(Nullable(Of DateTime))})
    Dim DatePart = GetType(SqlFunctions).GetMethod("DatePart", New Type() {GetType(String), GetType(Nullable(Of DateTime))})
    Dim DblToString = GetType(SqlFunctions).GetMethod("StringConvert", New Type() {GetType(Nullable(Of Double))})

    For Each cn In searchColumn
        For Each colName In cn.Split("|")
            If Not colName.estVide Then
                Dim body As Expression = ParamExpr
                For Each member In colName.Split(".")
                    body = Expression.PropertyOrField(body, member)
                Next
                Dim Tostr As Expression
                If body.Type.FullName.Contains("String") Then
                    Tostr = body
                ElseIf body.Type.FullName.Contains("DateTime") Then
                    Dim day = Expression.Call(Expression.Call(conCat2, Expression.Constant("0"), Expression.Call(DateName, Expression.Constant("day"), body)), "Substring", Nothing, Expression.Constant(0), Expression.Constant(2))
                    Dim Month = Expression.Call(DatePart, Expression.Constant("MM"), body)
                    Dim toDouble = Expression.Convert(Month, GetType(Nullable(Of Double)))
                    Dim mois = Expression.Call(conCat2, Expression.Constant("0"), Expression.Call(Expression.Call(DblToString, toDouble), "Trim", Nothing))
                    Dim an = Expression.Call(DateName, Expression.Constant("year"), body)
                    Tostr = Expression.Call(conCat2, Expression.Call(conCat4, day, Delim, mois, Delim), an)
                Else
                    Tostr = Expression.Call(body, "Convert.ToString", Nothing)
                    'Tostr = Expression.Convert(body, GetType(String))
                End If
                Dim condPart = Expression.Call(Expression.Call(Tostr, "ToLower", Nothing), "Contains", Nothing, Expression.Call(Expression.Constant(searchValue), "ToLower", Nothing))
                If cond Is Nothing Then
                    cond = condPart
                Else
                    cond = Expression.OrElse(cond, condPart)
                End If
            End If
        Next
    Next
    Return source.Provider.CreateQuery(Of TEntity)(Expression.Call(GetType(Queryable), "Where", New Type() {GetType(TEntity)}, source.Expression, Expression.Lambda(cond, ParamExpr)))
End Function

Now I've dynamic filter which generated a SQL request with complete clause where