Implementing Dynamic Searching Using LINQ

Implementing Dynamic Searching Using LINQ

  • Comments 34

A common requirement in forms-over-data applications is that users be able to search by any combination of fields to construct a dynamic query at run time.  For example, the search feature in this application allows the user to find all records that meet criteria defined on multiple columns:




LINQ makes it easy to write powerful queries like this over various data sources; for instance, we can use the following query to find all Orders shipped to a given country within a user-specified timeframe:

        Dim query = From order In db.Orders _

                    Where order.ShipCountry = txtCountry.Text _

                      And order.ShippedDate >= dtpStartDate.Value _

                      And order.ShippedDate <= dtpEndDate.Value


This is easy at compile-time, but what if we want to check the date the order was entered instead of the date it was shipped?  In this case we’d have to write a separate query using order.OrderDate.  Doing this dynamically at runtime isn’t all that difficult if you’re just building up a SQL string, but how would we do this with LINQ?  Doesn’t LINQ require me to specify the criteria at compile time in order to construct the query?  Fortunately the answer’s no, LINQ supports constructing dynamic queries at runtime through the Expression Tree API and the Expression Compiler.


In Visual Studio 2008, any valid VB expression can be represented as an expression tree.  What we need to do is create an Expression Tree to represent the user’s criteria, and then pass it to the LINQ to SQL runtime to do the SQL translation.  So for the first part of our Where clause above it’d look something like this:

        Dim p = Expression.Parameter(GetType(Order), "")

        Dim order = GetType(Order).GetProperty("ShipCountry")

        Dim expr = Expression.Equal(Expression.PropertyOrField(p, order.Name), Expression.Constant("Germany"))

        Dim predicate = Expression.Lambda(Of Func(Of Order, Boolean))(expr, New ParameterExpression() {p})


>Yikes, that’s a mouthful…and we’ve only done 1/3 of the Where clause so far!  I definitely don’t want to write 12 lines just to construct a Where clause at runtime.  What I’d like to be able to do is write an extension method called CreateCondition that would allow me to construct the expression tree in one easy line, something like this:

        Dim condition1 = db.Orders.CreateCondition("ShipCountry", Compare.Equal, "Germany")


And then for ShippedDate we could make these two conditions:

        Dim startDate? = #1/1/1997#

        Dim endDate? = #1/31/1997#


        Dim condition2 = db.Orders.CreateCondition("ShippedDate", Compare.GreaterThanOrEqual, startDate)

        Dim condition3 = db.Orders.CreateCondition("ShippedDate", Compare.LessThanOrEqual, endDate)


(Note: There’s a similar sample in Beta2 called DynamicQueries which does a lot more than what we’re looking at here; think of this as an easier-to-write subset of DynamicQueries).


Notice that we passed in a String the first time, and a Nullable Date literal for the next two; we can do this because the CreateCondition method is generic and infers the type based on the parameter passed in.  We now need to combine the conditions into one big condition:

        Dim c = Condition.Combine(condition1, Compare.And, condition2, condition3)


Or we could take advantage of Operator Overloading and do it this way (equivalent to the line above):

        Dim c = condition1 And condition2 And condition3


Ok so now that we’ve constructed our Condition object, let’s use it to filter the data:

        'Filter out all Orders that don't match the Condition

        'Note that the query isn't executed yet to due to deferred execution

        Dim filteredQuery = db.Orders.Where(c)


        'We can now perform other operations (such as Order By) on filteredQuery

        Dim query = From row In filteredQuery _

                    Order By row.OrderDate, row.OrderID _

                    Select row


        'Executes the query and displays the results in DataGridView1

        DataGridView1.DataSource = query


So far so good, we’re using LINQ over a dynamically-constructed condition and everything’s strongly-typed.  We’re still only hitting the database once since deferred execution ensures the query doesn’t run until we actually enumerate the results (through databinding).  The Condition API has made it a lot easier to construct and compile the expression trees, but we’d still have to write a fair bit of code to construct these conditions based on user input.  That’s where the ConditionBuilder control comes in:





This allows the user to dynamically specify the criteria at runtime:



Notice that for Date fields we automatically get a DateTimePicker instead of a TextBox, and Booleans would result in a CheckBox.


Ok so we’ve covered what the user experience is at runtime, but how do we actually create the Condition API?  There’s a fair bit of documentation in the code below so I won’t go through all of it, but here’s the basics:

1.       There are three main classes: Condition, Condition(Of T), and Condition(Of T, S)

a.       Condition is an abstract class that is used to construct the generic versions.  By structuring it this way we get the benefits of generic type parameter inference – i.e. we don’t have to worry about passing the generic type parameters to the method; the factory method figures it out for us.

b.      Condition(Of T) is used to join multiple conditions together.  T is the element type (i.e. Order in the example above).

c.       Condition(Of T, S) is the simplest type; it represents an “object.propery <comparison> value” expression.  The type parameter S will be inferred to be the type of the value passed in (i.e. String, Date, Boolean etc…).


2.       For local execution of a query we compile the LambdaExpression to a delegate so that it can be executed in-memory.  The user can invoke this delegate by calling the Matches method.

'Compile the lambda expression into a delegate

del = DirectCast(LambdaExpr.Compile(), Func(Of T, Boolean))


3.       The extension methods at the bottom are defined on IQueryable(Of T) for remote execution, and IEnumerable(Of T) for local execution.


The complete solution is attached in the file below, note that you’ll need to update the ConnectionString in app.config to point to your version of Northwind.


Imports System.ComponentModel

Imports System.Linq.Expressions

Imports System.Runtime.CompilerServices


Public Module DynamicQuery

    Public MustInherit Class Condition


'Used to ensure we get the same instance of a particular ParameterExpression

'across multiple queries

Private Shared ParamTable As New Dictionary(Of String, ParameterExpression)


'The expression tree which will be passed to the LINQ to SQL runtime

Protected Friend LambdaExpr As LambdaExpression


'Enumerates all the different comparisons which can be performed

Public Enum Compare

    [Or] = ExpressionType.Or

    [And] = ExpressionType.And

    [Xor] = ExpressionType.ExclusiveOr

    [Not] = ExpressionType.Not

    Equal = ExpressionType.Equal

    [Like] = ExpressionType.TypeIs + 1

    NotEqual = ExpressionType.NotEqual

    [OrElse] = ExpressionType.OrElse

    [AndAlso] = ExpressionType.AndAlso

    LessThan = ExpressionType.LessThan

    GreaterThan = ExpressionType.GreaterThan

    LessThanOrEqual = ExpressionType.LessThanOrEqual

    GreaterThanOrEqual = ExpressionType.GreaterThanOrEqual

End Enum


'Constructs a Condition with T as the element type and S as the value's type

Public Shared Function Create(Of T, S)(ByVal dataSource As IEnumerable(Of T), _

                                       ByVal propertyName As String, _

                                       ByVal condType As Compare, _

                                       ByVal value As S) As Condition(Of T, S)


    Return New Condition(Of T, S)(propertyName, condType, value)

End Function


'Constructs a Condition with T as the element type and valueType as the value's type

'This is useful for situations where you won't know the value's type until runtime.

Public Shared Function Create(Of T)(ByVal dataSource As IEnumerable(Of T), _

                                    ByVal propertyName As String, _

                                    ByVal condType As Compare, _

                                    ByVal value As Object, _

                                    ByVal valueType As Type) As Condition(Of T)


    Return New Condition(Of T)(propertyName, condType, value, valueType)

End Function



''' <summary>

''' Creates a Condition which combines two other Conditions

''' </summary>

''' <typeparam name="T">The type the condition will execute against</typeparam>

''' <param name="cond1">The first Condition</param>

''' <param name="condType">The operator to use on the conditions</param>

''' <param name="cond2">The second Condition</param>

''' <returns>A new Condition which combines two Conditions into one according to the specified operator</returns>

''' <remarks></remarks>

Public Shared Function Combine(Of T)(ByVal cond1 As Condition(Of T), _

                                     ByVal condType As Compare, _

                                     ByVal cond2 As Condition(Of T)) As Condition(Of T)

    Return Condition(Of T).Combine(cond1, condType, cond2)

End Function


'Combines multiple conditions according to the specified operator

Public Shared Function Combine(Of T)(ByVal cond1 As Condition(Of T), _

                                     ByVal condType As Compare, _

                                     ByVal ParamArray conditions() As Condition(Of T)) As Condition(Of T)

    Return Condition(Of T).Combine(cond1, condType, conditions)

End Function


'Combines two Expressions according to the specified operator (condType)

Protected Shared Function CombineExpression(ByVal left As Expression, _

                                            ByVal condType As Compare, _

                                            ByVal right As Expression) As Expression


    'Join the Expressions based on the operator

    Select Case condType

        Case Compare.Or : Return Expression.Or(left, right)

        Case Compare.And : Return Expression.And(left, right)

        Case Compare.Xor : Return Expression.ExclusiveOr(left, right)

        Case Compare.Equal : Return Expression.Equal(left, right)

        Case Compare.OrElse : Return Expression.OrElse(left, right)

        Case Compare.AndAlso : Return Expression.AndAlso(left, right)

        Case Compare.NotEqual : Return Expression.NotEqual(left, right)

        Case Compare.LessThan : Return Expression.LessThan(left, right)

        Case Compare.GreaterThan : Return Expression.GreaterThan(left, right)

        Case Compare.LessThanOrEqual : Return Expression.LessThanOrEqual(left, right)

        Case Compare.GreaterThanOrEqual : Return Expression.GreaterThanOrEqual(left, right)

        Case Compare.Like

            'For the Like operator we encode a call to the LikeString method in the VB runtime

            Dim m = GetType(CompilerServices.Operators).GetMethod("LikeString")

            Return Expression.Call(m, left, right, Expression.Constant(CompareMethod.Binary))

        Case Else

            Throw New ArgumentException("Not a valid Condition Type", "condType", Nothing)

    End Select

End Function


'Since both type parameters must be the same, we can turn what would normally

'be a Func(Of T, T, Boolean) into a Func(Of T, Boolean)

Protected Shared Function CombineFunc(Of T)(ByVal d1 As Func(Of T, Boolean), _

                                            ByVal condType As Compare, _

                                            ByVal d2 As Func(Of T, Boolean)) As Func(Of T, Boolean)


    'Return a delegate which combines delegates d1 and d2

    Select Case condType

        Case Compare.Or : Return Function(x) d1(x) Or d2(x)

        Case Compare.And : Return Function(x) d1(x) And d2(x)

        Case Compare.Xor : Return Function(x) d1(x) Xor d2(x)

        Case Compare.Equal : Return Function(x) d1(x) = d2(x)

        Case Compare.OrElse : Return Function(x) d1(x) OrElse d2(x)

        Case Compare.AndAlso : Return Function(x) d1(x) AndAlso d2(x)

        Case Compare.NotEqual : Return Function(x) d1(x) <> d2(x)

        Case Compare.LessThan : Return Function(x) d1(x) < d2(x)

        Case Compare.GreaterThan : Return Function(x) d1(x) > d2(x)

        Case Compare.LessThanOrEqual : Return Function(x) d1(x) <= d2(x)

        Case Compare.GreaterThanOrEqual : Return Function(x) d1(x) >= d2(x)

        Case Else

            Throw New ArgumentException("Not a valid Condition Type", "condType")

    End Select

End Function


'Guarantees that we get the same instance of a ParameterExpression for a given type t.

Protected Shared Function GetParamInstance(ByVal dataType As Type) As ParameterExpression


    'Parameters are matched by reference, not by name, so we cache the instances in a Dictionary.

    If Not ParamTable.ContainsKey(dataType.Name) Then

        ParamTable.Add(dataType.Name, Expression.Parameter(dataType, dataType.Name))

    End If


    Return ParamTable.Item(dataType.Name)

End Function


    End Class


    Public Class Condition(Of T) : Inherits Condition


'Delegate that contains a compiled expression tree which can be run locally

Friend del As Func(Of T, Boolean)


Friend Sub New()

End Sub


Friend Sub New(ByVal propName As String, ByVal condType As Compare, ByVal value As Object, ByVal valueType As Type)

    'Split the string to handle nested property access

    Dim s = propName.Split("."c)


    'Get the PropertyInfo instance for propName

    Dim pInfo = GetType(T).GetProperty(s(0))

    Dim paramExpr = GetParamInstance(GetType(T))

    Dim callExpr = Expression.MakeMemberAccess(paramExpr, pInfo)


    'For each member specified, construct the additional MemberAccessExpression

    'For example, if the user says "myCustomer.Order.OrderID = 4" we need an

    'additional MemberAccessExpression for "Order.OrderID = 4"

    For i = 1 To UBound(s)

        pInfo = pInfo.PropertyType.GetProperty(s(i))

        callExpr = Expression.MakeMemberAccess(callExpr, pInfo)



    'ConstantExpression representing the value on the left side of the operator

    Dim valueExpr = Expression.Constant(value, valueType)


    Dim b As Expression = CombineExpression(callExpr, condType, valueExpr)

    LambdaExpr = Expression.Lambda(Of Func(Of T, Boolean))(b, New ParameterExpression() {paramExpr})


    'Compile the lambda expression into a delegate

    del = DirectCast(LambdaExpr.Compile(), Func(Of T, Boolean))

End Sub


'Combines two conditions according to the specified operator

Friend Overloads Shared Function Combine(ByVal cond1 As Condition(Of T), _

                                         ByVal condType As Compare, _

                                         ByVal cond2 As Condition(Of T)) As Condition(Of T)

    Dim c As New Condition(Of T)


    Dim b As Expression = CombineExpression(cond1.LambdaExpr.Body, _

                                            condType, _



    Dim paramExpr() = New ParameterExpression() {GetParamInstance(GetType(T))}


    'Create the LambdaExpression and compile the delegate

    c.LambdaExpr = Expression.Lambda(Of Func(Of T, Boolean))(b, paramExpr)

    c.del = Condition.CombineFunc(cond1.del, condType, cond2.del)


    Return c

End Function


'Combines multiple conditions according to the specified operator

Friend Overloads Shared Function Combine(ByVal cond1 As Condition(Of T), _

                                         ByVal condType As Compare, _

                                         ByVal ParamArray conditions() As Condition(Of T)) As Condition(Of T)

    Dim finalCond = cond1

    For Each c In conditions

        finalCond = Condition.Combine(finalCond, condType, c)



    Return finalCond

End Function


'Run query locally instead of remotely

Public Function Matches(ByVal row As T) As Boolean

    Return del(row) 'passes the row into the delegate to see if it's a match

End Function


'Overloaded operators - allows syntax like "(condition1 Or condition2) And condition3"

Public Shared Operator And(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)

    Return Condition.Combine(c1, Compare.And, c2)

End Operator


Public Shared Operator Or(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)

    Return Condition.Combine(c1, Compare.Or, c2)

End Operator


Public Shared Operator Xor(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)

    Return Condition.Combine(c1, Compare.Xor, c2)

End Operator


    End Class


    'Represents a condition like "object.Property = value"

    'In this case object is of type T, and value is of type S


    'Even though most of the logic for this is already in the base class,

    'defining a second generic parameter means the user doesn't have to

    'pass in a System.Type - it can just be inferred.

    Public Class Condition(Of T, S) : Inherits Condition(Of T)


        Friend Sub New(ByVal propName As String, ByVal condType As Compare, ByVal value As S)

            MyBase.New(propName, condType, value, GetType(S))

        End Sub


    End Class


#End Region


#Region "Extension Methods"


    'Filters an IQueryable(Of T) according to the specified condition

    <Extension()> _

    Public Function Where(Of T)(ByVal source As IQueryable(Of T), _

                                ByVal condition As Condition(Of T)) As IQueryable(Of T)


        Dim callExpr = Expression.Call(GetType(Queryable), "Where", _

                                       New Type() {source.ElementType}, source.Expression, _



        Return CType(source.Provider.CreateQuery(callExpr), IQueryable(Of T))

    End Function


    'Filters an IEnumerable(Of T) according to the specified condition

    <Extension()> _

    Public Function Where(Of T)(ByVal source As IEnumerable(Of T), _

                                ByVal condition As Condition(Of T)) As IEnumerable(Of T)

        Return source.Where(condition.del)

    End Function



    'Extension method that can be called off any type that implements IEnumerable(Of T),

    'which constructs a Condition with T as the element type and S as the value's type

    <Extension(), EditorBrowsable(EditorBrowsableState.Always)> _

    Public Function CreateCondition(Of T, S)(ByVal dataSource As IEnumerable(Of T), _

                                             ByVal propName As String, _

                                             ByVal condType As condition.Compare, _

                                             ByVal value As S) As Condition(Of T, S)


        Return Condition.Create(dataSource, propName, condType, value)

    End Function


#End Region


End Module

The complete sample (including the code for the ConditionBuilder control) is available in the attached .zip file below.  While we’ve got the basics going pretty well, there’s definitely a lot of things you can extend the sample to do, such as:

1.       Add support for Grouping conditions into the ConditionBuilder – right now you’d have to drop down to the API to construct something like (condition1 Or condition2) And condition3.

2.       Add a .Select extension method to dynamically control which fields you bring back from the database.  This will improve performance in situations where you don’t want to return all fields, but you’ll lose strong-typing of your results since the method will have to return IQueryable instead of IQueryable(Of T).

3.       Add extension methods for OrderBy, Take, Skip, Distinct etc…  Each of these methods can still return strongly-typed results since they don’t change the element type; if you pass in T they return T.

4.       The Condition API supports nested property access (i.e. Where customer.Orders.Salesman.Name = “Bob”), but it’d take a bit more work to surface this in the ConditionBuilder control.


That’s everything, now all you have to do is download Beta2 and try it out!



Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • Hi,

    there is a way for building queries dynamically without the need to build any expression trees manually and withot loosing the type safety (even though your sample is 'safer' than the DynamicQueries sample, there are still a few possible problems). I wrote about it here: It is written in C#, but I guess that C# and VB have similar expressive power, so I would expect that same thing could be done in VB as well (but correct me if I'm wrong!).

  • So I decided to post a summary of all the content the VB team members, including myself, have created

  • Hi there! My name is Tim, and I'm the dev lead for the VB compiler team. Recently Amanda convinced me

  • Time for another weekly roundup of news that focuses on .NET, agile and general development related content

  • Last week I got the chance to visit the Toronto .NET User Group and give a talk on all the great new

  • 原文链接:

  • Hi. Thanks Great. But It doesn't seem to work for the Like operator between Strings.

    Can you fix that?


  • Hey Bruno,

    I can't seem to repro a problem with the Like operator - either in the ConditionBuilder control or in the API itself.

    Dim c = db.Orders.CreateCondition("CustomerID", Compare.Like, "A*")

    Dim filteredQuery = db.Orders.Where(c)


    Likewise in the control if I choose the Like operator from the dropdown and pass a valid string I get normal VB-Like-Operator semantics.

    Could you post more info on the case that's not working for you?


    Jonathan Aneja

    Program Manager, VB Team

  • Hi Jonathan. First, Thank you for such a fast reply.

    What I have is a web form where the user can fill it up and select in a cbox if he wants the condition for that field to be AND or OR.

    For example the FirstName field. If I select OR, the query whould be:

    Dim condition1 = db.Members.CreateCondition("FName", Compare.Like, fName)

    Dim fdx = condition1 And condition2 _

               'And condition3 And condition4 And _

               'condition5 And condition6 And condition7 And condition8 And _

               'condition9 And condition10

    Dim filteredQuery = db.Members.Where(fdx)

    Dim query = From row In filteredQuery _

    Order By row.MemberGUID, row.BaseMemberID _

    Select row

    When the Query is executed it returns:

    "LINQ to Entities does not recognize the method 'Boolean LikeString(System.String, System.String, Microsoft.VisualBasic.CompareMethod)' method, and this method cannot be translated into a store expression."

    I've read that Linq doesn't have the Like operator yet. So What you think that I can do? Am I doing this the right way?

    I'm now thinking in building the where clause as a String... and executed via Linq to Entities...

    But I prefered to use you way.

  • ah, ok, there's two seperate issues here.  LINQ and the DynamicCondition API both do support the Like operator, but in this case the particular LINQ *provider* doesn't support translating Like.

    LINQ to SQL and LINQ to Objects support Like, but LINQ to Entities doesn't.  I'll follow up with the Entities team and see if it's something they can add support for.  You can try using Substring or some of the BCL string functions as I'm pretty sure they'll support those.



  • Hi Jonathan. Just Another small question.

    I have a Sql user function that calcs a persons age and I wanted to use that in a query.

    I've heard that Model defined functions are not in the shipping Entity Framework (only in .NET 4.0)

    But I still whated to something like this:

    SELECT * FROM Members as m

    Where m.FName like @FName or

    m.LName like @LName or

    ufn_GetAge ( m.DOB, GETDATE() ) >= @AgeMin or

    ufn_GetAge ( m.DOB, GETDATE() ) <= @AgeMax

    I know that I cant do that with Link to Entities. But I was trying to do it with Entity to SQL like this:

    Dim queryString As String = "SELECT VALUE m FROM Members as m

    Where m.FName = @FName and

    m.LName = @LName and

    ufn_GetAge] ( m.DOB, GETDATE() ) >= @AgeMin and

    [dbo].[ufn_GetAge] ( m.DOB, GETDATE() ) <= @AgeMax

    Dim membersQuery As New ObjectQuery(Of Members)(queryString, db, MergeOption.NoTracking)



    But That Doesnt seem to work.

    Can you point me out on the right direction?

    Thank you for your help.

  • Hi I'm trying to use the library, but i fail when I combine 2 Like clauses together with and And. When I combine them with an OR it works, what is a bit weird for me.

    Can anybody help me with this?

    Thanks for any help!

    Here's my code:

    If Not city.EndsWith("*") Then

    city &= "*"

    End If

    If Not name.EndsWith("*") Then

    name &= "*"

    End If

    Using db = New DataLayer.SIS2009Db.SIS2009DataContext()

    Dim filter = Common.Condition.Create(db.Dealerships, "ds_City", Common.Condition.Compare.Like, city)

    Dim filter2 = Common.Condition.Create(db.Dealerships, "ds_Name", Common.Condition.Compare.Like, name)

    Dim endfilter = (filter And filter2)

    Return db.Dealerships.Where(endfilter).ToList()

    End Using

  • Your code looks right, are you sure the data in the database matches both conditions?

    I just tried this on Northwind and it works fine:

           Dim c = db.Orders.CreateCondition("CustomerID", Compare.Like, "A*")

           Dim c2 = db.Orders.CreateCondition("ShipAddress", Compare.Like, "B*")

           Dim filteredQuery = db.Orders.Where(c And c2)

    Try using DataContext.Log or SQL Profiler to see what SQL string is being sent to the database.



  • WRT ufn_GetAge:  eSQL

    GetDate() should be available under SqlServer namespace

    you can import GetAge into ssdl like this:

    <Function Name="GetAge" StoreFunctionName="ufn_GetAge " ReturnType="Int">

       <Parameter Name="dob" Type="DateTime" Mode="In" />

       <Parameter Name="today" Type="DateTime" Mode="In" />


    Once in ssdl, the function should be available in eSQL queries under the namespace defined in SSDL

  • I tried this following query:

       Dim condition1 = ds.Code.CreateCondition("OrgId", Compare.Equal, 1)

       Dim condition2 = ds.Code.CreateCondition("CodeType", Compare.Equal, "SECCOLOR")

       Dim c = condition1 And condition2

       Dim filteredQuery = ds.Code.Where(c)

       Dim query = From row In filteredQuery _

                        Order By row _

                        Select row

    I get the following error in the CombineExpression function:

    The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'.

Page 1 of 3 (34 items) 123