Dynamic Data with Entity Framework and RIA Services

Question

This question is an extension of another question, but I think it warrants its own thread. See See Silverlight Question

I have a stored procedure (SQL 2005) that returns a dynamic data set (different columns/schema) each time it is called.

I want to consume this in Silverlight 3.0 so I need to somehow wire this up using Entity Framework and RIA Services. I also need this to be Bindable (Silverlight Grid) so I need these dynamic columns to be accessible via properties (grid limitation). Any ideas?

Answer 1

In the currently shipping version of the Entity Framework, the only type of stored procedures you can map are those which return entity types. The mapping is done, generally, before you compile, although it seems at least theoretically possible to generate Entity Framework metadata at runtime.

Therefore, I see a few choices.

1. Give up on the whole idea of consuming a procedure which does not return a defined schema. You will never be able to map such a procedure before you compile.
2. Dynamically generate EDMX at runtime in order to map an entity type to the expected output columns of the procedure before you invoke. Note that the current version of the Entity Framework is a bit finicky about the columns a procedure returns; you can find documentation about this on MSDN.
3. In .NET 4.0, there are new features which allow you to inform the Entity Framework about your client schema at runtime without having to generate EDMX first. You might be able to leverage these features in order to map some entity type to the expected output columns of the procedure.
4. Again, in .NET 4.0, there may be support for procs which return scalar values. I can't remember if this is the case or not.
5. You can always get a standard database connection from the entity connection and execute the procedure directly, using regular SqlCommands. Unfortunately, this makes your code database-provider-specific, but it may be the simplest solution to your problem. Indeed, using such a procedure at all is already database-server-specific.

Answer 2

You might use a WCF web service wraper for accesing your SP and use the WCF service as data source Brad Abrams has a way to do that on his series of articles on RIA Services

Answer 3

hi, i know this is an older post but i'm coming across the same issue. I've moved to option #5 for now but really looking for a more meaningful solution.

I basically have a stored proc that returns one set of results with fixed fields plus a set of dynamic ones (actually pivot results). and those results are bound to a DataGridView.

The user is allowed to make changes to the grid and changes are queued in the objectcontext, once changes are complete users hits a button to fire context.savechanges().

I've been able to map the fixed fields to an entity object and in turn bind to the gridview without a problem. however, updating a value for one of the dynamic fields requires a "manual" solution (firing direct updates to a dataadapter on cell edit).

When the user triggers these actions one at a time between SaveChanges() and AdapterUpdates() everything works fine.

But when a user triggers an entitychange (AddRow) and then a direct update (cell change) we lose sync between the row added and the field updated for that row.

sorry for the long email but if anyone has any other suggestions please post them. i'm looking at the codeonly option but it appears to be for just creating your own entity objects/contexts