Querying an Entity Data Model - Entity Framework 6 Recipes - page 75

3-7. Returning Multiple Result Sets from a Stored Procedure
Problem
You have a stored procedure that returns multiple result sets, and you want to materialize entities from each result set.
Solution
Suppose that you have a model like the one shown in Figure 3-8 and a stored procedure like the one shown in Listing 3-14,
which returns both jobs and bids.
Figure 3-8. A model representing jobs and bids for the jobs
Listing 3-14. A Stored Procedure That Returns Multiple Result Sets
create procedure Chapter3.GetBidDetails
as
begin
select * from Chapter3.Job
select * from Chapter3.Bid
end
In our model, for each job we have zero or more bids. Our stored procedure returns all of the jobs and all of the
bids. We want to execute the stored procedure and materialize all of the jobs and all of the bids from the two result
sets. To do this, follow the pattern in Listing 3-15.
Listing 3-15. Materializing Jobs and Bids from the Two Result Sets Returned by Our Stored Procedure
using (var context = new EFRecipesEntities())
{
var job1 = new Job { JobDetails = "Re-surface Parking Log" };
var job2 = new Job { JobDetails = "Build Driveway" };
job1.Bids.Add(new Bid { Amount = 948M, Bidder = "ABC Paving" });
job1.Bids.Add(new Bid { Amount = 1028M, Bidder = "TopCoat Paving" });
job2.Bids.Add(new Bid { Amount = 502M, Bidder = "Ace Concrete" });
context.Jobs.AddObject(job1);
context.Jobs.AddObject(job2);
context.SaveChanges();
}