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

How can I resolve this error: Loading the include file ‘EF.Utility.CS.ttinclude’ returned a null or empty string

[Originally posted by]: http://stackoverflow.com/questions/14999633/how-can-i-resolve-this-error-loading-the-include-file-ef-utility-cs-ttinclude

How can I resolve these errors:

  1. Loading the include file ‘EF.Utility.CS.ttinclude’ returned a null or empty string.
  2. Value does not fall within the expected range.

Tools Used: VS 2012, Entity Framework 5.0.0, T4MVCExtensions 3.5.0, .NET Framework 4.5

I’ve been using EF and T4 templates in a project within a large MVC solution without incident. Now, when I right-click on the edmx diagram, EF responds with the message: Value does not fall within the expected range. When I compile the EF project it responds with these messages:

  1. Loading the include file ‘EF.Utility.CS.ttinclude’ returned a null or empty string. The transformation will not be run. C:\Projects\NSAForms\NSAForms\NSAFormsEDM\NSAFormsEntities.tt
  2. Failed to resolve include text for file:C:\Projects\NSAForms\NSAForms\NSAFormsEDM\EF.Utility.CS.ttinclude C:\Projects\NSAForms\NSAForms\NSAFormsEDM\NSAFormsEntities.tt

Every table in the database has a primary key. I’m struck by how suddenly this issue appeared. Does anyone have some suggestions how to resolve this issue?

Thanks in advance,

Arnold

shareedit

I had very similar issue and I tried with VS2012 and VS2013.

After reinstalling Entity Framework 6 Tools for Visual Studio 2012 http://www.microsoft.com/en-us/download/confirmation.aspx?id=40762 every thing went back to normal.

shareedit