Supporting Stored Procedures that Return Primitive Types in Entity Framework

April 3, 2009

Supporting Stored Procedures that Return Primitive Types in Entity Framework

One drawback of Entity Supporting Stored Procedures that Return Primitive Types in Entity Framework
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:

Add Function Import Dialog  
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

Lets sum up, in the post I showed how we can use function imports of

stored procedures that return primitive types. Probably we won’t

need to use this solution when the next release of Entity Framework

will go out but meanwhile we will have to use EntityClient as a

solution to this problem.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>