Stored procedure returns int instead of result set

Question

I have a stored procedure that contains dynamic select. Something like this:

ALTER PROCEDURE [dbo].[usp_GetTestRecords]      --@p1 int = 0,      --@p2 int = 0     @groupId nvarchar(10) = 0 AS BEGIN     SET NOCOUNT ON;      DECLARE @query  NVARCHAR(max)      SET @query = 'SELECT * FROM CUSTOMERS WHERE Id = ' + @groupId     /* This actually contains a dynamic pivot select statement */      EXECUTE(@query); END 

In SSMS the stored procedure runs fine and shows result set.

In C# using Entity Framework it shows returning an int instead of IEnumerable?

private void LoadTestRecords() {     TestRecordsDBEntities dataContext = new TestRecordsDBEntities();     string id = ddlGroupId.SelectedValue;      List<TestRecord> list = dataContext.usp_GetTestRecords(id); //This part doesn't work returns int     GridView1.DataSource = list; } 

Generated function for usp_GetTestRecords

public virtual int usp_GetTestRecords(string groupId) {     var groupIdParameter = groupId != null ?         new ObjectParameter("groupId", groupId) :         new ObjectParameter("groupId", typeof(string));      return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_GetTestRecords", groupIdParameter); } 

Answer 1

I get this when I have a stored procedure that includes an "exec" call into a temporary table, such as:

insert into #codes (Code, ActionCodes, Description) exec TreatmentCodes_sps 0 

It appears that Entity Framework gets confused as to what should be returned by the procedure. The solution I've come across is to add this at the top of the sproc:

SET FMTONLY OFF 

After this, all is well.

Answer 2

I got the same problem, and found solution here

1. Move to your .edmx
2. At Model Browser Window/Function Imports find your procedure then double click it
3. Change the return type to you want
4. Save .edmx and check the return type again.

It should be what you need now.