vrijdag 22 juni 2012

Execute a Stored Procedure with Entity Framework Code First

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);