Dynamically Build LINQ Queries Using Reflection

May 12, 2008

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)”);

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

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

Share This

Posted in LINQ, Programming 3 Comments »
Tags: dynamic linq, LINQ, reflection, SQL

Yonkly: Updated 05/09/2008

May 9, 2008

We just released a new updated version younkly that is mostly bug fixes.  We couldn’t get full twitter integration in place for this release, but Justine is working hard on it.

Fixed bugs

1. Updated code to use the latest MVC code refresh
2. Fixed bug with Gravatar image if email is in MiXeD case.
3. Added the message being replied to in the notification email
4. Fixed display of messages with single apostrophes
5. Messages with apostrophes post correctly to twitter
6. Reply box is cleared after the message is sent.

Upcoming Upgrades

1. Deeper Twitter integration
2. OpendID integration

Share This

Posted in yonkly 3 Comments »
Tags: updates, yonkly

Creative, Innovative and Completely Useless

May 9, 2008

I don’t remember owning many pop-up books as a child, but they are so cool and the best part is that they don’t require much (if any) reading.  Here is one that is just plain cool.  I wish I was that creative to design something like that.  Yes, it is useless but simply put, it is a piece of art.

The book is ABC3D - even the name is clever - and you can pre-order it at amazon.com over here. 

I ordered mine after I saw this video.

Share This

Posted in Random No Comments »
Tags: art, Books, cool, innovative, Interesting

Save a Life before Lunch

May 8, 2008

Remember that XBOX/PS3 game you bought last week for 60 bucks?  Or the $100 you dropped at a bar tap a few weeks ago?  Well how about you donate some money to the unfolding catastrophe in Myanmar?

Here are ways to help:

1. Blog about it and spread the word - if each one of you blogs about it, the network effect could be huge.
2. Donate some money to a reputable organization, such as:
1. unicef
2. American Red Cross
3. World Food Programme
4. AmeriCares
5. Relief International
3. Lear more
1. CNN Coverage
2. BBC Coverage

Over 100,000 people may die, that’s a HUNDRED THOUSANDS - link.

Remember “The road to hell is paved with good intentions”.

Myanmar Cyclone Videos

BBC Video - http://news.bbc.co.uk/1/hi/world/7387877.stm

Myanmar Cyclone Pictures

Share This

Posted in News, Random 1 Comment »
Tags: charity, cyclone, myanmar, natural disaster

Ruby on Rails Crumbs - Part 1

May 2, 2008

If you have been following along, I blogged previously about my experience trying Ruby on Rails as a .net developer (here and here).  I quickly realized how different things are and that I desperately need some help, so I got myself the beta PDF version of Agile Web Development with Rails and Rails Recipes.  Both great books but not Rails Recipes doesn’t cover rails latest version - 2.0. 

As I read through these books, I realized there are lots of things I need to keep track of, that is lots of crumbs.  This series of blog posts are a way for me to keep track of these crumbs as well as share them with others.  I don’t plan to explain everything in detail (because I can’t).  This is more of a quick reference to remind me of features and things that I read about in the books because there is no FREAKING INTELLISENSE in rails.  You will realize how valuable Intellisense is when you don’t have it.

These crumbs are not in any specific order.  For the most part, they are in the order I discovered them.

content_tag

Use it to surround a block of text with a tag.  The code below creates <b>make me bold</b>

content_tag (:b, “make me bold”)

&block

I can’t find a good explanation of this but when you call a method from your views like this

<% my_foo() do %>
  [content]
<% end %>

And the method is defined like this

def my_foo(&block)
end

Then the &block parameter will contain all the text that is wrapped by this method in the view.  In the above example &block would contain [content]

blind_down

Use to generate a blind down effect in JavaScript to make hiding an element smoother than just setting display to none.  In you rjs template, write:

page[:element_id_to_blind].visual_effect :blind_down

request.xhr?

This returns true if the request is xhr i.e. an AJAX request.  I like the trailing question mark.  In .net, it would be named IsXhr.

has_many

Use in your model to create a relationship.  For example, an article model class might look like this

class Article < ActiveRecord::Base
has_many :categories
end

NOTE: your category model class name is Category but the has_many uses the actual table name which categories.  This is so weird for me - no compiler errors, no warnings, nothing.  I know, I know, convention over configuration.

belongs_to

Same idea as has_many but in reverse, so the category model class would look like this

class Category < ActiveRecord::Base
belongs_to :articles
end

button_to

A helper method that generates an HTML form containing a button that submits to a URL when clicked.  For example:

button_to “Edit”, :action => ‘edit’, :id => 3

Creates a form with an Edit button that submits to the edit action and pass 3 as an id parameter.  If we were editing an article, the generated html looks like this:

<form class=”button-to” action=”/articles/edit/3″ method=”post”>
    <div><input type=”submit” value=”Edit” /></div>
</form>

More crumbs to come - stay tuned.  Also feel free to add your own crumbs to the comments, it might help someone.

Share This

Posted in Programming, Web Dev 2 Comments »
Tags: rails, ror, ruby, ruby on rails

I have Live Mesh Invitations

April 30, 2008

I just got into the Live Mesh beta program and have 5 invitations; so why not share the love. Please leave a comment below and I will invite you.

I haven’t played with it yet because it wants me to turn User Access Control (UAC) before I can install it - which SUCKS. I don’t know about you but I don’t want a freaking popup confirming every action I need to make.

Anyway, leave a comment and get an invitation.

[update] I am out of invitations, you can try sharemesh.com

Share This

Posted in Random 6 Comments »

Ruby on Rails for a .NET Developer - Part 2

April 30, 2008

Ok, so I thought the whole scaffolding thing was so easy and I would be done in a few minutes. I was so wrong. Nothing seemed to work right. I would call ruby script/generate scaffold Tag and it will generate all the files but the views were empty. Hmm… This is exactly what has been done in all the demos and tutorials. Apparently, things have changed in Rails 2.0 and I had to call this instead script/generate scaffold Tag tag:string.

One thing I don’t like so far is the lack of good documentation for Rails 2.0 and there aren’t any books out there. I went ahead and bought me of Agile Web Development with Rails which has been updated for 2.0 but you can only get as beta from The Pragmatic Bookshelf. I bought the PDF version and have been reading it for the past few days.

It occurred to me that before I can get too deep into Rails, I need to learn a little about Ruby. After all the syntax is very different from C# or vb.net. Everyone claims that it is an easier language to understand but I think that is very subjective. Spanish might be easier than Chinese but if you are Chinese you might not think so.

With that said, in this post I am going to list a few things I have learned over the past few days about Ruby Syntax and how it compares to C# syntax.

Comments

C#

//this is a one line comment

/* This is a multi-line
comment
*/

Ruby

# this is a one line comment

=begin
this is a mult-line
comment
=end

Winner: C#

Class Definition

C#

class Car : Vehicle
{
public Car()
{
}
}

Ruby

class Car < Vehicle
def initialize
end
end

Winner: tie but if you hate curly brackets then Ruby wins

Method Definition

C#

public int GetYear()
{
}

public static int GetYear(string carName)
{
}

Ruby

def GetYear
end

def self.GetYear(carName)
end

Note: You can use the class name instead of self to make the method static. Also, in Ruby they are called class methods and not static methods. Come to think of it, that makes sense, since the method is defined for the class and not the instance or the object.

Winner: Ruby

Variables

Local variables

C#

int aNumber = 4; //declared inside a method

Ruby

aNumber=4 # no declaration needed just use it inside a method

Instance variables (fields)

C#

int aNumber; //declared inside a class not a method

Ruby

@aNumber #no declaration needed just use it anywhere

Static variables (fields)

C#

static int aNumber; //declared inside a class not a method

Ruby

@@aNumber #no declaration needed just it anywhere

Control Structure

if

C#

if (aCondition)
{
}
else
{
}

Ruby

if aCondition then
else
end

unless

C#

There is no unless in C# but the same logic can be written like this:

if (!aCondition)
{
}
else
{
}

Ruby

unless isBlue

else

end

case

C#

switch (year)
{
case 1960:
case 1961:
case 1962:
…
case 1969:
break;
case 1970:
…
case 1979:
break;
default:
}

The code above has been truncated for brevity because you cannot do ranges in C# as easy you can in Ruby

Ruby

case year
when 1960 .. 1969
when 1970 .. 1979
else
end

The above Ruby code is complete and not truncated. The express 1960 .. 1969 specifies a range.

while

C#

while (aCondition)
{
}

Ruby

while aCondition
end

until

C#

There is no until in C# but the same logic can be expressed like this:

while (!aCondition)
{
}

Ruby

until aCondition
end

This is not a comparison of language features, it is just a comparison of the syntactical differences of *some* of both languages. This is *not* an exhaustive list and is not meant to be - a simple Google search will get you thousands of pages of syntax goodies for either language.

So far, I can’t say I love Ruby’s syntax but if I were new to programming, I would say it looks more readable and easier to understand. Being a C#/VB developer for so long, I am a little confused and uncomfortable with the Ruby syntax. But, the facts that I have used VB for the past 10 or so years and that I hate semi-colons and curly brackets in C#, it is not that bad.

Share This

Posted in C#, Programming, Software Development 4 Comments »
Tags: C#, ror, ruby, ruby on rails, syntax

Ruby on Rails for a .NET Developer - Part 1

April 25, 2008

Looking at my blog or resume, you will immediately know that I am a .net developer and my entire programming career has been Microsoft-centric.  I have been wanting to play with Ruby on Rails due to its inescapable popularity and I was actually surprised to learn that the language itself (Ruby) was developed in the early 90’s…  So it’s not really new.  Ruby on Rails is a framework that sits on top of it…

NOTE: forgive me if any of this is wrong, my RoR experience as of right now is 35 minutes…

Anyway, this post is not about the history of Ruby or a comparative analysis of which language or platform is better…  It is just a part of a series of blog posts that I will be writing as I learn more about RoR.  Everything is written from my perspective i.e. a 100% windows user and a .net developer.  So I am in foreign lands here.

The Setup

My current setup is a Mac Mini running Leopard with

The Environment

So after downloading all the RoR stuff from this website, I followed the steps and managed to create an empty application like this:

1. Open a terminal window 
2. Run rails /users/emad/code/favewares
3. Run cd path/to/your/new/application
4. Run ruby script/server

Then I opened http://localhost:3000 in my browser and got to the welcome page shown below.

Time to create the database, so I had to go download MySQL and install it which was surprisingly very straight forward:

1. Double click the MySQL installer
2. Double click the MySQL Startup Item installer

Then I ran into problems trying to get things going and quickly realized that being a windows user, I needed a GUI tool.  So I downloaded one from the MySQL website and installed it which was also pretty straight forward.  Until I tried to connect and got the error message “Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)”

After about an hour of trying different things and reading all sort of blogs and forums, I finally decided to restart my mac and voila, it worked…  At least Windows tells me that I need to restart.

Ok, so I got the GUI MySQL administrator up.  I added a database called favewares_development (to follow RoR naming conventions) and created some tables.

If you read anything about Rails you will end up reading the phrase “convention over configuration” a bazillion times; so here is a quick note on database naming conventions:

1. Database name = appname_development, appname_test, appname_production
2. Table name = plural e.g. authors, wares, stories, categories, etc…
3. Primary key name = id
4. Foreign keys = singularOfForeign_id e.g. category_id, author_id, etc…
5. Many to many tables = tablea_tableb in alphabetical order e.g. items_orders, classes_students

There is more information about Rails and Ruby naming conventions over here.

Database is now created, let’s edit the code.  Oops, no code editor - damn it.  I went to textmate and downloaded their 30-day trial.

Once I was done creating my tables, I generated the schema.rb file in the project’s db folder in textmate by clicking Bundles > Ruby on Rails > Database > Dump DB to schema.rb

My scheam.rb file looked like this:

Although I didn’t really have much so far, my brain had had enough of being outside its comfort zone - I miss windows… 

Stay tuned for the next part of this post.

[update] Part 2 is available here.

Share This

Posted in My Projects, Programming 3 Comments »
Tags: favewares, rails, ror, ruby

Live Mesh - Microsoft Finally Does Something Right

April 23, 2008

Personally, I am a huge fan of Microsoft but it has been very frustrating lately seeing all the “cool” stuff coming from Google and Apple.  So I got really excited when I heard about Live Mesh from Microsoft and I can’t wait to get my hands on it.  I am tired of running Groove to sync my computers and FolderShare to sync my mac.

Imagine having all your computers (PCs and Macs) and devices synced together and accessible from anywhere.  Imagine right-clicking a folder and adding it to a mesh to find it replicate through all your devices and computers as well as become accessible on the web.  This is what mesh promises to do.  Based on videos and blog posts that I have been reading and watching, it seems pretty slick.

Unfortunately, it is in technical preview and there is a waiting list.  Plus, currently, it only supports windows XP and Vista with more devices and mac support coming in the future.

I think for this to be successful, it will have to:

1. Support Windows Mobile devices
2. Support Mac
3. Support the iPhone
4. Have unlimited storage
5. be free

I am not sure how much it will be and if people will pay for it.  And if it is made free, how will Microsoft monetize.  I am not sure I will want to pay for it unless it solves all my sync problems.  It would also be nice if Microsoft Application can seamlessly work with the Mesh.  Imagine, if your Outlook data is automatically synced with all your mesh-connected devices without the need for Exchange server.

If you can’t wait for Live Mesh, you can try SugarSync.

Finally, Microsoft does something that is innovative and exciting.

Learn more about Live Mesh over here:

• Learn more at https://www.mesh.com/Welcome/LearnMore.aspx
• Live Mesh Blog at http://www.mesh.com/blog
• Read the team blog at http://blogs.msdn.com/livemesh/archive/2008/04/21/live-mesh-as-a-platform.aspx
• Watch a demo at http://www.on10.net/blogs/nic/Hands-on-with-Live-Mesh/

Share This

Posted in News No Comments »
Tags: live mesh, microsoft, software+services, synchronize machines., web services

My ALT.NET Code

April 23, 2008

Share This

Posted in Random No Comments »
Tags: alt.net, altnetcode