Why is Entity Framework calling my stored procedure but returning an incorrect value?

asp.net c# entity-framework sql-server

Question

I have a stored procedure that simply returns the total number of records divided by whatever value is passed in. This is to aid in pagination on a website.

However, I am using the entity framework to bind to that stored procedure and it's returning -1 for all calls to it. When I interrogate the stored procedure using SQL Management Studio, it comes back with the correct value.

My stored procedure looks like this:

CREATE PROCEDURE [dbo].[GetAuditRecordPageCount]
@Count INTEGER
AS
RETURN ((SELECT COUNT(Id) FROM AuditRecords) / @Count) + 1

And my call to the entity framework in C# is this:

int pageCount;
using (Entities entities = new Entities())
{
    pageCount = entities.GetAuditRecordPageCount(count);
}

Am I correct in writing the C# code this way?

As per a request in the comments, the SQL generated by EF is:

exec [dbo].[GetAuditRecordPageCount] @Count=100
1
1
1/2/2013 11:21:05 PM

Accepted Answer

Did you tried that? http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values

I think your procedure will look like this:

CREATE PROCEDURE [dbo].[GetAuditRecordPageCount]
@Count INTEGER
AS
declare @retVal int
set @retVal = ((SELECT COUNT(Id) FROM AuditRecords) / @Count) + 1
select @retVal

And in the c# code:

int? pageCount;
using (Entities entities = new Entities())
{
   pageCount = entities.GetAuditRecordPageCount(count).SingleOrDefault();

   if (pageCount.HasValue)
   {
      //do something here
   }
   else
   {

   }
}

Don't forget to put "Scalars: Int32" in edit function Import screen.

3
1/3/2013 12:08:11 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow