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

Question

I am trying to call a stored procedure from Entity Framework in the following way:

Context.Database.SqlQuery<int>(sqlSP, params).FirstOrDefault();

but I get this error:

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

With LinqPad you can see that the stored procedure is working

The generated code from LinqPad for the same call in SQL is the following

-- Region Parameters
DECLARE @RETURN_VALUE Int
DECLARE @contenttype VarChar(50) = ''
DECLARE @image VarBinary(1000) = null
DECLARE @applicationid Int = 81725
DECLARE @statusid Int = 10
DECLARE @notes VarChar(1000) = ''
DECLARE @requestuserid Int = 59
DECLARE @assigneduserid Int = 655
DECLARE @parentid Int = 0
DECLARE @eventid Int = 0
DECLARE @discipline Int = 5
DECLARE @replyby DateTime = '2017-09-14 16:22:40.082'
DECLARE @workitemid Int = 81725
DECLARE @messagetype Int = 2
DECLARE @inspectionid Int = 6081
DECLARE @floor SmallInt = 3
-- EndRegion
exec @RETURN_VALUE = [dbo].[usp_InsertInspectionEventPublic] @contenttype, @image, @applicationid, @statusid, @notes, @requestuserid, @assigneduserid, @parentid, @eventid, @discipline, @replyby, @workitemid, @messagetype, @inspectionid, @floor

The stored procedure always returns an id in the following way:

select @id = SCOPE_IDENTITY() from TEMPTABLE
Return @id

I tried this too:

var returnCode = new SqlParameter("@ReturnCode", SqlDbType.Int);
returnCode.Direction = ParameterDirection.ReturnValue;

var sql = "exec @ReturnCode =dbo.usp_insertinspectioneventpublic @contenttype, @image, @applicationid, @statusid, @notes, @requestuserid, @assigneduserid, @parentid, @eventid, @discipline, @replyby, @workitemid, @messagetype, @inspectionid, @floor";
var data = Context.Database.SqlQuery<int>(sql, returnCode, inParameters);
var res = data.FirstOrDefault();
return res;

But I am getting another error with that approach: When executing a command, paramerters must be exclusively database parameters or values. Which can be the problem here?

Return value is different than a result set. The SqlQuery is returning the result set. I'm not that familiar, but you may be able to access the parameter with direction ReturnValue from params (which is a keyword so don't use it) — Crowcoder
– Crowcoder, Commented Sep 14, 2017 at 21:10

David Browne - Microsoft · Accepted Answer · 2017-09-14 21:11:43Z

1

SqlQuery<T>() is looking for a resultset, not an output parameter.

Either map an output parameter and run the batch with ExecuteSqlCommand(sql,params), or write the sqlSP batch like this:

declare @RETURN_VALUE INT;
exec @RETURN_VALUE = [dbo].[usp_InsertInspectionEventPublic] @contenttype, @image, @applicationid, @statusid, @notes, @requestuserid, @assigneduserid, @parentid, @eventid, @discipline, @replyby, @workitemid, @messagetype, @inspectionid, @floor;
SELECT @RETURN_VALUE RETURN_VALUE;

So that the return value comes back in a resultset.

answered Sep 14, 2017 at 21:11

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Heinrich Over a year ago

Actually the store procedure doesn't have an output parameter declared, so I can't use something like this [dbo].[usp_InsertInspectionEventPublic] OUT myOutputParameter... The linkpad is generating the sql in that way when it calls the stored procedure. Inside the stored procedure there is a line that make the same thing that you suggest Return id after assigning the value to id. Sorry for not including the "at" on the variables in the comment, seems that this input are doesn't allow it

David Browne - Microsoft Over a year ago

It's not a stored procedure output parameter, it's a return value. That's why it's exec @RETURN_VALUE = procname ... instead of exec procname @RETURN_VALUE. . . return @id outputs the data as a return value select @id id returns it as a resultset, like SqlQuery<T> expects.

Heinrich Over a year ago

I am confuse, do you mean select id = SCOPE_IDENTITY() from TEMPTABLE Return id returns a resultset?

David Browne - Microsoft Over a year ago

Yes stand-alone SELECT in a stored procedure sends a resultset to the client. You don't need a temp table . Just select scope_identity() id will return a single-row, single-column resultset.

Heinrich Over a year ago

Can we continue the discussion here? chat.stackoverflow.com/rooms/154544/…

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related