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:

var query = from person in people
            where person.City == "Arlington"
            select new
            {
                Id = person.Id,
                FirstName = person.FirstName
            };

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:

people.Where("city = @0", "Arlington")
      .Select("new (Id, FirstName, LastName, State)");
 
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:

string dynamicClass = string.Empty;
for (int i = 0; i < ColumnsToExport.Count; i++)
{
    string col = ColumnsToExport[i];
    if (i == 0)
    {
        dynamicClass = "new(" + col;
    }
    else
    {
        dynamicClass += "," + col;
    }
    if (i == ColumnsToExport.Count - 1)
    {
        dynamicClass += ")";
    }
}

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 FilesMicrosoft Visual Studio 9.0Samples1033CSharpSamplesLinqSamplesDynamicQuery
  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:

   1: var props = typeof(PersonData).GetProperties();
   2: var columns = from prop in props
   3:               where prop.GetCustomAttributes(typeof(ExportableAttribute), false).Length > 0
   4:               select new ColumnCheckBoxItem
   5:               {
   6:                   DisplayName = (prop.GetCustomAttributes(typeof(ExportableAttribute), false)[0] as ExportableAttribute).DisplayName,
   7:                   PropertyName = prop.Name
   8:               };
   9:  
  10: clbColumns.DisplayMember = "DisplayName";
  11: clbColumns.ValueMember = "PropertyName";
  12: foreach (var column in columns)
  13: {
  14:     clbColumns.Items.Add(column);
  15: }

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:

[Column(Storage = "_ZIP", DbType = "NVarChar(100)")]
[Exportable(DisplayName = "Zip Code")]
public string ZIP
{
    get
    {
        return this._ZIP;
    }
    set
    {
        if ((this._ZIP != value))
        {
            this._ZIP = value;
        }
    }
}

 

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:

public class ExportableAttribute : Attribute
{
    public string DisplayName;
}

 

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):

Database db = DatabaseFactory.CreateDatabase();
var cmd = context.GetCommand(query as IQueryable))
var myReader =  db.ExecuteReader(cmd);
Advertisements

0 thoughts on “Dynamically Build LINQ Queries Using Reflection

  1. I don’t get it. How is this preferred over just doing a DataContext.Translate()?Also, your dynamicClass string generation could be rewritten as a much simpler one-liner:string dynamicClass = “new(” + string.Join(“,”, columnsToExport) + “)”;I’m assuming you validate for a positive number of selected columns somewhere else.

    Like

  2. I don’t get it. How is this preferred over just doing a DataContext.Translate()?

    Also, your dynamicClass string generation could be rewritten as a much simpler one-liner:
    string dynamicClass = “new(” + string.Join(“,”, columnsToExport) + “)”;

    I’m assuming you validate for a positive number of selected columns somewhere else.

    Like

  3. @mark: Thanks for pointing out the Translate() method, I looked into it and that wouldn’t solve my problem. For starters using reflection works well for me because I can dynamically populate the list of columns in a checkboxlist and then dynamically create the LINQ query…Using reflection and custom attribute also allowed me to specify which columns can be exported and which ones can’t. So that alone, would prevent me from using regular sql strings and executing on a command.On the other hand, you are right, almost everything I did could have been done with a sql string and a command but where is the fun in that :)Thanks for the string.Join… I am embarrassed to say that I have never used it before and looking at your code snippet – I LOVE IT.

    Like

  4. @mark: Thanks for pointing out the Translate() method, I looked into it and that wouldn’t solve my problem. For starters using reflection works well for me because I can dynamically populate the list of columns in a checkboxlist and then dynamically create the LINQ query…

    Using reflection and custom attribute also allowed me to specify which columns can be exported and which ones can’t. So that alone, would prevent me from using regular sql strings and executing on a command.

    On the other hand, you are right, almost everything I did could have been done with a sql string and a command but where is the fun in that 🙂

    Thanks for the string.Join… I am embarrassed to say that I have never used it before and looking at your code snippet – I LOVE IT.

    Like

  5. Nice article. Quick question: if i want to pass the results of the query which is in data access layer to business then to UI, what would be the return type?

    Like

Leave a Reply

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