{"id":5553,"date":"2007-08-29T18:51:00","date_gmt":"2007-08-29T18:51:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/08\/29\/implementing-dynamic-searching-using-linq\/"},"modified":"2024-07-05T14:41:30","modified_gmt":"2024-07-05T21:41:30","slug":"implementing-dynamic-searching-using-linq","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/implementing-dynamic-searching-using-linq\/","title":{"rendered":"Implementing Dynamic Searching Using LINQ"},"content":{"rendered":"

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:<\/span><\/span><\/font><\/font><\/p>\n

<\/span><\/p>\n

 <\/font><\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

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<\/span> a given country within a user-specified timeframe<\/span>:<\/font><\/font><\/p>\n

        Dim<\/span> query = From<\/span> order In<\/span> db.Orders _<\/span><\/p>\n

                    Where<\/span> order.ShipCountry = txt<\/span>Coun<\/span>try.Text _<\/span><\/p>\n

                      And<\/span> order.ShippedDate >= dtpStartDate.Value<\/span> _<\/span><\/p>\n

                      And<\/span> order.ShippedDate <= dtpEndDate.Value<\/span><\/span><\/p>\n

 <\/font><\/p>\n

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

 <\/font><\/p>\n

<\/p>\n

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\u2019s 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\u2019d look something like this:<\/span><\/font><\/p>\n

        Dim<\/span> p = Expression.Parameter(GetType<\/span>(Order), “”<\/span>)<\/span><\/p>\n

        Dim<\/span> order = GetType<\/span>(Order).GetProperty(“ShipCountry”<\/span>)<\/span><\/p>\n

        Dim<\/span> expr = Expression.Equal(Expression.PropertyOrField(p, order.Name), Expression.Constant(“Germany”<\/span>))<\/span><\/p>\n

        Dim<\/span> predicate = Expression.Lambda(Of<\/span> Func(Of<\/span> Order, Boolean<\/span>))(expr, New<\/span> ParameterExpression() {p})<\/span><\/p>\n

 <\/font><\/p>\n

>Yikes, that\u2019s a mouthful\u2026and we\u2019ve only done 1\/3 of the Where clause so far<\/span>!  I definitely don\u2019t want to write 12 lines just to construct a Where clause at runtime.  What I\u2019d like to be able to do is write an extension method called CreateCondition that would allow me to construct the expression tree<\/span> in one easy line, something like this:<\/font><\/p>\n

        Dim<\/span> condition1 = db.Orders.CreateCondition(“ShipCountry”<\/span>, Compare.Equal, “Germany”<\/span>)<\/span><\/p>\n

 <\/font><\/p>\n

And then for ShippedDate we could make these two conditions:<\/font><\/p>\n

        Dim<\/span> startDate? = #1\/1\/1997#<\/span><\/p>\n

        Dim<\/span> endDate? = #1\/31\/1997#<\/span><\/p>\n

 <\/span><\/p>\n

        Dim<\/span> condition2 = db.Orders.CreateCondition(“ShippedDate”<\/span>, Compare.GreaterThanOrEqual, startDate)<\/span><\/p>\n

        Dim<\/span> condition3 = db.Orders.CreateCondition(“ShippedDate”<\/span>, Compare.LessThanOrEqual, endDate)<\/span><\/p>\n

 <\/font><\/p>\n

(Note: There\u2019s a similar sample in Beta2 called<\/span> <\/span>DynamicQueries which does a lot more than what we\u2019re looking at here; think of this as an easier-to-write subset of DynamicQueries).<\/span><\/font><\/font><\/p>\n

 <\/font><\/span><\/p>\n

Notice that we passed in a String the first time, and a Nullable D<\/span>ate 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:<\/font><\/font><\/p>\n

        Dim<\/span> c = Condition.Combine(condition1, Compare.And, condition2, condition3)<\/span><\/p>\n

 <\/font><\/p>\n

Or we could take advantage of Operator Overloading and do it this way<\/span> <\/span>(equivalent to the line above):<\/span><\/font><\/font><\/p>\n

        Dim<\/span> c = condition1 And<\/span> condition2 And<\/span> condition3<\/span><\/p>\n

 <\/font><\/p>\n

Ok so now that we\u2019ve constructed our Condition object, let\u2019s use it to filter the data:<\/font><\/font><\/span><\/p>\n

        ‘Filter out all Orders that don’t match the Condition<\/span><\/span><\/p>\n

        ‘Note that the query isn’t executed yet to due to deferred execution<\/span><\/span><\/p>\n

        Dim<\/span> filteredQuery = db.Orders.Where(c)<\/span><\/p>\n

 <\/span><\/p>\n

        ‘We can now perform other operations (such as Order By) on filteredQuery<\/span><\/span><\/p>\n

        Dim<\/span> query = From<\/span> row In<\/span> filteredQuery _<\/span><\/p>\n

                    Order<\/span> By<\/span> row.OrderDate, row.OrderID _<\/span><\/p>\n

                    Select<\/span> row<\/span><\/p>\n

 <\/span><\/p>\n

        ‘Executes the query and displays the results in DataGridView1<\/span><\/span><\/p>\n

        DataGridView1.DataSource = query<\/span><\/p>\n

 <\/font><\/p>\n

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

<\/font> <\/p>\n

<\/span><\/p>\n

 <\/font><\/span> <\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

This allows the user to dynamically specify the criteria at runtime:<\/font><\/p>\n

 <\/p>\n

<\/p>\n

 <\/font><\/p>\n

Notice that for Date fields we automatically get a DateTimePicker instead of a TextBox, and Booleans would result in a CheckBox.<\/font><\/p>\n

 <\/font><\/p>\n

Ok so we\u2019ve covered what the user experience is at runtime, but how do we actually create the Condition API?  There\u2019s a fair bit of documentation in the code below so I won\u2019t go through all of it, but here\u2019s the basics:<\/font><\/p>\n

1.<\/font>       <\/span><\/span><\/span>There are three main classes: Condition, Condition(Of T), and Condition(Of T, S)<\/font><\/p>\n

a.<\/font>       <\/span><\/span><\/span>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 \u2013 i.e. we don\u2019t have to worry about passing the generic type parameters to the method; the factory method figures it out for us.<\/font><\/p>\n

b.<\/font>      <\/span><\/span><\/span>Condition(Of T) is used to join multiple conditions together.  T is the element type (i.e. Order in the example above).<\/font><\/p>\n

c.<\/font>       <\/span><\/span><\/span>Condition(Of T, S) is the simplest type; it represents an \u201cobject.propery <comparison> value\u201d expression.  The type parameter S will be inferred to be the type of the value passed in (i.e. String, Date, Boolean etc\u2026).<\/font><\/p>\n

 <\/font><\/p>\n

2.<\/font>       <\/span><\/span><\/span>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.<\/font><\/p>\n

‘Compile the lambda expression into a delegate<\/span><\/p>\n

del = DirectCast<\/span>(LambdaExpr.Compile(), Func(Of<\/span> T, Boolean<\/span>))<\/span><\/p>\n

 <\/span><\/p>\n

3.<\/font>       <\/span><\/span><\/span>The extension methods at the bottom are defined on IQueryable(Of T) for remote execution, and IEnumerable(Of T) for local execution.<\/font><\/p>\n

 <\/font><\/span><\/p>\n

The complete solution is attached in the file DynamicCondition.zip below, note that you\u2019ll need to update the ConnectionString in app.config to point to your version of Northwind.<\/i><\/font><\/font><\/span><\/p>\n

<\/span><\/span> <\/p>\n

<\/p>\n

Imports<\/span> System.ComponentModel<\/span><\/span><\/p>\n

Imports<\/span> System.Linq.Expressions<\/span><\/span><\/p>\n

Imports<\/span> System.Runtime.CompilerServices<\/span><\/span><\/p>\n

 <\/span><\/p>\n

Public<\/span> <\/span>Module<\/span> DynamicQuery<\/span><\/span><\/p>\n

    <\/span>Public<\/span> <\/span>MustInherit<\/span> <\/span>Class<\/span> Condition<\/span><\/p>\n

 <\/span><\/p>\n

‘Used to ensure we get the same instance of a particular ParameterExpression<\/span><\/p>\n

‘across multiple queries<\/span><\/p>\n

Private<\/span> <\/span>Shared<\/span> ParamTable <\/span>As<\/span> <\/span>New<\/span> Dictionary(<\/span>Of<\/span> <\/span>String<\/span>, ParameterExpression)<\/span><\/p>\n

 <\/span><\/p>\n

‘The expression tree which will be passed to the LINQ to SQL runtime<\/span><\/p>\n

Protected<\/span> <\/span>Friend<\/span> LambdaExpr <\/span>As<\/span> LambdaExpression<\/span><\/p>\n

 <\/span><\/p>\n

‘Enumerates all the different comparisons which can be performed<\/span><\/p>\n

Public<\/span> <\/span>Enum<\/span> Compare<\/span><\/p>\n

    <\/span>[Or] = ExpressionType.Or<\/span><\/p>\n

    [And] = ExpressionType.And<\/span><\/p>\n

    [Xor] = ExpressionType.ExclusiveOr<\/span><\/p>\n

    [Not] = ExpressionType.Not<\/span><\/p>\n

    Equal = ExpressionType.Equal<\/span><\/p>\n

    [Like] = ExpressionType.TypeIs + 1<\/span><\/p>\n

    NotEqual = ExpressionType.NotEqual<\/span><\/p>\n

    [OrElse] = ExpressionType.OrElse<\/span><\/p>\n

    [AndAlso] = ExpressionType.AndAlso<\/span><\/p>\n

    LessThan = ExpressionType.LessThan<\/span><\/p>\n

    GreaterThan = ExpressionType.GreaterThan<\/span><\/p>\n

    LessThanOrEqual = ExpressionType.LessThanOrEqual<\/span><\/p>\n

    GreaterThanOrEqual = ExpressionType.GreaterThanOrEqual<\/span><\/p>\n

End<\/span> <\/span>Enum<\/span><\/p>\n

 <\/span><\/p>\n

‘Constructs a Condition with T as the element type and S as the value’s type<\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> Create(<\/span>Of<\/span> T, S)(<\/span>ByVal<\/span> dataSource <\/span>As<\/span> IEnumerable(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                       <\/span>ByVal<\/span> propertyName <\/span>As<\/span> <\/span>String<\/span>, _<\/span><\/p>\n

                                       <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                       <\/span>ByVal<\/span> value <\/span>As<\/span> S) <\/span>As<\/span> Condition(<\/span>Of<\/span> T, S)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Return<\/span> <\/span>New<\/span> Condition(<\/span>Of<\/span> T, S)(propertyName, condType, value)<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Constructs a Condition with T as the element type and valueType as the value’s type<\/span><\/p>\n

‘This is useful for situations where you won’t know the value’s type until runtime.<\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> Create(<\/span>Of<\/span> T)(<\/span>ByVal<\/span> dataSource <\/span>As<\/span> IEnumerable(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                    <\/span>ByVal<\/span> propertyName <\/span>As<\/span> <\/span>String<\/span>, _<\/span><\/p>\n

                                    <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                    <\/span>ByVal<\/span> value <\/span>As<\/span> <\/span>Object<\/span>, _<\/span><\/p>\n

                                    <\/span>ByVal<\/span> valueType <\/span>As<\/span> Type) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Return<\/span> <\/span>New<\/span> Condition(<\/span>Of<\/span> T)(propertyName, condType, value, valueType)<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

”’ <\/span><summary><\/span><\/p>\n

”’ Creates a Condition which combines two other Conditions<\/span><\/p>\n

”’ <\/span><\/summary><\/span><\/p>\n

”’ <\/span><typeparam name=”T”><\/span>The type the condition will execute against<\/span><\/typeparam><\/span><\/p>\n

”’ <\/span><param name=”cond1″><\/span>The first Condition<\/span><\/param><\/span><\/p>\n

”’ <\/span><param name=”condType”><\/span>The operator to use on the conditions<\/span><\/param><\/span><\/p>\n

”’ <\/span><param name=”cond2″><\/span>The second Condition<\/span><\/param><\/span><\/p>\n

”’ <\/span><returns><\/span>A new Condition which combines two Conditions into one according to the specified operator<\/span><\/returns><\/span><\/p>\n

”’ <\/span><remarks><\/remarks><\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> Combine(<\/span>Of<\/span> T)(<\/span>ByVal<\/span> cond1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                     <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                     <\/span>ByVal<\/span> cond2 <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Return<\/span> Condition(<\/span>Of<\/span> T).Combine(cond1, condType, cond2)<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Combines multiple conditions according to the specified operator<\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> Combine(<\/span>Of<\/span> T)(<\/span>ByVal<\/span> cond1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                     <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                     <\/span>ByVal<\/span> <\/span>ParamArray<\/span> conditions() <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Return<\/span> Condition(<\/span>Of<\/span> T).Combine(cond1, condType, conditions)<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Combines two Expressions according to the specified operator (condType)<\/span><\/p>\n

Protected<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> CombineExpression(<\/span>ByVal<\/span> left <\/span>As<\/span> Expression, _<\/span><\/p>\n

                                            <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                            <\/span>ByVal<\/span> right <\/span>As<\/span> Expression) <\/span>As<\/span> Expression<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Join the Expressions based on the operator<\/span><\/p>\n

    <\/span>Select<\/span> <\/span>Case<\/span> condType<\/span><\/p>\n

        <\/span><\/span>Case<\/span> Compare.Or : <\/span>Return<\/span> Expression.Or(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.And : <\/span>Return<\/span> Expression.And(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.Xor : <\/span>Return<\/span> Expression.ExclusiveOr(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.Equal : <\/span>Return<\/span> Expression.Equal(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.OrElse : <\/span>Return<\/span> Expression.OrElse(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.AndAlso : <\/span>Return<\/span> Expression.AndAlso(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.NotEqual : <\/span>Return<\/span> Expression.NotEqual(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.LessThan : <\/span>Return<\/span> Expression.LessThan(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.GreaterThan : <\/span>Return<\/span> Expression.GreaterThan(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.LessThanOrEqual : <\/span>Return<\/span> Expression.LessThanOrEqual(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.GreaterThanOrEqual : <\/span>Return<\/span> Expression.GreaterThanOrEqual(left, right)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.Like<\/span><\/p>\n

            <\/span><\/span>‘For the Like operator we encode a call to the LikeString method in the VB runtime<\/span><\/p>\n

            <\/span>Dim<\/span> m = <\/span>GetType<\/span>(CompilerServices.Operators).GetMethod(<\/span>“LikeString”<\/span>)<\/span><\/p>\n

            <\/span>Return<\/span> Expression.Call(m, left, right, Expression.Constant(CompareMethod.Binary))<\/span><\/p>\n

        <\/span>Case<\/span> <\/span>Else<\/span><\/p>\n

            <\/span>Throw<\/span> <\/span>New<\/span> ArgumentException(<\/span>“Not a valid Condition Type”<\/span>, <\/span>“condType”<\/span>, <\/span>Nothing<\/span>)<\/span><\/p>\n

    <\/span>End<\/span> <\/span>Select<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Since both type parameters must be the same, we can turn what would normally<\/span><\/p>\n

‘be a Func(Of T, T, Boolean) into a Func(Of T, Boolean)<\/span><\/p>\n

Protected<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> CombineFunc(<\/span>Of<\/span> T)(<\/span>ByVal<\/span> d1 <\/span>As<\/span> Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>), _<\/span><\/p>\n

                                            <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                            <\/span>ByVal<\/span> d2 <\/span>As<\/span> Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>)) <\/span>As<\/span> Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Return a delegate which combines delegates d1 and d2<\/span><\/p>\n

    <\/span>Select<\/span> <\/span>Case<\/span> condType<\/span><\/p>\n

        <\/span><\/span>Case<\/span> Compare.Or : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <\/span>Or<\/span> d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.And : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <\/span>And<\/span> d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.Xor : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <\/span>Xor<\/span> d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.Equal : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) = d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.OrElse : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <\/span>OrElse<\/span> d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.AndAlso : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <\/span>AndAlso<\/span> d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.NotEqual : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <> d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.LessThan : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) < d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.GreaterThan : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) > d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.LessThanOrEqual : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) <= d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> Compare.GreaterThanOrEqual : <\/span>Return<\/span> <\/span>Function<\/span>(x) d1(x) >= d2(x)<\/span><\/p>\n

        <\/span>Case<\/span> <\/span>Else<\/span><\/p>\n

            <\/span><\/span>Throw<\/span> <\/span>New<\/span> ArgumentException(<\/span>“Not a valid Condition Type”<\/span>, <\/span>“condType”<\/span>)<\/span><\/p>\n

    <\/span>End<\/span> <\/span>Select<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Guarantees that we get the same instance of a ParameterExpression for a given type t.<\/span><\/p>\n

Protected<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> GetParamInstance(<\/span>ByVal<\/span> dataType <\/span>As<\/span> Type) <\/span>As<\/span> ParameterExpression<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Parameters are matched by reference, not by name, so we cache the instances in a Dictionary.<\/span><\/p>\n

    <\/span>If<\/span> <\/span>Not<\/span> ParamTable.ContainsKey(dataType.Name) <\/span>Then<\/span><\/p>\n

        <\/span>ParamTable.Add(dataType.Name, Expression.Parameter(dataType, dataType.Name))<\/span><\/p>\n

    <\/span>End<\/span> <\/span>If<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Return<\/span> ParamTable.Item(dataType.Name)<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>End<\/span> <\/span>Class<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Public<\/span> <\/span>Class<\/span> Condition(<\/span>Of<\/span> T) : <\/span>Inherits<\/span> Condition<\/span><\/p>\n

 <\/span><\/p>\n

‘Delegate that contains a compiled expression tree which can be run locally<\/span><\/p>\n

Friend<\/span> del <\/span>As<\/span> Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>)<\/span><\/p>\n

 <\/span><\/p>\n

Friend<\/span> <\/span>Sub<\/span> <\/span>New<\/span>()<\/span><\/p>\n

End<\/span> <\/span>Sub<\/span><\/p>\n

 <\/span><\/p>\n

Friend<\/span> <\/span>Sub<\/span> <\/span>New<\/span>(<\/span>ByVal<\/span> propName <\/span>As<\/span> <\/span>String<\/span>, <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, <\/span>ByVal<\/span> value <\/span>As<\/span> <\/span>Object<\/span>, <\/span>ByVal<\/span> valueType <\/span>As<\/span> Type)<\/span><\/p>\n

    <\/span>‘Split the string to handle nested property access<\/span><\/p>\n

    <\/span>Dim<\/span> s = propName.Split(<\/span>“.”c<\/span>)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Get the PropertyInfo instance for propName<\/span><\/p>\n

    <\/span>Dim<\/span> pInfo = <\/span>GetType<\/span>(T).GetProperty(s(0))<\/span><\/p>\n

    <\/span>Dim<\/span> paramExpr = GetParamInstance(<\/span>GetType<\/span>(T))<\/span><\/p>\n

    <\/span>Dim<\/span> callExpr = Expression.MakeMemberAccess(paramExpr, pInfo)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘For each member specified, construct the additional MemberAccessExpression<\/span><\/p>\n

    <\/span>‘For example, if the user says “myCustomer.Order.OrderID = 4” we need an<\/span><\/p>\n

    <\/span>‘additional MemberAccessExpression for “Order.OrderID = 4”<\/span><\/p>\n

    <\/span>For<\/span> i = 1 <\/span>To<\/span> UBound(s)<\/span><\/p>\n

        <\/span>pInfo = pInfo.PropertyType.GetProperty(s(i))<\/span><\/p>\n

        callExpr = Expression.MakeMemberAccess(callExpr, pInfo)<\/span><\/p>\n

    <\/span>Next<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘ConstantExpression representing the value on the left side of the operator<\/span><\/p>\n

    <\/span>Dim<\/span> valueExpr = Expression.Constant(value, valueType)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Dim<\/span> b <\/span>As<\/span> Expression = CombineExpression(callExpr, condType, valueExpr)<\/span><\/p>\n

    LambdaExpr = Expression.Lambda(<\/span>Of<\/span> Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>))(b, <\/span>New<\/span> ParameterExpression() {paramExpr})<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Compile the lambda expression into a delegate<\/span><\/p>\n

    del = <\/span>DirectCast<\/span>(LambdaExpr.Compile(), Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>))<\/span><\/p>\n

End<\/span> <\/span>Sub<\/span><\/p>\n

 <\/span><\/p>\n

‘Combines two conditions according to the specified operator<\/span><\/p>\n

Friend<\/span> <\/span>Overloads<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> Combine(<\/span>ByVal<\/span> cond1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                         <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                         <\/span>ByVal<\/span> cond2 <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Dim<\/span> c <\/span>As<\/span> <\/span>New<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Dim<\/span> b <\/span>As<\/span> Expression = CombineExpression(cond1.LambdaExpr.Body, _<\/span><\/p>\n

                                            condType, _<\/span><\/p>\n

                                            cond2.LambdaExpr.Body)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Dim<\/span> paramExpr() = <\/span>New<\/span> ParameterExpression() {GetParamInstance(<\/span>GetType<\/span>(T))}<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Create the LambdaExpression and compile the delegate<\/span><\/p>\n

    c.LambdaExpr = Expression.Lambda(<\/span>Of<\/span> Func(<\/span>Of<\/span> T, <\/span>Boolean<\/span>))(b, paramExpr)<\/span><\/p>\n

    c.del = Condition.CombineFunc(cond1.del, condType, cond2.del)<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Return<\/span> c<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Combines multiple conditions according to the specified operator<\/span><\/p>\n

Friend<\/span> <\/span>Overloads<\/span> <\/span>Shared<\/span> <\/span>Function<\/span> Combine(<\/span>ByVal<\/span> cond1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                         <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, _<\/span><\/p>\n

                                         <\/span>ByVal<\/span> <\/span>ParamArray<\/span> conditions() <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Dim<\/span> finalCond = cond1<\/span><\/p>\n

    <\/span>For<\/span> <\/span>Each<\/span> c <\/span>In<\/span> conditions<\/span><\/p>\n

        <\/span>finalCond = Condition.Combine(finalCond, condType, c)<\/span><\/p>\n

    <\/span>Next<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>Return<\/span> finalCond<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Run query locally instead of remotely<\/span><\/p>\n

Public<\/span> <\/span>Function<\/span> Matches(<\/span>ByVal<\/span> row <\/span>As<\/span> T) <\/span>As<\/span> <\/span>Boolean<\/span><\/p>\n

    <\/span>Return<\/span> del(row) <\/span>‘passes the row into the delegate to see if it’s a match<\/span><\/p>\n

End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

‘Overloaded operators – allows syntax like “(condition1 Or condition2) And condition3”<\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Operator<\/span> <\/span>And<\/span>(<\/span>ByVal<\/span> c1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), <\/span>ByVal<\/span> c2 <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Return<\/span> Condition.Combine(c1, Compare.And, c2)<\/span><\/p>\n

End<\/span> <\/span>Operator<\/span><\/p>\n

 <\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Operator<\/span> <\/span>Or<\/span>(<\/span>ByVal<\/span> c1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), <\/span>ByVal<\/span> c2 <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Return<\/span> Condition.Combine(c1, Compare.Or, c2)<\/span><\/p>\n

End<\/span> <\/span>Operator<\/span><\/p>\n

 <\/span><\/p>\n

Public<\/span> <\/span>Shared<\/span> <\/span>Operator<\/span> <\/span>Xor<\/span>(<\/span>ByVal<\/span> c1 <\/span>As<\/span> Condition(<\/span>Of<\/span> T), <\/span>ByVal<\/span> c2 <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

    <\/span>Return<\/span> Condition.Combine(c1, Compare.Xor, c2)<\/span><\/p>\n

End<\/span> <\/span>Operator<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>End<\/span> <\/span>Class<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span><\/span>‘Represents a condition like “object.Property = value”<\/span><\/p>\n

    <\/span>‘In this case object is of type T, and value is of type S<\/span><\/p>\n

    <\/span>‘<\/span><\/p>\n

    <\/span>‘Even though most of the logic for this is already in the base class, <\/span><\/p>\n

    <\/span>‘defining a second generic parameter means the user doesn’t have to<\/span><\/p>\n

    <\/span>‘pass in a System.Type – it can just be inferred.<\/span><\/p>\n

    <\/span>Public<\/span> <\/span>Class<\/span> Condition(<\/span>Of<\/span> T, S) : <\/span>Inherits<\/span> Condition(<\/span>Of<\/span> T)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Friend<\/span> <\/span>Sub<\/span> <\/span>New<\/span>(<\/span>ByVal<\/span> propName <\/span>As<\/span> <\/span>String<\/span>, <\/span>ByVal<\/span> condType <\/span>As<\/span> Compare, <\/span>ByVal<\/span> value <\/span>As<\/span> S)<\/span><\/p>\n

            <\/span>MyBase<\/span>.New(propName, condType, value, <\/span>GetType<\/span>(S))<\/span><\/p>\n

        <\/span>End<\/span> <\/span>Sub<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>End<\/span> <\/span>Class<\/span><\/p>\n

 <\/span><\/p>\n

#End<\/span> <\/span>Region<\/span><\/p>\n

 <\/span><\/p>\n

#Region<\/span> <\/span>“Extension Methods”<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Filters an IQueryable(Of T) according to the specified condition<\/span><\/p>\n

    <Extension()> _<\/span><\/p>\n

    <\/span>Public<\/span> <\/span>Function<\/span> Where(<\/span>Of<\/span> T)(<\/span>ByVal<\/span> source <\/span>As<\/span> IQueryable(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                <\/span>ByVal<\/span> condition <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> IQueryable(<\/span>Of<\/span> T)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Dim<\/span> callExpr = Expression.Call(<\/span>GetType<\/span>(Queryable), <\/span>“Where”<\/span>, _<\/span><\/p>\n

                                       <\/span>New<\/span> Type() {source.ElementType}, source.Expression, _<\/span><\/p>\n

                                       Expression.Quote(condition.LambdaExpr))<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Return<\/span> <\/span>CType<\/span>(source.Provider.CreateQuery(callExpr), IQueryable(<\/span>Of<\/span> T))<\/span><\/p>\n

    <\/span>End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Filters an IEnumerable(Of T) according to the specified condition<\/span><\/p>\n

    <Extension()> _<\/span><\/p>\n

    <\/span>Public<\/span> <\/span>Function<\/span> Where(<\/span>Of<\/span> T)(<\/span>ByVal<\/span> source <\/span>As<\/span> IEnumerable(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                <\/span>ByVal<\/span> condition <\/span>As<\/span> Condition(<\/span>Of<\/span> T)) <\/span>As<\/span> IEnumerable(<\/span>Of<\/span> T)<\/span><\/p>\n

        <\/span>Return<\/span> source.Where(condition.del)<\/span><\/p>\n

    <\/span>End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

    <\/span>‘Extension method that can be called off any type that implements IEnumerable(Of T), <\/span><\/p>\n

    <\/span>‘which constructs a Condition with T as the element type and S as the value’s type<\/span><\/p>\n

    <Extension(), EditorBrowsable(EditorBrowsableState.Always)> _<\/span><\/p>\n

    <\/span>Public<\/span> <\/span>Function<\/span> CreateCondition(<\/span>Of<\/span> T, S)(<\/span>ByVal<\/span> dataSource <\/span>As<\/span> IEnumerable(<\/span>Of<\/span> T), _<\/span><\/p>\n

                                             <\/span>ByVal<\/span> propName <\/span>As<\/span> <\/span>String<\/span>, _<\/span><\/p>\n

                                             <\/span>ByVal<\/span> condType <\/span>As<\/span> condition.Compare, _<\/span><\/p>\n

                                             <\/span>ByVal<\/span> value <\/span>As<\/span> S) <\/span>As<\/span> Condition(<\/span>Of<\/span> T, S)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Return<\/span> Condition.Create(dataSource, propName, condType, value)<\/span><\/p>\n

    <\/span>End<\/span> <\/span>Function<\/span><\/p>\n

 <\/span><\/p>\n

#End<\/span> <\/span>Region<\/span><\/p>\n

 <\/span><\/p>\n

End<\/span> <\/span>Module<\/span><\/span><\/p>\n

<\/span><\/span><\/p>\n

The complete sample (including the code for the ConditionBuilder control) is available in the attached .zip file below.  While we\u2019ve got the basics going pretty well, there\u2019s definitely a lot of things you can extend the sample to do, such as:<\/font><\/p>\n

1.<\/font>       <\/span><\/span><\/span>Add support for Grouping conditions into the ConditionBuilder \u2013 right now you\u2019d have to drop down to the API to construct something like (condition1 Or condition2) And condition3.<\/font><\/p>\n

2.<\/font>       <\/span><\/span><\/span>Add a .Select extension method to dynamically control which fields you bring back from the database.  <\/span>This will improve performance in situations where you don\u2019t want to return all fields, but you\u2019ll lose strong-typing of your results since the method will have to return IQueryable instead of IQueryable(Of T).<\/span><\/font><\/font><\/p>\n

3.<\/font>       <\/span><\/span><\/span>Add extension methods for OrderBy, Take, Skip, Distinct etc\u2026  Each of these methods can still return strongly-typed results since they don\u2019t change the element type; if you pass in T they return T<\/span>.<\/span><\/font><\/font><\/p>\n

4.<\/font>       <\/span><\/span><\/span>The Condition API supports nested property access (i.e. Where customer.Orders.Salesman.Name = \u201cBob\u201d), but it\u2019d take a bit more work to surface this in the ConditionBuilder control.<\/font><\/p>\n

 <\/font><\/span><\/p>\n

That\u2019s everything, now all you have to do is<\/span> download Beta2<\/font><\/a><\/span> and try it out!<\/font><\/font><\/p>\n

 <\/font><\/span><\/p>\n

Jonathan<\/span><\/font><\/font><\/p>\n

<\/span><\/p>\n

DynamicCondition.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[192,195],"tags":[77,83,94,117,166],"class_list":["post-5553","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-visual-basic","tag-iqueryable","tag-jonathan-aneja","tag-linqvb9","tag-orcas","tag-vb2008"],"acf":[],"blog_post_summary":"

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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5553","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=5553"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5553\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=5553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=5553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=5553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}