Entity Framework 5 stored procedure with multiple result sets

Question

I am currently encoding asp.net mvc 4.5 and EF5 Beta 2, and I have a stored procedure that returns multiple result sets. I found this site and it says that the newer version (which I use) already supports several result sets.

Now I can not find this support. Since I'm fairly new to EF in general, I hope that I'm not mistaken.

I do not have matching objects in my database for the generated result sets.

Answer 1

EF core libraries support multiple result set procedures. Unfortunately, the designer does not - and it is not clear whether this will be after the release.

I also found the documentation a bit sparse, especially for returning several types of objects (as opposed to complex types). Fortunately, manually editing EDMX is not too complicated. I wrote a blog post on the topic ....

Entity Framework 5 - Multiple result sets from a stored procedure (note, my server may take several minutes for disks when not many people spend my modest little blog).

Briefly this 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 (CS Mapping) section you will want ...

 <edmx:Mappings> <Mapping Space="CS" ....> <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> 

Answer 2

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

• It seems that the designer does not have support for multiple result sets - everything should be displayed manually in EDMX, opened as XML
• It seems that the previous article about the June CTP 2011 is no longer valid because MSL does not allow multiple ResultMapping to be declared for matching different result sets, and each ResultMapping can only display one set of results

EDIT:

I was wrong. It works at runtime. Only the designer complains about EDMX validation, but MSL itself accepts several ResultMapping elements. The initial step-by-step guide related to the question remains valid.

Answer 3

I made this code

Procedure following

  alter procedure dbo.proc_getmorethanonetable as select * from dbo.Orders select * from dbo.Items select * from dbo.Users u inner join dbo.Orders o on o.User_ID = u.User_ID go 

The code is so simple here we go

  using (var db = new EF_DEMOEntities()) { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "[dbo].[proc_getmorethanonetable]"; try { db.Database.Connection.Open(); using (var reader = cmd.ExecuteReader()) { var orders = ((IObjectContextAdapter)db).ObjectContext.Translate<Order>(reader); GridView1.DataSource = orders.ToList(); GridView1.DataBind(); reader.NextResult(); var items = ((IObjectContextAdapter)db).ObjectContext.Translate<Item>(reader); GridView2.DataSource = items.ToList(); GridView2.DataBind(); } } finally { db.Database.Connection.Close(); } } 

and here you go :)