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

.net .net-3.5 ado.net entity-framework visual-studio

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)

Accepted Answer

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.


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why