Entity Framework 5 stored procedure with multiple result sets

Question

I'm currently coding with asp.net mvc 4.5 and EF5 Beta 2 and I have a stored procedure which returns multiple result sets. I've found this site and it says that the newer version (which I'm using) already has support for multiple result sets.

Now I can't seem to find that support. As I'm fairly new to the EF altogether, I hope I'm not doing something wrong.

I have no corresponding entities in my database for the result sets created.

Solution 1

The core EF libraries support multi result set procedures. Unfortunately the designer does not -- and it's not clear if it will upon release.

I too found the documentation a bit sparse, particularly for returning multiple entity types (as opposed to complex types). Fortunately, manually editing the EDMX isn't too complicated. I wrote up a blog post on topic ....

Entity Framework 5 – Multiple Entity-Typed Result Sets from a Stored Procedure (note, my server may take a few minutes for the disks to spin up as not too many people traffic my humble little blog).

The short of it is in the CSDL section ..

<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 in the MSL (C-S Mapping section) you'll want...

<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>

Solution 2

That is quite interesting question. I played with .NET 4.5 Beta / VS11 Beta for a while and I have two observations:

• It looks like there is no support in designer for multiple result sets - everything must be mapped manually in EDMX opened as XML
• It looks like the former article about June CTP 2011 is no longer valid because MSL doesn't allow declaring multiple ResultMapping to map different result sets and each ResultMapping can map only single result set

EDIT:

I was wrong. It works at runtime. Only designer complains about EDMX validation but MSL itself accepts multiple ResultMapping elements. The original walkthrough linked in the question is still valid.

Comments

[Ladislav Mrnka]

Are you using code first or database first (EDMX)?

[whodares]

Oops, seems I forgot to include that; I'm using Database first (.edmx)

[Gert Arnold]

Here it is still mentioned as new feature...

[whodares]

Thank you for your response, but this isn't really EF. This is just the old way where you use your model, but still have a ton of code.