Export (0) Print
Expand All

How to: Use Expression Trees to Build Dynamic Queries (C# and Visual Basic)

In LINQ, expression trees are used to represent structured queries that target sources of data that implement IQueryable<T>. For example, the LINQ to SQL provider implements the IQueryable<T> interface for querying relational data stores. The C# and Visual Basic compilers compile queries that target such data sources into code that builds an expression tree at runtime. The query provider can then traverse the expression tree data structure and translate it into a query language appropriate for the data source.

Expression trees are also used in LINQ to represent lambda expressions that are assigned to variables of type Expression<TDelegate>.

This topic describes how to use expression trees to create dynamic LINQ queries. Dynamic queries are useful when the specifics of a query are not known at compile time. For example, an application might provide a user interface that enables the end user to specify one or more predicates to filter the data. In order to use LINQ for querying, this kind of application must use expression trees to create the LINQ query at runtime.

The following example shows you how to use expression trees to construct a query against an IQueryable data source and then execute it. The code builds an expression tree to represent the following query:

C# Query

companies.Where(company => (company.ToLower() == "coho winery" || company.Length > 16)).OrderBy(company => company)

Visual Basic Query

companies.Where(Function(company) company.ToLower() = "coho winery" OrElse company.Length > 16).OrderBy(Function(company) company)

The factory methods in the System.Linq.Expressions namespace are used to create expression trees that represent the expressions that make up the overall query. The expressions that represent calls to the standard query operator methods refer to the Queryable implementations of these methods. The final expression tree is passed to the CreateQuery<TElement>(Expression) implementation of the provider of the IQueryable data source to create an executable query of type IQueryable. The results are obtained by enumerating that query variable.

            // Add a using directive for System.Linq.Expressions. 

            string[] companies = { "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" };

            // The IQueryable data to query.
            IQueryable<String> queryableData = companies.AsQueryable<string>();

            // Compose the expression tree that represents the parameter to the predicate.
            ParameterExpression pe = Expression.Parameter(typeof(string), "company");

            // ***** Where(company => (company.ToLower() == "coho winery" || company.Length > 16)) *****
            // Create an expression tree that represents the expression 'company.ToLower() == "coho winery"'.
            Expression left = Expression.Call(pe, typeof(string).GetMethod("ToLower", System.Type.EmptyTypes));
            Expression right = Expression.Constant("coho winery");
            Expression e1 = Expression.Equal(left, right);

            // Create an expression tree that represents the expression 'company.Length > 16'.
            left = Expression.Property(pe, typeof(string).GetProperty("Length"));
            right = Expression.Constant(16, typeof(int));
            Expression e2 = Expression.GreaterThan(left, right);

            // Combine the expression trees to create an expression tree that represents the 
            // expression '(company.ToLower() == "coho winery" || company.Length > 16)'.
            Expression predicateBody = Expression.OrElse(e1, e2);

            // Create an expression tree that represents the expression 
            // 'queryableData.Where(company => (company.ToLower() == "coho winery" || company.Length > 16))'
            MethodCallExpression whereCallExpression = Expression.Call(
                typeof(Queryable),
                "Where",
                new Type[] { queryableData.ElementType },
                queryableData.Expression,
                Expression.Lambda<Func<string, bool>>(predicateBody, new ParameterExpression[] { pe }));
            // ***** End Where ***** 

            // ***** OrderBy(company => company) ***** 
            // Create an expression tree that represents the expression 
            // 'whereCallExpression.OrderBy(company => company)'
            MethodCallExpression orderByCallExpression = Expression.Call(
                typeof(Queryable),
                "OrderBy",
                new Type[] { queryableData.ElementType, queryableData.ElementType },
                whereCallExpression,
                Expression.Lambda<Func<string, string>>(pe, new ParameterExpression[] { pe }));
            // ***** End OrderBy ***** 

            // Create an executable query from the expression tree.
            IQueryable<string> results = queryableData.Provider.CreateQuery<string>(orderByCallExpression);

            // Enumerate the results. 
            foreach (string company in results)
                Console.WriteLine(company);

            /*  This code produces the following output:

                Blue Yonder Airlines
                City Power & Light
                Coho Winery
                Consolidated Messenger
                Graphic Design Institute
                Humongous Insurance
                Lucerne Publishing
                Northwind Traders
                The Phone Company
                Wide World Importers
            */

This code uses a fixed number of expressions in the predicate that is passed to the Queryable.Where method. However, you can write an application that combines a variable number of predicate expressions that depends on the user input. You can also vary the standard query operators that are called in the query, depending on the input from the user.

  • Create a new Console Application project in Visual Studio.

  • Add a reference to System.Core.dll if it is not already referenced.

  • Include the System.Linq.Expressions namespace.

  • Copy the code from the example and paste it into the Main method (C#) or the Main Sub procedure (Visual Basic).

Show:
© 2014 Microsoft