Dixin / EntityFramework.Functions

EntityFramework.Functions library implements Entity Framework code first support for stored procedures (with single result type, multiple result types, output parameter), table-valued functions (returning entity type, complex type), scalar-valued functions (composable, non-composable), aggregate functions, built-in functions, niladic functions, and model defined functions.
https://weblogs.asp.net/Dixin/EntityFramework.Functions
MIT License
79 stars 27 forks source link

Can't get default value of DB procedure / System.Int32 is not supported in conceptual model as a structural type #27

Open Talpidae1 opened 7 years ago

Talpidae1 commented 7 years ago

I've got the following procedure

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]
   @paramIn INT,
   @paramOut1 INT OUTPUT,
   @paramOut2 INT OUTPUT
AS

BEGIN
   DECLARE
      @iErrorCode INT

   SET @paramOut1 = 1
   SET @paramOut2 = 2
   SET @iErrorCode = -42

   RETURN @iErrorCode

END

GO

I get the output parameters, but am not able to get the return value.

I tried the following:

        [Function(FunctionType.StoredProcedure, "TestProcedure", Schema = "dbo")]
        public ObjectResult<int> TestProcedure([Parameter(DbType = "int", Name = "paramIn")] int paramIn,
            [Parameter(DbType = "int", ClrType = typeof(int), Name = "paramOut1")] ObjectParameter paramOut1,
            [Parameter(DbType = "int", ClrType = typeof(int), Name = "paramOut2")] ObjectParameter paramOut2)
        {
            //Input parameter 
            var paramInObjectParameter = new ObjectParameter("paramIn", typeof(int))
            {
                Value = 100,
            };

            using (var context = new BlContext())
            {
                var objectContext = context.ObjectContext();
                return objectContext.ExecuteFunction<int>("TestProcedure", paramInObjectParameter, paramOut1, paramOut2);
            }
        }

This leads to an exception: 'System.NotSupportedException: 'System.Int32 for method TestProcedure is not supported in conceptual model as a structural type.''

I also tried:

        [Function(FunctionType.StoredProcedure, "TestProcedure", Schema = "dbo")]
        public int TestProcedure([Parameter(DbType = "int", Name = "paramIn")] int paramIn,
            [Parameter(DbType = "int", ClrType = typeof(int), Name = "paramOut1")] ObjectParameter paramOut1,
            [Parameter(DbType = "int", ClrType = typeof(int), Name = "paramOut2")] ObjectParameter paramOut2)
        {
            //Input parameter 
            var paramInObjectParameter = new ObjectParameter("paramIn", typeof(int))
            {
                Value = 100,
            };

            using (var context = new BlContext())
            {
                var objectContext = context.ObjectContext();
                return objectContext.ExecuteFunction("TestProcedure", paramInObjectParameter, paramOut1, paramOut2);
            }
        }

This will always return '-1'.

After reading your documentation, I tried to get the correct result type with sys.dm_exec_describe_first_result_set, but this gives me an empty table as a result.

Is this feature missing at the moment or am I doing anything wrong?

Thank you for your help and this great library!