Builder Methods to create dynamic esql queries

In the current project, I am working on,  I make use of esql heavily. One of the reason is because I am still using EF v1 which has limited support for linq queries. The second reason is, if you have dynamic queries which are build on what user selects from the search criteria, esql makes the job much easier. This is why Entity Framework supports the concepts of using builder methods.

Builder methods allow you write sections of code using esql like a dynamic where clause, orderby clause or returning different columns in the select statement based on what user like to see on the UI. The comprehensive list of Builder methods can be obtained from the msdn link below.

http://msdn.microsoft.com/en-us/library/bb896238.aspx

Builder methods are exposed on ObjectQuery of T not on IQueryable of T.  When new developers start working with builder methods they have hard time figuring out why the overload for the builder method is not available on a certain IQueryable. Example below shows common confusion.

var customers = db.Customers.Where(c => c.CompanyName == “CTD”);

customers.Where(“it.City == @city”,.”London”);// no builder method available.

In the above code, when you try to use Where Builder method, it wont be available because customers is an IQueryable. You have to cast IQueryable to Objectquery<Customer> before builder method would be exposed.

There might be cases in your application where you are mixing and matching linq and esql both. For the most part, you wont run into any issues but there are some cases where casting from IQueryable to ObjectQuery would lead to runtime exception. As a rule, try not to be in a situation where you have to cast IQueryable to ObjectQuery because after a certain point some esql operators will throw you runtime exception. You can always go from ObjectQuery to IQueryable but as you start applying linq expressions on that IQueryable, going back to ObjectQuery wont be possible.

In the walk through below, I will make use of several builder methods to dynamically build my query based on the user input.

Suppose we have a model consisting of Customer, Order and OrderDetails as shown below.

You have a user interface screen that lets you enter the city for the Customer and also the total amount for all the orders user has ever placed.  You want to return only the name of those customers. To accomplish the above requirement, I will make use of Where builder method along with SelectValue which would allow me to project various columns that I desire. Example below shows the complete code.

Notice the above dynamic where builder method makes use of parameterized query by passing the searchcity and sum as parameters to the query. The selectvalue in this case returns a single value. If you have to return more then 1 column, you can use the Select Builder method.

Hopefully this small example illustrate how easy it is to use builder methods to create dynamic queries.