Returning Dynamic Data with Entity Framework and WCF
Recently, I found myself with the task of exposing a catch-all reporting stored procedure through WCF. This single stored procedure accepts a standard set of parameters including a report name. Depending on the report name however, the rows and columns may vary significantly.
We’re going to gloss over the fact that there are probably better ways of writing the reporting layer. There are some things you just can’t change.
The heart of the problem is that both Entity Framework and WCF would be much happier with rigidly defined data contracts and models. A dynamically created resultset that’s the result of an ad hoc query or varying list of queries throws a wrench into these tools.
The first issue is to execute the stored procedure or SQL command.
The first task is to get the database context into db. We use this database context to create the command object representing the SQL or Stored Procedure we’re going to execute against the database.
If you’re using a stored procedure, make sure that you update the command-type property – if you miss this step, you may get confusing warnings about missing parameters.
This command object is then executed, each row is converted to an Expando object, and the result is serialized to a string. Easy. Sure, but the command object doesn’t have the AddParameterWithValue method, Expando objects are new to .NET 4, and WCF still wants a data contract to serialize to JSON. So maybe it’s not that easy.
First things first. Extension methods.
This method is courtesy of the MSDN Forum. Extension methods are really powerful bits of magic that I always forget about. Used effectively, they manage to make your code easier to write and easier to read. This particular version extends the dbCommand object to overcome the missing constructor on the parameter class.
Expando Objects – No. Seriously. That’s what they’re called
Microsoft added the ExpandoObject class in .NET 4.0. It’s simply a class of object that can have members added at run time. In this case, we iterate over the reader fields, creating an expando object.