Supporting Stored Procedures that Return Primitive Types in Entity Framework

Supporting Stored Procedures that Return Primitive Types in Entity Framework

One drawback of Entity
Framework designer is that it does
not generate code in the object
context for function imports
of stored procedures that return a
primitive type. This post will explain
how to achieve this functionality up until the designer will support it in the
next release of Entity Framework.

Supporting Stored Procedures that Return Primitive Types

As indicated in the post’s prolog the Entity Framework designer in V1 doesn’t
generate code for function imports of stored procedures that return a
primitive type. This makes the function import unavailable to use by the
object context of Entity Framework as opposed to other function import
mapping scenarios. We can work around this problem with the use of
EntityClient functionality.

The Example

In the example I’m going to use the following stored procedure which
returns the total budget of a school by the date range it gets as
parameters. The budget is stored as money type in the database.

CREATE PROCEDURE dbo.SchoolBudgetForDateRange

    @StartDate DATETIME,

    @EndDate DATETIME

AS

SET NOCOUNT ON;

SELECT SUM(Department.Budget) AS SchoolBudget

FROM Department 

WHERE StartDate BETWEEN @StartDate AND @EndDate

Step 1
Add a FunctionImport for the stored procedure.
In the designer press the right mouse button and click on the
Add –> Function Import from the menu. The Add Function Import dialog
will be open and insert the following details:
  
Press OK to end this process. After doing step 1 we will have a FunctionImport
element in the CSDL but we won’t be able to use it from the object context
because no code will be generated for it.

Step 2
Use EntityClient to query the data. The following code shows how to
open a connection, create a stored procedure command, add parameters
to it and execute it using EntityClient.

static void Main(string[] args)

{

    decimal result = 0;

    using (var conn = new EntityConnection("Name = SchoolEntities"))

    {

        var cmd = conn.CreateCommand();

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.CommandText = "SchoolEntities.SchoolBudgetForDateRange";

        cmd.Parameters.AddWithValue("StartDate", new DateTime(2009, 1, 1));

        cmd.Parameters.AddWithValue("EndDate", DateTime.Now);              

        conn.Open();

        result = Convert.ToDecimal(cmd.ExecuteScalar());

    }

    Console.WriteLine("The school budget is {0}", result);

    Console.ReadLine();

}

As you can see it’s very close to using old fashion ADO.NET but
it works on the entity data model instead of a database.

Summary