CODEWITHSUNDEEP
sql-server-2008.net-4.0entity-framework-4
Craig
Craig

Reputation: 18694

Stored Procedures in EF4.4

We have had to downgrade a project which used EF5, to EF4, and are battling to get a stored procedure into the EF model.

The procedure returns a table:

  -- A table to hold the results
  DECLARE @Result TABLE
  (
      ActionType    CHAR(1),
      ChangedBy     VARCHAR(50),
      ChangedDate   DATETIME,
      FieldName     VARCHAR(150),
      OriginalValue VARCHAR(100),
      NewValue VARCHAR(100),
      ForeignKeyName VARCHAR(50),
      ForeignKeyValue VARCHAR(15)
  )

and then adds records, and returns:

  -- Return the result
  SELECT * FROM @Result

However, adding a proc seems very different. The stored procedures are not showing up in our Entity Framework context.

So we follow the example shown here: http://msdn.microsoft.com/en-us/library/bb896231.aspx

However, we select the return type as 'Complex', but when we click 'Get Column Information', we're greeted with the following error:

An exception of type “Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException” occurred while attempting to get columns information. The exception message is: ExecuteScalar requires an open and available Connection. The connection’s current state is closed. The inner exception caught was of type ‘System.InvalidOperationException’, with this error message: ‘ExecuteScalar requires an open and available Connection. The connection’s current state is closed.’.

Can anyone assist with this error, or maybe explain a better way to reference stored procedures in EF4.4?

Upvotes: 0

Views: 72

Answers (1)

Craig
Craig

Reputation: 18694

Solved! Don't use SELECT *!

-- Return the result
  SELECT       
      ActionType,
      ChangedBy,
      ChangedDate,
      FieldName,
      OriginalValue,
      NewValue,
      ForeignKeyName,
      ForeignKeyValue
  FROM @Result

Upvotes: 1

Related Questions