ORDINA BLOGT

Execute a Stored Procedure with Entity Framework Code First

Problem when executing stored procedures with output parameters using Entity Framework Code

  • 22 juni 2012

I ran into a problem when executing stored procedures with output parameters using Entity Framework Code First which lead to this exception:
 
The data reader has more than one field. Multiple fields are not valid for EDM primitive types.

Problem

 This is my stored procedure:
 
CREATE PROCEDURE sp_test (@name varchar(30) OUTPUT)
 AS
    SELECT @name = 'John Doe'
 GO
 
As you can see I want to return one value as varchar/string.

This is how you call a stored procedure with EF Code First:
 
var sqlParameter = new SqlParameter{
        ParameterName = "UniekKenmerkOUT",
        Value = -1,
        DbType = DbType.String,
        Size = 30,
        Direction = ParameterDirection.Output};
 
var result = Context.Database
       .SqlQuery<String>("exec  sp_test @name = @name OUT",
                         sqlParameter);
 
When I execute the code I get this exception:
 
The data reader has more than one field. Multiple fields are not valid for EDM primitive types.
 

Solution

Entity Framework expects a return value. The stored procedure is only returning an output parameter, and that is not enough. We can fix it this way:
 
CREATE PROCEDURE sp_test (@name varchar(30) OUTPUT)
 AS
    SELECT @name = 'John Doe'
    SELECT @name
 GO
 
The first SELECT statement in the stored procedure is not returning a value. It's actually assigning a value to the output parameter. That's why we need a second select to really return the value. I think it's preferable to write it this way:
 
CREATE PROCEDURE sp_test (@name varchar(30) OUTPUT)
 AS
    SET @name = 'John Doe'
    SELECT @name
 GO

Without output parameters

As you can see the output parameter in the example above is not necessary, but it returns the exact same value as the select statement. So, we can rewrite the stored procedure like this:
 
CREATE PROCEDURE sp_test
 AS
    SELECT 'John Doe'
 GO
 
And then we can execute this stored procedure with Entity Framework Code First:
 
var result = Context.Database.SqlQuery<String>("exec  sp_test);