Entity Model Function Imports 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)

1
8
5/1/2016 12:24:50 AM

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.

8
2/23/2009 5:56:33 PM

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow