How to get dynamic SQL result from SP in entity framework?

Question

Suppose I have following SP to run a dynamic sql ALTER PROCEDURE [dbo].[MySP] AS BEGIN

declare @sql varchar(4000)
select @sql = 'select cnt = count(*) from Mytable ..... ';
exec (@sql)

END

then in edmx, I add the sp and import function for this sp. the return type is scalars int32.

then I want to use this function in code like:

int? result = context.MySP();

I got error said "cannot implicitly convert type System.Data.Objects.ObjectResults to int?"

If use

   var result = context.MySP();

then Single() cann't be applied to context.MySP().

How to get the result for this case?

Answer 1

You may have already resolved this, but ...

I had done the same thing; my procedure was returning an integer so I selected Scalars and Int32, and it was returning System.Data.Objects.ObjectResult

Of course, the problem is the window states "Returns a Collection Of" (emphasis mine).

Durr.

Select None instead of Scalars and the appropriate result (int) will be returned.