none
Get an integer result from a stored procedure fails using Entity Framework when calling Context.Database.SqlQuery<int> RRS feed

  • Question

  • My team has a doubt of how to resolve this problem, can any one help me us with the problem we have. We posted the question on stackoverflow and this is the link. Thanks in advance

    https://stackoverflow.com/questions/46228223/get-an-integer-result-from-a-stored-procedure-fails-using-entity-framework-when


    • Edited by izinova Friday, September 15, 2017 6:42 PM
    Friday, September 15, 2017 6:41 PM

All replies

  • You can use the EF backdoor and execute the Sproc as you would normally do using ADO.NET,  SQL Command objects and a datareader.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    https://blogs.msdn.microsoft.com/alexj/2009/03/25/tips-and-tricks/

    The EF backdoor example is using ObjectContext.  However, you can make an ObjectContext from a DBContext;

    https://stackoverflow.com/questions/8059900/convert-dbcontext-to-objectcontext-for-use-with-gridview

    Sunday, September 17, 2017 11:11 AM
  • I tried that and it didn't work, it shows the same error I am getting with EF
    Monday, September 18, 2017 7:03 PM
  • I tried that and it didn't work, it shows the same error I am getting with EF

    One would assume that one knows how to get the Return Value from a Sproc.

    https://stackoverflow.com/questions/46228223/get-an-integer-result-from-a-stored-procedure-fails-using-entity-framework-when

    The Sproc being ran using the connection provided by EF through the backdoor on EF would be no different than if EF was not being used at all. 

    Monday, September 18, 2017 8:45 PM
  • I tried that and it didn't work, it shows the same error I am getting with EF

    One would assume that one knows how to get the Return Value from a Sproc.

    https://stackoverflow.com/questions/46228223/get-an-integer-result-from-a-stored-procedure-fails-using-entity-framework-when

    The Sproc being ran using the connection provided by EF through the backdoor on EF would be no different than if EF was not being used at all. 

    oops wrong link provided....

    https://blogs.msdn.microsoft.com/spike/2009/05/07/a-simple-example-on-how-to-get-return-and-out-parameter-values-using-ado-net/

    Monday, September 18, 2017 8:51 PM
  • it worked with the SQLCommand, thanks. But there is not a way to do the same thing with EF? Instead of taking the backdoor?

    var connection = Context.Database.Connection.ConnectionString;
                    try
                    {
                        using (var con = new SqlConnection(connection))
                        {
                            con.Open();
                            SqlCommand cmd = new SqlCommand(storedProcedure, con);
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(allParams.ToArray());
                            cmd.ExecuteNonQuery();
                            var outparameterName = outParameters.FirstOrDefault().ParameterName;
                            int res = (int)cmd.Parameters[outparameterName].Value;
                            con.Close();
                            return res;
                        }
                    }
                    catch (Exception e)
                    {

                    }

    Wednesday, September 20, 2017 4:22 PM
  • My question would be why are you even using an Sproc to do a record insert and get the ID of the inserted record, because EF can do it?

    Wednesday, September 20, 2017 11:17 PM
  • No, we are calling the store procedure because it is part of a legacy application. We are rewriting a new one that has an integration with the legacy one, that's why we need to call certain sp and not modify them. This one in particular is inserting an element on a temp table at some point and then returns the value of the variable @id. We just need to get the @id value.
    Thursday, September 21, 2017 8:50 PM
  • Well, EF can't directly deal with Return Value from a Sproc. So you either come up with using a connectionsting, use ASO.NET and the SQL Command objects separately, or you use the backdoor and use the connection provided by EF and use ADO.NET and SQL Command objects.  
    Friday, September 22, 2017 1:09 AM
  • Hi izinova,

    Thank you for posting here.

    >>we are calling the store procedure because it is part of a legacy application.

    If you would like to call a stored procedure, you refer to my code, as follows.

    My stored procedure:

    CREATE PROCEDURE [dbo].[user_Procedure1]
           @ProductType nvarchar(50),
           @Description nvarchar(50)
    AS
           Insert into ProductType (ProductType,Description) values(@ProductType,@Description)
    RETURN 1
    

    Calling the above procedure:

    namespace App6EFDataBase
    {
        class Program
        {
            static void Main(string[] args)
            {
                MyDataBaseEntities db = new MyDataBaseEntities();
               var result = db.Database.ExecuteSqlCommand("[dbo].[user_Procedure1] @ProductType,@Description", new SqlParameter("@ProductType", "aaa"), new SqlParameter("@Description", "all kind of fruit"));
                db.SaveChanges();
                var select = db.ProductTypes.ToList();
            }
        }
    }
    

    Here is my result:

    If you have any other question about my reply, please contact me freely.

    Best Regards,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 22, 2017 2:27 AM