Rounding the Corner

Using Entity Framework (EF) to call a stored procedure that returns multiple result sets (aka “MARS”) is something of a moving target. This post will explain how to use MARS in the upcoming Entity Framework 5 (currently available in the VS 2011 beta or via NuGet). In particular, I want to expand on this Microsoft Post showing how to leverage entity types.

History

But First, a little history on the topic.  Why is MARS a moving target?   In EF 4 it was impossible—well, sort of.  Once entity framework extensions were released it was possible to write the ADO  connection/command yourself  while still leveraging the conceptual model and hydration capabilities of EF.  The process hinged on a snippet of code something like:  Translate<MyEntityFrameworkType>(myADO_Reader).

Then along came the June 2011 CTP and hallelujah “stored procedures can now have multiple result sets.”   EF 4.2 and 4.3 were subsequently release and the feature was missing. Huh? Well, the CTP apparently required some changes to the core EF object and would only be released with .NET 4.5.  

So EF 5 will apparently support MARS.  From the commentary, however,  it appears that MARS will be supported in EF’s plumbing, but the modeling tools won’t expose the option.  This leaves one editing the EDXM manually—not fun, but arguably better than writing your own ADO code like EF 4 required.  

The Microsoft Article

Over in Redmond the ADO.NET team was kind enough to post a walk through for using MARS detailing how to fill two complex types.  The article is pretty long, but if you’re already using EF step 7 and 8 explain how the EDMX file needs to be edited. Then step 9 shows the syntax for retrieving subsequent result sets.

If you’re familiar with EF you know there are Entity Types, which loosely correspond to the tables you see in your model browser. There are also Complex Types, which the tool cajoles you to use as the return type for most stored procedures.

Unfortunately, what the article doesn’t explain is how to return entity types.  As an EF user Entity Types are a large portion of your model, and leveraging these types is likely important.  Following is some simple EDXM to show how Entity Types can be returned.

MARS & Entity Types

Once you’re familiar with  the Microsoft article there isn’t much to say…all you need is the syntax for returning Entities.   Figuring out that syntax is, well, not very fun.  It took quite a bit of digging in the MS-MSL and MS-CSDL specs listed at the end of the article.  I managed, however, to coble together a working example and the result is fairly easy to understand..

My working example consisted of a tiny two table model:  Two tables (Table_A and Table_B) and a single stored procedure (MyMARS_Proc which SELECT’s * from A & B).    Given this model, your EDX would be as follows.

In the CSDL section of your model :

<edmx:ConceptualModels>
   <Schema Namespace="myModel" ...>
     <EntityContainer Name="myModelEntities" ....>
     ......
     <!--  
        this is what “function import” wrote, that I’m overwriting…          
        FunctionImport Name="MyMARS_Proc" ReturnType="Collection(myModel.Table_A)"/> 
      --> 
      <FunctionImport Name="MyMARS_Proc" > 
         <ReturnType Type="Collection(myModel.Table_A)" EntitySet="Table_As"/> 
         <ReturnType Type="Collection(myModel.Table_B)" EntitySet="Table_Bs"/> 
      </FunctionImport> 
 

Then down in the MSL (C-S mapping) section you’ll want:

<!-- C-S mapping content -->
<edmx:Mappings>
  <Mapping Space="C-S" ....>
    <EntityContainerMapping  ....>

       <FunctionImportMapping FunctionImportName="MyMARS_Proc"
                               FunctionName="myModel.Store.MyMARS_Proc"> 
            <ResultMapping> 
                <EntityTypeMapping  TypeName="myModel.Table_A"/> 
            </ResultMapping> 
            <ResultMapping> 
                <EntityTypeMapping TypeName="myModel.Table_B"/> 
            </ResultMapping> 
       </FunctionImportMapping>

Research

• Entity Framework Extensions
• Entity Framework June 2011 CTP Announcement
• Walkthrough: Multiple Result Sets from Stored Procedure (June CTP)
• MS-MSL Specification - 2.1.24 Structures - Elements – ResultMapping
• MS-CSDL Specification - 2.1.16 Structures - Elements - FunctionImport Return Type

Development Entity Framework, MARS