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

using (var context = new EFRecipesEntities())
{
var conn = context.Database.Connection;
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "Chapter3.GetBidDetails";
conn.Open();
var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
var jobs = ((IObjectContextAdapter)context).ObjectContext.Translate<Job>(reader, "Jobs",
MergeOption.AppendOnly).ToList();
reader.NextResult();
((IObjectContextAdapter)context).ObjectContext.Translate<Bid>(reader, "Bids",
MergeOption.AppendOnly).ToList();
foreach (var job in jobs)
{
Console.WriteLine("\nJob: {0}", job.JobDetails);
foreach (var bid in job.Bids)
{
Console.WriteLine("\tBid: {0} from {1}",
bid.Amount.ToString("C"), bid.Bidder);
}
}
}
Following is the output of the code in Listing 3-15:
Job: Re-surface Parking Log
Bid: $948.00 from ABC Paving
Bid: $1,028.00 from TopCoat Paving
Job: Build Driveway
Bid: $502.00 from Ace Concrete
How It Works
We start out by adding a couple of jobs and a few bids for the jobs. After adding them to the context, we use
SaveChanges() to save them to the database.
Entity Framework 5.0 has improved capabilities for working with multiple results sets returned from a stored
procedure. However, to leverage this functionality, you'll have to use the legacy ObjectContext object, as the more
recent DbContext object does not directly support multiple result sets. To solve the problem, we read the stored
procedure data using the familiar SqlClient pattern. This pattern involves creating a SqlConnection , SqlCommand
setting the command text to the name of the stored procedure and calling ExecuteReader() to get a data reader.
With a reader in hand, we use the Translate() method from the ObjectContext to materialize instances of the
Job entity from the reader. This method takes a reader; the entity set name, and a merge option. The entity set name
is required because an entity can live in multiple entity sets. Entity Framework needs to know which to use.
The merge option parameter is a little more interesting. Using MergeOption.AppendOnly causes the new instances
to be added to the object context and tracked. We use this option because we want to use Entity Framework's entity
span to fix up the associations automatically between jobs and bids. For this to happen, we simply add to the context
all of the jobs and all of the bids. Entity Framework will automatically associate the bids to the right jobs. This saves us
a great deal of tedious code.