Error on calling stored procedure with output param in Entity Framework code-first

Question

I am getting the following error while calling ExecuteStoreQuery from Entity Framework (using a code-first approach).

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

But my insert/update operation reflects in database.

Any idea of this issue?

This is the code am tring from c#

long Id=0;

SqlParameter paramoutput = new SqlParameter()
{
   ParameterName = "Result",
   Value = "",
   SqlDbType = System.Data.SqlDbType.BigInt,
   Size = 100,
   Direction = System.Data.ParameterDirection.Output
};

var mydata = (dataContext as IObjectContextAdapter).ObjectContext
             .ExecuteStoreQuery<long>("exec Save @MarkupId, @ApiId, @MainBranchId, @Title, @Markup, @IsPercentage, @IsDomestic, @CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy, @IsActive, @Result out",
                 new SqlParameter("@MarkupId", generalMarkupModel.Id),
                 new SqlParameter("@ApiId", generalMarkupModel.ApiId),
                 new SqlParameter("@MainBranchId", generalMarkupModel.MainBranchId),
                 new SqlParameter("@Title", generalMarkupModel.Title),
                 new SqlParameter("@Markup", generalMarkupModel.Markup),
                 new SqlParameter("@IsPercentage", generalMarkupModel.IsPercentage),
                 new SqlParameter("@IsDomestic", generalMarkupModel.IsDomestic),
                 new SqlParameter("@IsActive", generalMarkupModel.IsActive),
                 new SqlParameter("@CreatedOn", DateTime.Now),
                 new SqlParameter("@CreatedBy", "ajc"),
                 new SqlParameter("@ModifiedOn", DateTime.Now),
                 new SqlParameter("@ModifiedBy", "ajm"),
                 paramoutput);

Id = Convert.ToInt64(paramoutput.Value.ToString());
return Id;

The stored procedure performs an insert operation and set the @@identity to the output parameter.

Here is my stored procedure:

ALTER PROCEDURE  mypocedurename-here
 @MarkupId bigint,          
 @ApiId int,           
 @MainBranchId int,            
 @Title varchar(50),                    
 @Markup float ,  
 @IsPercentage bit,  
 @IsDomestic bit,
 @CreatedOn datetime, 
 @CreatedBy varchar(50),
 @ModifiedOn datetime, 
 @ModifiedBy varchar(50), 
 @IsActive bit,
 @Result bigint OUTPUT              

AS            
BEGIN            

  INSERT INTO GeneralMarkups            
  ( 
    ApiId,            
   MainBranchId, 
   Title,           
   Markup,            
   IsPercentage ,          
   IsDomestic ,  
   IsActive ,               
   CreatedOn,   
   CreatedBy,  
   ModifiedOn,
   ModifiedBy           
  )            
  VALUES            
  (
    @ApiId,            
   @MainBranchId,            
   @Title,            
   @Markup ,          
   @IsPercentage ,  
   @IsDomestic,
   @IsActive ,          
   @CreatedOn,
   @CreatedBy,  
   @ModifiedOn,
   @ModifiedBy          

  )
  select @Result=@@IDENTITY    

END

POST CODE! I have no idea what you're talking about. You need to post the code that is giving the error and we might be able to help you. — Zane
I would recommend to use SCOPE_IDENTITY() instead of anything else to grab the newly inserted identity value. See this blog post for an explanation as to WHY — marc_s
It's a stored procedure - as in a procedure which is stored inside your SQL Server. It's not a "store" procedure (has nothing to do with a "store"), nor is it a "storeprocedure" (which is a word that doesn't even exist in English) — marc_s

amiry jd amiry jd · Accepted Answer · 2016-08-23T13:37:07

First of all, your procedure is not selecting any result. So you need to select something (since you specified a return-result-set of type long). So, change the proc's last line:

-- instead of select @Result=@@IDENTITY 
-- use these lines:
SET @Result = @@IDENTITY
SELECT @Result Result

Now, you can get the selected value by your procedure call. Means when you execute your method, you will get the new id from result (as your mydata parameter - which should be iterated).

But there's still a bug. You won't be able to get this line to work:

Id = Convert.ToInt64(paramoutput.Value.ToString());

Because your output parameter has not a value (it's null), unless you iterate the result of query. Means paramoutput.Value would be null until you call one iterator-method on the query. So:

SqlParameter paramoutput = new SqlParameter()
{
   ParameterName = "Result",
   // Value = "", no need
   SqlDbType = System.Data.SqlDbType.BigInt,
   // Size = 100, no need
   Direction = System.Data.ParameterDirection.Output
};

var mydata = (dataContext as IObjectContextAdapter).ObjectContext
             .ExecuteStoreQuery<long>("exec Save @MarkupId, @ApiId, @MainBranchId, @Title, @Markup, @IsPercentage, @IsDomestic, @CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy, @IsActive, @Result out",
                 new SqlParameter("@MarkupId", generalMarkupModel.Id),
                 new SqlParameter("@ApiId", generalMarkupModel.ApiId),
                 new SqlParameter("@MainBranchId", generalMarkupModel.MainBranchId),
                 new SqlParameter("@Title", generalMarkupModel.Title),
                 new SqlParameter("@Markup", generalMarkupModel.Markup),
                 new SqlParameter("@IsPercentage", generalMarkupModel.IsPercentage),
                 new SqlParameter("@IsDomestic", generalMarkupModel.IsDomestic),
                 new SqlParameter("@IsActive", generalMarkupModel.IsActive),
                 new SqlParameter("@CreatedOn", DateTime.Now),
                 new SqlParameter("@CreatedBy", "ajc"),
                 new SqlParameter("@ModifiedOn", DateTime.Now),
                 new SqlParameter("@ModifiedBy", "ajm"),
                 paramoutput);

// here, the mydata, is of type ObjectResult<long> which you should iterate it to get underlying IDataReader iterated and closed. Then you can use output parameters. So, add this line:
var returnedId = mydata.FirstOrDefault();
// now, the output parameter is available:
Id = Convert.ToInt64(paramoutput.Value.ToString());

Please consider the comments I have added to code (and new line).

After all, why are you passing an output parameter and selecting same result too? One of those should do the job and the other one is redundant. Isn't it? Or there is something I can't see?

Error on calling stored procedure with output param in Entity Framework code-first

1 Answers