Dynamically Build LINQ Queries Using Reflection

I was recently working on a project where there was an option to export data from the database.  The export function simply exported 2 hard-coded columns that were returned by a stored procedure into a tab-delimited text file. 

The sproc looked something like select id, name from mytable (I am over simplifying of course).  I was tasked to customize the export process, so that the user can select the columns/fields to be exported.  There were several constraints though:

  1. Stored procedure should not change
  2. Work within the existing export framework

The problem was that the existing framework used an Export function defined in a base class that all Exporters inherited.  The Export function expected a DataReader and simply exported whatever columns were in the reader.

One solution was to dynamically generate the SQL string in code and then execute it.  Another better solution was to use Dynamic LINQ.  Wait, but there is no such thing as dynamic LINQ, you say.  You are half right.  There is no built-in dynamic LINQ query generation into the framework but SOME GENIOUS created a bunch of extension methods to facilitate such a miraculous feat.

What is Dynamic LINQ? 

I hear you ask.  Let’s start by looking at regular (or static) LINQ, which looks like this:

This is static because there is no way to change the select so that it dynamically selects different attributes from the person class based on criteria specified by the user.  Also, for example, if you were creating an advanced search page and wanted the user to specify custom filter criteria, you won’t be able to dynamically generate the where conditions.

Dynamic LINQ, lets you do all these things and more.  Let’s take a look at a sample dynamic LINQ query:

Note that the select and where are strings.  That means you can dynamically generate the strings based on user input.

So, I dynamically created the select using a collection of columns.  This is just a string collection that contains all the columns selected by the user to be exported.  The code looked like this:

All I have to do now is pass the dynamicClass string variable to the Select method.

Get Dynamic

To get dynamic LINQ in your project:

  1. Copy Dynamic.cs file from C:\Program Files\Microsoft Visual Studio 9.0\Samples\1033\CSharpSamples\LinqSamples\DynamicQuery
  2. If you don’t have that folder, click Help > Samples in Visual Studio and follow the instructions to install the samples
  3. All you have to do is import the System.Linq.Dynamic namespace wherever you want to use Dynamic LINQ.

Let’s Reflect

I forgot to mention a few minor details.  The data to be exported is coming from a view.  The view is defined as a LINQ to SQL entity.

Ok, now that that’s out of the way, I needed to create a checkbox list of all the columns to allow the user to select the ones to export.  I obviously knew that this is doable using reflection, but had to dig around for the right calls to make.  All I needed to do is basically loop through all the properties of the LINQ to SQL entity class (the data class) for the view and bind them to the checkbox list control.  Here is the code:

Let’s look at this code in more details.  In line 1, I get all the properties for my entity class PersonData.  Then I select only the properties that have the “Exportable” attribute.  This allowed me to filter out some properties from showing up in the checkbox list, such as timestamp and Guid columns.  For example the zip code is defined as a property called ZIP, but I wanted it to show up in the checkbox list as “Zip Code”, so I added a property to my ExportableAttribute class called DisplayName which allowed me to customize the diplay name.  So, the ZIP property looked like this:


In lines 4 to 8, I create an anonymous object that contains the display name and the actually property name which will respectively correspond to DisplayMember and ValueMember in the checkbox list (lines 10 and 11).

Note that in line 6, I retrieve all the attributes of the property that are of type ExportableAttribute.  This returns an array, so I use the first element to retrieve the DisplayName.  There is no need to test for null since the where condition will ensure that only properties with the Exportable attribute are included.

You are also probably wondering why I didn’t just set the checkbox list DataSource to the columns collection.  Well, I did an it didn’t bind correctly.  I am not sure why but the for loop worked and I didn’t want to waste too much time.  When I bound the list using the DataSource property, the checkboxes contained text like {DisplayMember = “Zip Code”, PropertyName=”ZIP”} instead of just containing Zip Code.  Does anyone know why?

Finally, the ExportableAttribute class looks like this:


The Export

Now that I have prompted the user to customize the export and I have dynamically built the LINQ query based of the user’s selection, all I have to do is export it.  As I mentioned above, the Export method in the base class needed a DataReader, so I had to convert my LINQ expression to a DataReader.  Here is how you get a DataReader out of a LINQ expression (note that this will work with both static and dynamic queries):