Does the current Astoria/EDM CTP work against SQLCLR table-valued functions or stored procs?
What is the easiest/most straightforward way of integrating a propiertary data store with the current CTP?
Michael.
Hi Michael,
Astoria relies on the Entity Framework for that, so you can turn the question into "does the Entity Framework support TVFs/procs?".
In the Orcas Beta 1 bits, the Entity Framework includes support for stored-procedures for update processing (you can tell the system to use your custom stored-procs instead of automatically generating insert/update/delete SQL statements), but it doesn't support stored-procedures for data retrieval. We already built that, and it will be there in the next CTP (or whatever intermediate release we do). Once we have Astoria bits that run on top of that CTP, you'll be able to use stored-procedures directly and easily.
TVFs are a bit more complicated. We don't currently have support for TVFs (regardless of whether they are SQLCLR or T-SQL TVFs). For the particular case where you have a TVF without parameters, you can create a view on top of the TVF (select * from dbo.tvf()) and then point the Entity Framework to that (just lie to it, tell it it is a table in the SSDL file). That will give you read support. To enable write support you can either setup stored-procedures for entity-set, or use INSTEAD OF triggers in the view on the server side.
To integrate other data stores you have two options:
a) if the store looks a lot like a database, you could write an ADO.NET vNext provider and plug it into the Entity Framework. It's more work to build the provider, but then you get great integration across the rest of the stack.
b) the other option is to write "service operations" that pull the data from your data source and send it to the client. Those service operations would return IEnumerable<T> where "T" still needs to be some entity type (even though it didn't come from the database).
NOTE: the restriction of T having to be an entity type is specific to this CTP; we'll try to enable use of any type later on...we will see if it works out well.
For more information on "service operations" check out the "using astoria" document at http://astoria.mslivelabs.com/UsingMicrosoftCodenameAstoria.doc.
Pablo Castro
Technical Lead
Microsoft Corporation
Thank you Pablo,
To confirm, with the current Astoria, EDM and Orcas bits, a SQL view over a SQL CLR TVF will work today?
Michael.
Yes, it will work today, but it requires some manual tweaking. Specifically, first generate a default EDM schema and mapping using the ADO.NET Entity Framework or the edmgen.exe command-line tool.
Then look at the .ssdl file, which is the file that contains the definition of the store (logical) schema; in the file, either replace a table (entity-set in ssdl/csdl terms) with your view name, or add a new one with theview name, and its corresponding columns.
If you created the view as a new table in the .ssdl file, add the corresponding mappings to the .msl file (follow the pattern from the other EntitySetMapping entries) and add the conceptual level view of it to the .csdl file.
Note that in ssdl/csdl you need to add both the type definition (entitytype) and the entity-set.
At that point you should be able to use it, using the name of the entity-set defined in the CSDL to access instances in that container.
Please let me know if you run into any issues.
Pablo Castro
Technical Lead
Microsoft Corporation
