We’ve considered a number of ways to pass sets of data to an SQL query from a client application. Often those sets are not complex. They may simply be a set of filter options that may be expressed as a series of OR operations. For example,
Where LastName = ‘Taylor’ OR LastName = ‘Smith’
This is easy SQL to write. But your application may have zero, one or many different last names to request from the database. When you want to build this SQL query you have a few options. You can roll your own query builder. If you are using Dot Net, you can take advantage of an Expression tree, sending a list of expressions.
Using an Expression tree you can send multiple expressions to be evaluated. They may be based on a list to compare to a single database column, or multiple operations to be performed against multiple columns.
You could define an expression testing last name and age as a single expression. You could define an expression where the last name is in a list, or like the values in a list. There are any number of methods to build these expressions, and to submit them to SQL Server through Linq 2 Sql, or through Entity Framework. The neat thing is that Expressions do not have to be compiled when you compile your application. They are compiled as they are used. In fact, where possible, Entity Framework will take the expression definition, translate it to SQL, and pass it through as part of the query as if you had written the where clause yourself.
I found a great article on how to build these expressions using the PredicateBuilder. If you’re interested in learning a great way to write a where clause in Dot Net that performs well, take a look at this snippet from the O’Reilly Book, C# 6.0 In a Nutshell.
Cheers,
Ben