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
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.
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:
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.
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.
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.
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.