How to return a value from a stored procedure to EF

[Origin]: https://stackoverflow.com/questions/35352605/how-to-return-a-value-from-a-stored-procedure-to-ef

I’m attempting to call a stored proc through EF and retrieve a return value from the stored proc. I’ve used this answer as a guide. Here is my stored proc:

CREATE PROCEDURE [dbo].[usp_test]
(
    @result int OUT
)
AS
BEGIN

--DO STUFF

SET @result = 0
END

Here is how I’m calling it from EF:

var status = new SqlParameter
{
    ParameterName = "result",
    DbType = DbType.Int32,
    Direction = ParameterDirection.Output 
};
var result = context.Database.SqlQuery<int>("EXEC usp_test @result", status);
var wasSuccessful = result.First() == 1;
if (!wasSuccessful)
{
    //DO STUFF
}

I’m getting this error message:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types

What am I doing wrong?

shareedit

Try select @result before end of procedure.

CREATE PROCEDURE [dbo].[usp_test]
(
    @result int OUT
)
AS
BEGIN

--DO STUFF

SET @result = 0
Select @result
END

Hope it works.

shareedit
Advertisements

How to retrieve output parameter from stored procedure in Entity Framework

[Author]: https://code.msdn.microsoft.com/How-to-retrieve-output-e85526ba

How to get output parameter in stored procedure from Entity Framework(CSEFOutputParameter)

Introduction

This sample demonstrates how to use ObjectParameter instance to get the value of output parameter in Entity Framework.

Building the Sample

  1. Start Visual Studio 2012 and select File > Open > Project/Solution.
  2. Go to the directory in which you download the sample. Go to the directory named for   the sample, and double-click the Microsoft Visual Studio Solution (.sln) file.
  3. Attach the database file EFDemoDB.mdf under the folder _External_Dependencies to your SQL Server 2008R2 database instance.
  4. Modify the connection string in the App.config according to your SQL Server 2008R2 database instance name.
  5. Press F7 or use Build > Build Solution to build the sample.

Running the Sample

  1. Right click the solution and built it.
  2. Press F5 to run the project, a console window will appear.

3

3.Follow the prompt to input person information.

Using the Code

Stored Procedure

ALTER PROCEDURE [dbo].[InsertPerson]    
@Name varchar(50),    
@Description varchar(200),      
@ID int OUT    
AS    
INSERT INTO Person(Name,Description) VALUES(@Name,@Description)    
SET @ID = SCOPE_IDENTITY() 

The code below demonstrates how to get the value of output parameter.

// Create a ObjectParameter instance to retrieve output parameter from stored procedure 
ObjectParameter Output = new ObjectParameter("ID", typeof(Int32)); 
context.InsertPerson(Name, Description, Output);   

Console.Write("ID: {0}", Output.Value); 

Model backing a DB Context has changed; Consider Code First Migrations

[Origin]: http://stackoverflow.com/questions/14948205/model-backing-a-db-context-has-changed-consider-code-first-migrations

EF codefirst will look at your DbContext, and discover all the entity collections declared in it(and also look at entities related to those entities via navigation properties). It will then look at the database you gave it a connection string to, and make sure all of the tables there match the structure of your entities in model. If they do not match, then it cannot read/write to those tables. Anytime you create a new database, or if you change something about the entity class declarations, such as adding properties or changing data types, then it will detect that the model and the database are not in sync. By default it will simply give you the above error. Usually during development what you want to happen is for the database to be recreated(wiping any data) and generated again from your new model structure.

To do that, see “RecreateDatabaseIfModelChanges Feature” in this article:http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

You basically need to provide a database initializer that inherits from RecreateDatabaseIfModelChanges.

Once you go to production and no longer want to lose data, then you’d remove this initializer and instead use Database Migrations so that you can deploy changes without losing data.

shareedit

To solve this error write the the following code in Application_Start() Method in Global.asax.cs file

Database.SetInitializer<MyDbContext>(null);
shareedit