Using stored procedures with EDM ObjectServices in the March CTP (with code)

One thing that I thought was particularly interesting in the Orcas Mar CTP was support for stored procedures. This support exists in LINQ to SQL and EDM ObjectServices; I thought I'd start with ObjectServices. There almost no documentation on this topic at this point, about half a page with an incomplete mapping schema example. That's to be expected at this point, though.

In Mar CTP, you can specify stored procs for insert/update/delete, but not for EDM queries yet. You need to change the SSDL (store schema definition language) and MDL (mapping definition language) files. With the correct mapping filechanges, AcceptChanges just calls the sprocs automatically. I started with a VS-generated set of mapping files and used XSD-based intellisence and error messages with line numbers to guide me along. Although you may have gotten rid of the XSD/XML errors or compile-time errors, your mapping files can fail to agree with one another. This occurs as a runtime error when you "new-up" an instance of your model class. Try-catch is your friend here.

SSDL changes were easier (just add the Function and Parameter elements for the proc) with one gotcha. My SSDL Namespace attribute was called "people"; if you specify "dbo.myproc" as the store name, EDM looks for [people].[dbo.myproc] at execution time. That's invalid in SQL Server. Specifying "myproc" as the store  name (or letting it default to the value of the "Name"attribute) causes the runtime to look for [people].[myproc]. The name of my sproc was actually [dbo].[myproc]; I had to change to SSDL Namespace attribute to "dbo" to make it work. Doing so made me change the MSL to match, but had no global ill effects.

The MSL was a little more interesting. I needed to put an "EntityTypeMapping" and "TableMappingFragment" in between my "EntitySetMapping" and "ScalarProperties" elements. Then I could add my ModificationFunctionMapping and Insert/Update/DeleteFunction elements as children of EntityTypeMapping. Because the XML schemas require elements be defined in order, I almost thought this feature was masked out in this CTP. Although I could see it in the schemas. It was there, of course, you just have to hit the intellisense in exactly the correct place in the document to show the element you're looking for.

OF COURSE, this is going to sound like complete gibberish without an example. I started with the easiest possible example: one table, few columns, primary key (its required) but not even an identity column. I also have two projects, one with the "vanilla" tool-generated CSDL/SSDL/MSL so I could refer back if I had problems. I did have problems. Coding three files of XML by hand, even with XSD-base intellisense, is right up there with [insert your least favorite chore here].

Three hours later, thanks to perseverance and SQLProfiler, I had a functioning prototype. It's posted here. I'm trying to decide which undoc'd part of this interesting set of mappings, models, and query lanaguages to try next. Or what tool to create to make this less of a chore. Enjoy!

ProcedureEDM.zip (75.96 KB)