Can you return multiple resultsets in a stored procedure using Entity Framework

Question

Hi,

Is it possible to return multiple result set in a stored procedure using Entity Framework.

Create Procedure P1()

Begin

select * from Employee;

select * from Customers;

End

Can I execute this stored procedure in entity framework and return 2 lists (1 for emp and 1 for cust).

 

Entities oDB = new Entities();

var ResultSet = oDB.P1();

return ResultSet.ToList();

Many Thanks,

Chocks

Answer 1

Hi Chocks;

Not out of the box. Microsoft has an extension that will allow this. Please see this link, ADO.NET Entity Framework Extensions for more information.

Fernando

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Hi,

Fernando is right.
Up untill EF4.1 (and including) there is no support for multiple result set in a stored procedure.
The ADO.NET Entity Framework Extensions might help you with that and this post will explain how to use the dll with SP - http://blogs.msdn.com/b/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx

Another option (which is harder to implement) is to use EntityClient to run the stored procedure and then create from the returned EntityDataReader the relevant entities by hand. Here is a link that can help you figure out how to use EntityClient - http://msdn.microsoft.com/en-us/library/bb896274.aspx

From EF4.2 (see the announcment about Microsoft Entity Framework June 2011 CTP - http://blogs.msdn.com/b/adonet/archive/2011/06/30/announcing-the-microsoft-entity-framework-june-2011-ctp.aspx) there will be support for that so there are things to expect in the future.

---

Gil Fink

Answer 3

Hi, Just wondering if you can expand on the link regarding using EntityClient.  When I imported my stored procedure as a Function Import it required me to select a return type (None, Scalar, Complex, or Entities).  If I have procedure that returns multiple select statements, I'm not sure how to handle it when I import it into the model.

I tried creating a complex type but it only sees the first SELECT columns.

I can find a way to work around this.  Thanks, Dave.