CODEWITHSUNDEEP
c#.netsql-serverentity-framework
Jyrkka
Jyrkka

Reputation: 536

The data reader has more than one field error while calling a procedure that returns an integer

I was trying to get status code of a stored procedure execution when encountered this error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

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

I have rewritten the procedure to do absolutely nothing but to return an integer value, this is how it looks now:

ALTER Procedure [dbo].[test]
(
    @i int,
    @a nvarchar(max),
    @b nvarchar(max),
    @c nvarchar(max),
    @d nvarchar(max)
)
As
Begin
    SET NOCOUNT ON

    return 1
End

But I still get the same error at run-time when reaching the procedure call at line:

this.Database.SqlQuery<int>("test @i, @a, @b, @c, @d", p_i, p_a, p_b, p_c, p_d).FirstOrDefault();

Is there anyway to figure out what these fields are, and where they are coming from? And how should I get the returned value?

I've tried to specify a tuple of two strings as the T just to look into these values, but with no success...

Updates:
Select 1 instead of return 1 makes the function usable, the only question remains what are these mysterious fields that are returned to the data reader?

Upvotes: 10

Views: 15793

Answers (4)

user6767235
user6767235

Reputation:

I came to a similar situation, I was calling a procedure from EF and the procedure had some print statements, EF was getting confused as it reads all the prints as an output. Make sure you comment all the prints before using it in EF.

Upvotes: 0

jjj
jjj

Reputation: 4997

Database.SqlQuery<T>() expects some kind of result set (e.g. SELECT). Under the hood, it uses DbCommand.ExecuteReader(), and when T is scalar, it expects the result set to have exactly one field -- but if the result set has more than one field, or if there are no fields, it throws the exception that you encountered.

The return value can be retrieved by passing a DbParameter to Database.SqlQuery<T>() and setting Direction = ParameterDirection.ReturnValue as seen in these examples:

FYI, if you just want the return value, but you don't want a result set, use ExecuteSqlCommand with a DbParameter.

Upvotes: 9

Ramesh Babu
Ramesh Babu

Reputation: 425

I think the answer given by Charlie is right. But you have to write the code as follows.

var result = this.Database.SqlQuery<int>(string.Format("exec test {0}, {1}, {2}, {3}, {4}", p_i, p_a, p_b, p_c, p_d)).FirstOrDefault();

Upvotes: 0

Scharly Ochoa
Scharly Ochoa

Reputation: 301

try this 

var result = this.Database.SqlQuery<int>("exec test {0}, {1}, {2}, {3}, {4}", p_i, p_a, p_b, p_c, p_d).FirstOrDefault();

Upvotes: 0

Related Questions