Cannot implicitly convert type 'System.Data.Linq.ISingleResult<CustomeProcedureName> to 'int'

Question

Sorry for this simple question .

I have a Stored Procedure that return an int value , I'm trying to call this sp from my asp.net linq to sql project .

int currentRating = db.sproc_GetAverageByPageId(pageId);

But i get this error :

Cannot implicitly convert type `'System.Data.Linq.ISingleResult<PsychoDataLayer.sproc_GetAverageByPageId> to 'int' .`

Edit 1 The solution that friends implied didn't work . All the time it return 0 For more information i put my stored procedure here :

ALTER procedure [dbo].[sproc_GetAverageByPageId](
@PageId int )
as 
select (select sum(score) from votes where pageId = @PageId)/(select count(*) from votes where pageId=@PageId)

Answer 1

This is actually returning an ISingleResult

int currentRating = (int) db.sproc_GetAverageByPageId(pageId).ReturnValue;

Change your sp to :

ALTER procedure [dbo].[sproc_GetAverageByPageId](
@PageId int )
as 
  return  (select sum(score) from votes where pageId = @PageId)/(select count(*) from votes where pageId=@PageId)

Answer 2

You should inspect the ReturnValue property.

Perhaps the following works better?

int currentRating = (int)db.sproc_GetAverageByPageId(pageId).ReturnValue;

Update: since your stored proc returns a resultset instead of using a return statement the actual data will be available as an element in the enumerable returned by db.sproc_GetAverageByPageId(pageId). If you inspect the ISingleResult<T> type, you'll see that it inherits IEnumerable<T> which indicates that you can enumerate the object to get to the data, each element being of type T.

Since the sproc does a SELECT SUM(*) ... we can count on the resultset to always contain one row. Thus, the following code will give you the first (and only) element in the collection:

var sumRow = db.sproc_GetAverageByPageId(pageId).Single();

Now, the type of sumRow will be T from the interface definition, which in your case is PsychoDataLayer.sproc_GetAverageByPageId. This type hopefully contains a property that contains the actual value you are after.

Perhaps you can share with us the layout of the PsychoDataLayer.sproc_GetAverageByPageId type?

Answer 3

Looks like you're actually after the ReturnValue. You may need to cast it to System.Data.Linq.ISingleResult if it isn't already, then cast ReturnValueto int.