2

Quick update, I went through line by line, seeing the "function evaluation requires all threads to run".

Trying to execute a stored procedure using Entity Framework in C#. I have been successful in doing this for a SELECT statement inside a stored procedure, but not so much with an UPDATE.

I have verified the following:

  • The stored procedure works in SQL Server with the values I am feeding it manually
  • The stored procedure expects bigint as parameters, and thus the input is sent as INT64.

The method I assume the EF built around the stored procedure, gets through all parts except its return statement, where I get a rather general invalidexception error.

Any thoughts on this? I can provide a bit of the stored procedure and the line on the method:

 return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("SP_HERE", int64, int64, int64, int64);
    }

Here is the stored procedure:

CREATE PROCEDURE some_SP
    @var1 bigint,
    @var2 bigint,
    @var3 bigint,
    @var4 bigint 
AS
BEGIN
    --SET NOCOUNT ON;

Then various update statements, this I pasted was the original create.

Any tips off the top of your heads would be helpful. Not sure what is causing this, and digging into the exception doesn't seem to yield anything helpful.

Thanks guys!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MZawg
  • 308
  • 4
  • 15
  • Possible duplicate of https://stackoverflow.com/questions/39062972/execute-stored-procedure-using-entity-framework and https://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first – Richard Nov 27 '18 at 17:43
  • @Richard I wish :(. If you find anything referencing my update, though, let me know :). – MZawg Nov 27 '18 at 17:46
  • @MikeCMR - Are you returning anything from your stored proc? ExecuteFunction returns an Int32....per MSDN documentation, InvalidOperationException is thrown when there is a type mismatch between the reader and the function. Wondering if you are returning something other than the count of rows that is updated? Could that be what is causing the exception? – user1011627 Nov 27 '18 at 17:49
  • @user1011627 good suggestion, but nope. Not returning anything. It can return an int it looks like, maybe rows affected? But that is not my goal. Just simply object db.stored_procedure(values), using it like a method. – MZawg Nov 27 '18 at 17:52
  • 1
    Maybe try and return @@ROWCOUNT and see if it works then...at least from a troubleshooting standpoint. – user1011627 Nov 27 '18 at 17:53
  • @user1011627 nope :(. Thanks again though for the suggestion. I do think it has to do with this: function evaluation requires all threads to run – MZawg Nov 27 '18 at 17:58
  • @user1011627 you SOB :D. I refreshed it after adding the rows count lol.. It works !! Add it as an answer and I will pick it. My only question is, I know you mentioned the documentation on MSFT, but is this true for all actions I wish to make using EF and stored procedures? I am required to return something whether it be row count or data? – MZawg Nov 27 '18 at 18:06
  • Cool...glad you got it working. Will add the answer now. As for the question of returns...I'm not entirely sure. It looked like the default return was always 0 so I would have guessed it would have worked without the return at all based on the docs but I figured it made sense to test it out to see. – user1011627 Nov 27 '18 at 19:26
  • Just realized your question was also about returning data....in the event you are asking about returning result sets, it depends on the version of EF you are using. Stored procs will create functions in EF and will allow you to call them directly without using ExecuteFunction. Also, take a look at the links that Richard sent....they show a way to use SqlQuery function that allows you to serialize the results directly to objects. My first response was only in relation to create, update and delete operations. – user1011627 Nov 27 '18 at 19:48

1 Answers1

1

Return @@ROWCOUNT from the stored procedure. Most likely the issue you were experiencing is related to the mismatch between the type returned from the stored proc and the return value expected by the ExecuteFunction method.

user1011627
  • 1,741
  • 1
  • 17
  • 25
  • Thanks again! I am going to dig around a bit to see why the @rows truly solved the issue, and update if I find anything. But glad its working. – MZawg Nov 27 '18 at 20:09
  • YW...please do....I dug a little to see if I could find out but never stumbled onto a real answer....would definitely like to know though. – user1011627 Nov 27 '18 at 20:41