Creating dynamic/configurable parameterized queries in Entity Framework

EDIT: A better, refactored version of this can be found here.

Entity Framework is an amazing tool. It allows you to quickly and easily get your basic queries and updates running with little hassle. In addition, you have business objects to interact with, which makes your middle tier (or your controller) that much easier to work with. However, when you need to start setting up more dynamic queries from user input, things start to get tricky. Here’s a solution I created to help with that.

Introduction

Within Entity Framework, you have two main forms of queries, LINQ and EntityQueries. While LINQ offers an easier “visual” approach to a query, we run into a bit of difficulty when we want to optionally append a lot of parameters to our WHERE clause in the query. I have done this in the past by using a syntax similar to:

WHERE (@Cus_Name IS NULL OR Cus_Name LIKE '%' + @Cus_Name + '%')
  AND (@Cus_City IS NULL OR Cus_City LIKE '%' + @Cus_City + '%')

but in this case we’re working with a large number of options and would like our executing SQL query to just look for the variables we need. In that regard, we can use our EntityQuery and setup something similar to…

var sites = context.sites;
var query = sites.Where("Cus_Id = @CusId");
var queryParameter = new ObjectParameter("CusId", Type.GetType("System.Int32")) { Value = 5 };

query.Parameters.Add(queryParameter);
var count = query.Count();

…and work from there. To add to our simple queries, we need to remember that we’re setting up a way for users to be able to query our EF context, so we also need to account for  wildcard searches (Name LIKE ‘%Jo%’), multiple item searches (Id = 5 OR Id = 6), range searches (StartDate >= ‘1/1/2010’ AND StartDate <= ‘2/1/2010’), and also for NULL values in our searches (TypeId = NULL).

Ultimately our task is not complex (iterating through a list of parameters and setting up our query to handle them), but being able to do so in a relatively dynamic manner (minimal configuration) would allow us to reuse our code against any table/entity in our context/database. For that we need to setup a little bit of structure.

Search Parameter Object and Dictionary

To accomplish this, the first step is to create a simple object that holds all of our  parameter/criteria building information.

You’ll notice we have quite a few constructors in here. That’s to make it easier to create our criteria based on our common scenarios. With this object, we then create a Dictionary collection of all the fields we want to be able to search on in our database. While it would be nice to auto-infer our search criteria, we have a few tricky situations (especially with date ranges) that require us to configure things accordingly:

Building the Parameterized Query

Armed with this structure, we can now build our parameterized query with the following method:

Notice a few of the perks/nuances here:

  • If there are no parameters in our collection, we simply return the existing query. This allows us to take an existing query from anywhere and not worry about what it already has in it.
  • Since our “range” type queries involve two variables,  we automatically look for paired parameters with a From/To name in them, parse them and generate a Range parameter for them.
  • We also check for the empty or “null” value and use DBNull instead. Otherwise the parameterized query would throw an exception when it manually tried to convert the string “null” to NULL and it would treat empty strings differently.
  • Parameters that are identified  as allowing multiples are assumed to be pipe delimited and will be split accordingly.
  • The method is using .Net generics for it’s resulting ObjectQuery output as well as input object. This allows us to easily pass any entity from our EF Context into the method, since the parameters and search criteria are in a fixed type no matter what.

Pull Your Parameters and Go!

Now that we have all the pieces together, how do we actually  build and get our query results? The first step is to get your parameters. Notice the method is expecting a NameValueCollection object for parameters. The nice thing is that by default, web postbacks will automatically take any form parameters and put them into this object. This makes it perfect for a web interface, but you can easily modify your code to generate your own NameValueCollection to work with. Once you have this, it is as simple as executing the following code:

var ce = new YourEFContextObject();
var searchCriteria = GetSearchCriteria();
ObjectQuery<site> siteQuery = ce.sites;
siteQuery = BuildObjectQuery(siteQuery, parameters, searchCriteria);

var siteCount = siteQuery.Count();

That’s all there is to it! If you need to work with a different entity object, you simply generate a new ObjectQuery with its entity type, make sure you have the appropriate search criteria defined (it doesn’t have to be a method, I find it easier for me to structure it as such), grab your parameters, and go!

Room For Improvement

As it is with most things, there is always room for improvement here. For example, we’re creating exclusive (AND) queries by default, and it would be helpful to modify the method to produce inclusive (OR) queries as well. In addition, we make the assumption of a pipe delimiter for multiple items, and we could also make that configurable.

Ideally it would be nice to take a simple string name of the entity we wanted to work with and have the method generate the ObjectQuery<T> object to work with. I did a lot of digging and couldn’t quite make that happen. There’s some tricky work with reflection and metadata that the entity context contains, but I couldn’t quite find it. Any advice is greatly appreciated on how to make this happen.

All in all I’m quite happy to have this structure in place. Currently I’m finishing off implementing the dictionary search criteria for one entity item with about 50+ parameters that can be optionally searched on. I then have to repeat the process with another  7 or 8 tables. A couple of those tables will have more columns than the current one. With this solution, I no longer have to worry about special cases or duplicate processing. I simply generate my criteria dictionary, grab my parameters, and go!

I hope this is helpful to you as well!

7 thoughts on “Creating dynamic/configurable parameterized queries in Entity Framework

    1. I have not. I might have to give that a try. I’ve been discussing things in the Google+ .Net group and have some optimizations I’m working on as well.

  1. My context where method does not even receive a astring as a parameter. What Entity framework version are you using? I thought it can work out in MVC5 but I guess ti is intended for some specific scenario you don´t clearly state.

    1. Hmm. When I originally posted this it was with an MVC 4.0 Framework project we had going. I’m working on an MVC5 framework app and we’re leveraging this same tactic again, so I’ll see if anything is different. Do you care to share your basic code? You can even use the contact form (at the top of the page) if you wish to contact me privately about it.

  2. Hi, I am sure this is a useful tool / idea, but after reading the post, I am still confused about the actual problem this solves. Is the goal of this post to address dynamically querying data from a database table(s) using EF where you might not know anything programmatically about the table in question? I am interested in a solution to this problem. Thanks!

    1. Thank you for your question Joseph! The problem I ran into when I built this solution was that I had a typical “list view with search filter” page where there were a LOT (10+) parameters in which the user could select from in order to query against. The user had the option to search a range (such as date) for some fields, and allow wildcard type searches for other fields.

      In the past I’ve built a simple query based on the parameters that came in, using simple if/else type logic, but this grew far more complex and I needed a more structured solution so help the code function a bit cleaner, and be reused later on for other projects or to make it more flexible if new fields were added.

      Does that help explain it further?

What are your 10 bits on the matter? I want to know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s