R – Dynamic Data with Entity Framework and RIA Services

entity-frameworksilverlight-3.0wcf-ria-services

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?

Best Answer

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.
Related Topic