Telerik OpenAccess ORM, MVC Extensions, and Dynamics!

While playing around with OpenAccess the other day, I wondered if it would be possible to have OpenAccess return a dynamic list, and if so, could I bind it to an MVC grid without specifying any columns names.  This could prove to be helpful in some reporting scenarios, where you just want the grid to output whatever a sproc, or query, returns.  This blog shows how to accomplish just that!

Step 1

To accomplish this task we will be taking advantage of the ADO API we released in Q2 2011.  To start out I simply created an extension method for the OpenAccessContext, which handles running the query, and then calls an extension method on the DataReader which returns an IEnumerable<dynamic>. 

OpenAccessContext Extension

The DataReader extension creates a new dynamic list, loops over the reader’s result set. While looping over the results an ExpandoObject is created for each result, and then the expando is cast as a IDictionary<string,object> which allows us to set the keys and values, at the end the expando is added to the dynamic list.

DataReader Extension

At this point we can now execute any query we want, and we will get back a IEnumerable<dynamic>  from OpenAccess. 

For Example

As you can see we are returning an IEnumerable of customers with orders to the view.

Step 1 Complete!

Step 2

Now to make this truly dynamic, I don’t want to have to specify any columns for the MVC grid to bind to.  Otherwise that would defeat the point altogether :)  So naturally, I go ahead, and bind my grid to the model, and see what happens. 

As you can see, I ended up with an empty grid.  This is because when binding to a dynamic, the MVC grid requires you to specify the member name as a string for each column.  In this case, we don’t want to do that so how can we work around it?

If you remember, a little bit up the page I mentioned that I created an ExpandoObject, casted it to a Dictionary, set the keys, and then return the Expando as a dynamic.  So, that means we can cast back to a dictionary right?  What if we cast to a dictionary, and interrogate the first object to get all keys.  This just might work :)

So now the code for my grid looks like this:

And the result:

Sweet we have data, Step 2 complete!

Gotchas

Going this route is not ideal, and i primarily wanted to show how you could return IEnumerable<dynamic> using the OpenAccess ADO API, and do something useful with the results.  This method is mainly useful for read-only scenarios, such as reporting, as I mentioned previously.  Also, in the current implementation there are some kinks in sorting when there is a field in the column with no value, so it would be best to handle sorting on the db side of things.  All in all it was a fun experiment, and hopefully you find it useful.

Happy coding!