Multiple fields are not valid for EDM primitive types.

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("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("exec sp_test);