Entity Framework shows error when called stored procedure

Question

In my project EF calls a stored procedure which is shown below. It returns either 1 or scope identity.

On EF function imports, the stored procedure is listed with a return type of decimal.

When the stored procedure returns scope identity, everything is ok.

But when if condition of sp satisfies, ef throws error as

The data reader returned by the store data provider does not have enough columns for the query requested.

Pls help..

This is my stored procedure:

@VendorId int,
    @ueeareaCode varchar(3),
    @TuPrfxNo varchar(3),
    @jeeSfxNo varchar(4),
    @Tjode varchar(3),
    @uxNo varchar(3),
    @TyufxNo varchar(4),
    @Iyuy bit


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

     IF EXISTS (Select dfen_id
                    from dbo.efe_phfedwn_eflwn
                    where
                    [yu] = @Tyuode and
                    [uy] = @TuyxNo and
                    [yuno] = @Tuo)
                    return 1
    ELSE
        Begin
            INSERT INTO dbo.yu 
                         ....................                       
            Select Scope_Identity()
    End
END

I'm not very experienced with EF but is it possible that because you're returning 1 or selecting the Scope_Identity() it's having difficulties? Could you try changing Select Scope_Identity() to RETURN Scope_Identity() and see if that makes a difference? — Sean Airey
– Sean Airey, Commented May 30, 2013 at 9:36
If that doesn't solve it (I don't have high hopes), it would be helpful for other people to see the code that EF is using to get the result of this SP, the Model code or whatever it is — Sean Airey
– Sean Airey, Commented May 30, 2013 at 9:39
no , I have tested using return .But now it shows the same error in both cases. Later, it was showing only when if condition satisfies. — Raju S Nair
– Raju S Nair, Commented May 30, 2013 at 9:46
Ok so perhaps the solution is actually the other way around and you just use SELECT 1 and SELECT Scope_Identity()? — Sean Airey
– Sean Airey, Commented May 30, 2013 at 9:50
when used select, else condition is ok, but on if condition, it cannot convert int32 to decimal . pls help.My Ef output is as decimal. — Raju S Nair
– Raju S Nair, Commented May 30, 2013 at 9:55

Sean Airey · Accepted Answer · 2013-05-30 10:10:42Z

3

The error tells us that EF is expecting a result set and when we use RETURN we don't get a result set. Your error means that the stored procedure is returning an integer but EF is expecting a decimal, so we just CAST the selected values to a decimal.

So modify the SQL so that we SELECT instead of RETURN, like so (not forgetting to use CAST):

 IF EXISTS (Select cntct_ctr_phn_ln_id
                from dbo.cntct_ctr_phn_ln
                where
                [toll_free_phn_area_cd] = @TollfreeareaCode and
                [toll_free_phn_prfx_no] = @TollfreePrfxNo and
                [toll_free_phn_sfx_no] = @TollfreeSfxNo)
                SELECT CAST(1 AS decimal)

Then also CAST the result of SCOPE_IDENTITY() to a decimal:

SELECT CAST(SCOPE_IDENTITY() AS decimal)

answered May 30, 2013 at 10:10

Sean Airey

6,3922 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Raju S Nair Over a year ago

If Sp returns both decimal and integer, without doing cast at sp level , is any other way to handle at Ef level , so that Ef can accept both and can use or convert at the code level.

Sean Airey Over a year ago

I honestly couldn't tell you. You may be able to override EF's default logic for building the object but I'm afraid I've not got that far in my understanding.

Le-roy Staines Over a year ago

But what if we NEED the return statement rather than a select statement to return out ID's in Sprocs?

Sean Airey Over a year ago

I'm not entirely sure, but this question seems to suggest that you should use EXEC to select the return value of the stored procedure into an output parameter and then select the value of the parameter once the query has executed.

Suncat2000 Over a year ago

@Le-royStaines Stored procedures provide data to the EF reader through a SELECT resultset, not a returned value. You don't need a RETURN because EF isn't designed to access it. Only another stored procedure can use the returned value from a stored procedure.

Collectives™ on Stack Overflow

Entity Framework shows error when called stored procedure

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related