Entity Framework - Stored Procedure (2)

In Entity Framework - Stored Procedure (1), I defined the store data model and the object data model.
In this blog, I will explore ways to use a stored procedure to materialize objects based on our defined data model.
Initially, I thought the framework would be able to use a stored procedure in its generated T-SQL instead of doing a SELECT explicitly. To see if that was the case, I created a stored procedure to get all drivers for a given policyId as follows:

IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_GetDriversPerPolicy]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].[qp_GetDriversPerPolicy];
GO
CREATE PROCEDURE ef.qp_GetDriversPerPolicy (
@iPolicyId INT
)
AS
BEGIN
SELECT D.driverId, D.policyId, D.age, D.ts
FROM ef.Driver D
WHERE D.policyId = @iPolicyId;
END;
GO
GRANT EXEC ON [ef].[qp_GetDriversPerPolicy] TO Public;
GO
-- Example:
EXEC ef.[qp_GetDriversPerPolicy] @iPolicyId = 2;
GO

Then I followed the instructions from
How to: Define a Model with a Stored Procedure
to modify WORK .edmx file so that
In SSDL, in the element, I added




In CSDL, in the element, I added




And in MSL, under , I added


I saved the XML file WORK.edmx and in VS, I clicked "Run Custom Tool" on item WORK.edmx to update the generated class file.

Then, I creatd a Console project whose Main program basically ran the following code:

using (WORKModel.WORKEntities ctx = new WORKModel.WORKEntities())
{
try
{

ObjectQuery pl =
ctx.Policies//.Include("Drivers")
.Where("it.policyId = @id", new ObjectParameter("id", 2));
var p = pl.Select(s => s).FirstOrDefault();
p.Drivers.Load();

Console.WriteLine("policyId\t expDt\t riskState\t description");
Console.WriteLine("{0}\t {1}\t {2}\t {3}", p.policyId, p.expDt, p.riskState, p.description);

Console.WriteLine("age");
foreach (WORKModel.Driver dr in p.Drivers)
{
Console.WriteLine("{0}", dr.age);
}

Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine("Exception Occurred: {0}", e.Message);
Console.ReadLine();
}
}

I was expecting "p.Drivers.Load()" to use our stored procedure, but instead, the following T-SQL was excuted on the SQL server:

exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[driverId] AS [driverId],
[Extent1].[age] AS [age],
[Extent1].[ts] AS [ts],
[Extent1].[policyId] AS [policyId]
FROM [ef].[Driver] AS [Extent1]
WHERE [Extent1].[policyId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

So the conclusion is that EF currently ignores stored procedures when it loads data into the ObjectContext object instances. It would be nice in future versions to have the choice of using stored procedures in that context if available.

Then, is it possible to use our stored procedure at all in the ObjectContext?
MSDN article How to: Execute a Query Using a Stored Procedure (Entity Framework)
shows that it is possible. To test that, create a Console project with its Main function as follows:
using (WORKModel.WORKEntities ctx = new WORKModel.WORKEntities())
{
try
{
int policyId = 2;
Console.WriteLine("driverId\t age");
foreach (WORKModel.Driver d in ctx.GetDriversPerPolicy(policyId))
{
Console.WriteLine("{0}\t {1}", d.driverId, d.age);
}
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine("Exception Occurred: {0}", e.Message);
Console.ReadLine();
}
}
SQL Profiler indeed shows the following has been excuted:
exec [ef].[qp_GetDriversPerPolicy] @iPolicyId=2.

EF makes our stored procedure a strongly-type function and makes it very convenient to execute a stored procedure explicitly in user codes. It uses the ObjectContext's native ADO.NET connection to execute the stored procedure and we also notice that in between two consecutive calls to the stored procedure, SQL Profiler shows a connection reset by "exec sp_reset_connection". That is because EF wants to make sure the execution state for each stored proc call is initialized. See
What does sp_reset_connection do?

for more information about the details of connection reset.