Using Stored Procedures with the ADO.NET Entity Framework

Using Stored Procedures with the ADO.NET Entity Framework

Using stored procedures with the ADO.NET Entity Framework requires mapping functions. Calling these stored procedures is complex and requires some coding.

Providing Functionality

The Connection object includes properties and methods that provide enhanced statistics functionality that are standard in the ADO.NET data provider, but are not available at the ADO.NET Entity Framework layer. Instead, you expose the same functionality through "pseudo" stored procedures.

This approach uses the Entity Data Model (EDM) to achieve results that correspond to the ADO.NET results. This in effect provides entities and functions backed by pseudo stored procedures.

Table 19 lists the mapping of the data provider’s Connection properties to the corresponding pseudo stored procedure.

Table 19

Mapping to Pseudo Stored Procedure

Connection Property

Pseudo Stored Procedure

StatisticsEnabled

Psql_Connection_EnableStatistics

Psql_Connection_DisableStatistics

Connection Method

Pseudo Stored Procedure

ResetStatistics

Psql_Connection_ResetStatistics

RetrieveStatistics

Psql_Connection_RetrieveStatistics

Applications must use the ObjectContext to create a stored procedure command as shown in the following C# code fragment:

using (MyContext context = new MyContext())

EntityConnection entityConnection = (EntityConnection)context.Connection;

// The EntityConnection exposes the underlying store connection

DbConnection storeConnection = entityConnection.StoreConnection;

DbCommand command = storeConnection.CreateCommand();

command.CommandText = "Psql_Connection_EnableStatistics";

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new PsqlParameter("cid", 1));

bool openingConnection = command.Connection.State == ConnectionState.Closed;

if (openingConnection) { command.Connection.Open(); }

result = command.ExecuteNonQuery();

if (openingConnection && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); }

Using Overloaded Stored Procedures

If you have multiple overloaded stored procedures, the PSQL Entity Framework data provider appends an identifier to each stored procedure name so you can distinguish between them in the SSDL. The data provider removes the appended identifier before calling the stored procedure for your application.