Stored procedure returns int instead of result set
c#, entity-framework, sql
Solution
Entity Framework can't tell what your stored procedure is returning. I've had success creating a table variable that mirrors the data from your SELECT statement. Just insert into the table variable then do a select from that table variable. EF should pick it up.
Problem
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); } ```