Function Imports in Entity Model with a non-Entity Return Type

Question

I have a stored procedure in my Entity Data Model and added it to the function imports.

Problem is... Visual Studio generates the function code in the model's code-behind if and only if I specify the return to be an entity type. Scalar and null return types do not work. Visual Studio does not generate the function code when I choose them.

Is there something I am missing, or is this a bug?
Any work-arounds?

---

Using Visual Studio 2008 v9.0.30729.1 SP (Service Pack 1)

Solution 1

It's not so much a bug as it is the lack of a feature. The Entity Framework just doesn't support stored procedures returning scalar values right now. I believe this is supposed to change in .NET 4.0. In the meantime, you can execute such a stored procedure by using the store connection, available via CreateDbCommand.

Solution 2

Since the only thing that works now is to map the return type to an entity, one workaround is to create a view that corresponds to the return data and create an entity for the view. This will only work if the SP is doing a SELECT to return a result set, not a return value. I got this to work with a sample app, like so: SP:

ALTER PROCEDURE [dbo].[DoSomething]
    @param1 varchar(50),
    @param2 varchar(50)
AS
BEGIN
    DECLARE @ID INT
    SET NOCOUNT ON;
    INSERT tmp_header (fname, lname) VALUES (@param1, @param2) 
    SET @ID = SCOPE_IDENTITY()
    SELECT @ID AS 'id'
END

VIEW:

CREATE VIEW [dbo].[View_1]
AS
SELECT   0 as  id

Create the function import setting the return type to View_1 and build the model.

in code:

    class Program
    {
      static void Main(string[] args)
      {
        using (PS_RADSTESTEntities ctx = new PS_RADSTESTEntities())
        {
          EntityConnection ec = ctx.Connection as EntityConnection;
          ec.Open();
          DbTransaction txn = ec.BeginTransaction();
          ObjectResult<View_1> result = ctx.DoSomething("Toby", "Kraft");
          View_1 row = result.Single();
          int id = row.id;
// do some other interesting things ...
          ctx.SaveChanges();
          txn.Commit();
        }
      }
    }

Be sure to set the column names exactly the same between the view and SP. Toby

Solution 3

I had the similar problem and solved it using this article:

http://blogs.msdn.com/bindeshv/archive/2008/11/21/using-stored-procedures-that-return-non-entity-type.aspx

Comments

[Robert Claypool]

Thanks. The UI is deceptive since it allows one to select scalar and null return types.