Edit

Share via

Querying based on runtime state (Visual Basic)

  • Article

Consider code that defines an IQueryable or an IQueryable(Of T) against a data source:

Dim companyNames As String() = {
    "Consolidated Messenger", "Alpine Ski House", "Southridge Video",
    "City Power & Light", "Coho Winery", "Wide World Importers",
    "Graphic Design Institute", "Adventure Works", "Humongous Insurance",
    "Woodgrove Bank", "Margie's Travel", "Northwind Traders",
    "Blue Yonder Airlines", "Trey Research", "The Phone Company",
    "Wingtip Toys", "Lucerne Publishing", "Fourth Coffee"
}

' We're using an in-memory array as the data source, but the IQueryable could have come
' from anywhere -- an ORM backed by a database, a web request, Or any other LINQ provider.
Dim companyNamesSource As IQueryable(Of String) = companyNames.AsQueryable
Dim fixedQry = companyNamesSource.OrderBy(Function(x) x)

Every time you run this code, the same exact query will be executed. This is frequently not very useful, as you may want your code to execute different queries depending on conditions at run time. This article describes how you can execute a different query based on runtime state.

IQueryable / IQueryable(Of T) and expression trees

Fundamentally, an IQueryable has two components:

  • Expression—a language- and datasource-agnostic representation of the current query's components, in the form of an expression tree.
  • Provider—an instance of a LINQ provider, which knows how to materialize the current query into a value or set of values.

In the context of dynamic querying, the provider will usually remain the same; the expression tree of the query will differ from query to query.

Expression trees are immutable; if you want a different expression tree—and thus a different query—you'll need to translate the existing expression tree to a new one, and thus to a new IQueryable.

The following sections describe specific techniques for querying differently in response to runtime state:

  • Use runtime state from within the expression tree
  • Call additional LINQ methods
  • Vary the expression tree passed into the LINQ methods
  • Construct an Expression(Of TDelegate) expression tree using the factory methods at Expression
  • Add method call nodes to an IQueryable's expression tree
  • Construct strings, and use the Dynamic LINQ library

Use runtime state from within the expression tree

Assuming the LINQ provider supports it, the simplest way to query dynamically is to reference the runtime state directly in the query via a closed-over variable, such as length in the following code example:

Dim length = 1
Dim qry = companyNamesSource.
    Select(Function(x) x.Substring(0, length)).
    Distinct

Console.WriteLine(String.Join(", ", qry))
' prints: C, A, S, W, G, H, M, N, B, T, L, F

length = 2
Console.WriteLine(String.Join(", ", qry))
' prints: Co, Al, So, Ci, Wi, Gr, Ad, Hu, Wo, Ma, No, Bl, Tr, Th, Lu, Fo

The internal expression tree—and thus the query—haven't been modified; the query returns different values only because the value of length has been changed.

Call additional LINQ methods

Generally, the built-in LINQ methods at Queryable perform two steps:

You can replace the original query with the result of an IQueryable(Of T)-returning method, to get a new query. You can do this conditionally based on runtime state, as in the following example:

' Dim sortByLength As Boolean  = ...

Dim qry = companyNamesSource
If sortByLength Then qry = qry.OrderBy(Function(x) x.Length)

Vary the expression tree passed into the LINQ methods

You can pass in different expressions to the LINQ methods, depending on runtime state:

' Dim startsWith As String = ...
' Dim endsWith As String = ...

Dim expr As Expression(Of Func(Of String, Boolean))
If String.IsNullOrEmpty(startsWith) AndAlso String.IsNullOrEmpty(endsWith) Then
    expr = Function(x) True
ElseIf String.IsNullOrEmpty(startsWith) Then
    expr = Function(x) x.EndsWith(endsWith)
ElseIf String.IsNullOrEmpty(endsWith) Then
    expr = Function(x) x.StartsWith(startsWith)
Else
    expr = Function(x) x.StartsWith(startsWith) AndAlso x.EndsWith(endsWith)
End If
Dim qry = companyNamesSource.Where(expr)

You might also want to compose the various subexpressions using a third-party library such as LinqKit's PredicateBuilder:

' This is functionally equivalent to the previous example.

' Imports LinqKit
' Dim startsWith As String = ...
' Dim endsWith As String = ...

Dim expr As Expression(Of Func(Of String, Boolean)) = PredicateBuilder.[New](Of String)(False)
Dim original = expr
If Not String.IsNullOrEmpty(startsWith) Then expr = expr.Or(Function(x) x.StartsWith(startsWith))
If Not String.IsNullOrEmpty(endsWith) Then expr = expr.Or(Function(x) x.EndsWith(endsWith))
If expr Is original Then expr = Function(x) True

Dim qry = companyNamesSource.Where(expr)

Construct expression trees and queries using factory methods

In all the examples up to this point, we've known the element type at compile time—String—and thus the type of the query—IQueryable(Of String). You may need to add components to a query of any element type, or to add different components depending on the element type. You can create expression trees from the ground up, using the factory methods at System.Linq.Expressions.Expression, and thus tailor the expression at run time to a specific element type.

Constructing an Expression(Of TDelegate)

When you construct an expression to pass into one of the LINQ methods, you're actually constructing an instance of Expression(Of TDelegate), where TDelegate is some delegate type such as Func(Of String, Boolean), Action, or a custom delegate type.

Expression(Of TDelegate) inherits from LambdaExpression, which represents a complete lambda expression like the following:

Dim expr As Expression(Of Func(Of String, Boolean)) = Function(x As String) x.StartsWith("a")

A LambdaExpression has two components:

  • A parameter list—(x As String)—represented by the Parameters property.
  • A body—x.StartsWith("a")—represented by the Body property.

The basic steps in constructing an Expression(Of TDelegate) are as follows:

  • Define ParameterExpression objects for each of the parameters (if any) in the lambda expression, using the Parameter factory method.

    Dim x As ParameterExpression = Parameter(GetType(String), "x")

  • Construct the body of your LambdaExpression, using the ParameterExpression(s) you've defined, and the factory methods at Expression. For instance, an expression representing x.StartsWith("a") could be constructed like this:

    Dim body As Expression = [Call](
    x,
    GetType(String).GetMethod("StartsWith", {GetType(String)}),
    Constant("a")
)

  • Wrap the parameters and body in a compile-time-typed Expression(Of TDelegate), using the appropriate Lambda factory method overload:

    Dim expr As Expression(Of Func(Of String, Boolean)) =
    Lambda(Of Func(Of String, Boolean))(body, x)

The following sections describe a scenario in which you might want to construct an Expression(Of TDelegate) to pass into a LINQ method, and provide a complete example of how to do so using the factory methods.

Scenario

Let's say you have multiple entity types:

Public Class Person
    Property LastName As String
    Property FirstName As String
    Property DateOfBirth As Date
End Class

Public Class Car
    Property Model As String
    Property Year As Integer
End Class

For any of these entity types, you want to filter and return only those entities that have a given text inside one of their string fields. For Person, you'd want to search the FirstName and LastName properties:

' Dim term = ...
Dim personsQry = (New List(Of Person)).AsQueryable.
    Where(Function(x) x.FirstName.Contains(term) OrElse x.LastName.Contains(term))

But for Car, you'd want to search only the Model property:

' Dim term = ...
Dim carsQry = (New List(Of Car)).AsQueryable.
    Where(Function(x) x.Model.Contains(term))

While you could write one custom function for IQueryable(Of Person) and another for IQueryable(Of Car), the following function adds this filtering to any existing query, irrespective of the specific element type.

Example

' Imports System.Linq.Expressions.Expression
Function TextFilter(Of T)(source As IQueryable(Of T), term As String) As IQueryable(Of T)
    If String.IsNullOrEmpty(term) Then Return source

    ' T is a compile-time placeholder for the element type of the query
    Dim elementType = GetType(T)

    ' Get all the string properties on this specific type
    Dim stringProperties As PropertyInfo() =
        elementType.GetProperties.
            Where(Function(x) x.PropertyType = GetType(String)).
            ToArray
    If stringProperties.Length = 0 Then Return source

    ' Get the right overload of String.Contains
    Dim containsMethod As MethodInfo =
        GetType(String).GetMethod("Contains", {GetType(String)})

    ' Create the parameter for the expression tree --
    ' the 'x' in 'Function(x) x.PropertyName.Contains("term")'
    ' The type of the parameter is the query's element type
    Dim prm As ParameterExpression =
        Parameter(elementType)

    ' Generate an expression tree node corresponding to each property
    Dim expressions As IEnumerable(Of Expression) =
        stringProperties.Select(Of Expression)(Function(prp)
                                                   ' For each property, we want an expression node like this:
                                                   ' x.PropertyName.Contains("term")
                                                   Return [Call](      ' .Contains(...)
                                                       [Property](     ' .PropertyName
                                                           prm,        ' x
                                                           prp
                                                       ),
                                                       containsMethod,
                                                       Constant(term)  ' "term"
                                                   )
                                               End Function)

    ' Combine the individual nodes into a single expression tree node using OrElse
    Dim body As Expression =
        expressions.Aggregate(Function(prev, current) [OrElse](prev, current))

    ' Wrap the expression body in a compile-time-typed lambda expression
    Dim lmbd As Expression(Of Func(Of T, Boolean)) =
        Lambda(Of Func(Of T, Boolean))(body, prm)

    ' Because the lambda is compile-time-typed, we can use it with the Where method
    Return source.Where(lmbd)
End Function

Because the TextFilter function takes and returns an IQueryable(Of T) (and not just an IQueryable), you can add further compile-time-typed query elements after the text filter.

Dim qry = TextFilter(
    (New List(Of Person)).AsQueryable,
    "abcd"
).Where(Function(x) x.DateOfBirth < #1/1/2001#)

Dim qry1 = TextFilter(
    (New List(Of Car)).AsQueryable,
    "abcd"
).Where(Function(x) x.Year = 2010)

Add method call nodes to the IQueryable's expression tree

If you have an IQueryable instead of an IQueryable(Of T), you can't directly call the generic LINQ methods. One alternative is to build the inner expression tree as above, and use reflection to invoke the appropriate LINQ method while passing in the expression tree.

You could also duplicate the LINQ method's functionality, by wrapping the entire tree in a MethodCallExpression that represents a call to the LINQ method:

Function TextFilter_Untyped(source As IQueryable, term As String) As IQueryable
    If String.IsNullOrEmpty(term) Then Return source
    Dim elementType = source.ElementType

    ' The logic for building the ParameterExpression And the LambdaExpression's body is the same as in
    ' the previous example, but has been refactored into the ConstructBody function.
    Dim x As (Expression, ParameterExpression) = ConstructBody(elementType, term)
    Dim body As Expression = x.Item1
    Dim prm As ParameterExpression = x.Item2
    If body Is Nothing Then Return source

    Dim filteredTree As Expression = [Call](
        GetType(Queryable),
        "Where",
        {elementType},
        source.Expression,
        Lambda(body, prm)
    )

    Return source.Provider.CreateQuery(filteredTree)
End Function

In this case you don't have a compile-time T generic placeholder, so you'll use the Lambda overload that doesn't require compile-time type information, and which produces a LambdaExpression instead of an Expression(Of TDelegate).

The Dynamic LINQ library

Constructing expression trees using factory methods is relatively complex; it is easier to compose strings. The Dynamic LINQ library exposes a set of extension methods on IQueryable corresponding to the standard LINQ methods at Queryable, and which accept strings in a special syntax instead of expression trees. The library generates the appropriate expression tree from the string, and can return the resultant translated IQueryable.

For instance, the previous example (including the expression tree construction) could be rewritten as follows:

' Imports System.Linq.Dynamic.Core

Function TextFilter_Strings(source As IQueryable, term As String) As IQueryable
    If String.IsNullOrEmpty(term) Then Return source

    Dim elementType = source.ElementType
    Dim stringProperties = elementType.GetProperties.
            Where(Function(x) x.PropertyType = GetType(String)).
            ToArray
    If stringProperties.Length = 0 Then Return source

    Dim filterExpr = String.Join(
        " || ",
        stringProperties.Select(Function(prp) $"{prp.Name}.Contains(@0)")
    )

    Return source.Where(filterExpr, term)
End Function

See also