Entity Model Function Imports with a Non-Entity Return Type

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

Question

My entity data model has a stored procedure, which I have added to the function imports.

The issue is that Visual Studio only creates the function code in the code-behind of the model if and when I indicate that the return type should be an entity type. Null and scalar return types are incompatible. When I choose them, Visual Studio does not produce the function code.

Do I need to be doing anything else or is this a bug?
Any alternatives?


the version 9.0.30729.1 SP of Visual Studio 2008 (Service Pack 1)
1
8
5/1/2016 12:24:50 AM

Accepted Answer

It's less a defect than it is the absence of a feature. Currently, the Entity Framework simply does not allow stored operations that return scalar values. This is intended to change with.NET 4.0, in my opinion. You may run one of these saved procedures in the meantime by utilizing the store connection, which is accessible through CreateDbCommand.

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

Popular Answer

One solution is to construct a view that matches to the return data and create an entity for the view since right now the only thing that works is to map the return type to an entity. Only when the SP does a SELECT to return a result set and not a return value will this be effective. With a test app, I was able to make this function as follows: 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

Build the model after creating the function import and setting the return type to View 1.

as 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();
        }
      }
    }

Make sure that the column names in the view and SP are precisely the same. 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