locked
No Return Type for a Stored Procedure in a Linq to SQL Data Layer RRS feed

  • Question

  • User-1168511948 posted

    Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng.

    I have a weird problem when using LINQ to SQL and Store Procedures with a return value...

    The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue...

    Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value...

    I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this...

    This is how part of my SP wich I used yesterday to test looks like (this worked yesterday, but not if I add it again to the DBML)

     

    INSERT INTO SidSektion (SidID, Rubrik, Bild, [Text], SortOrder) 
    VALUES (@SidID, @Rubrik, @Bild, @Text, @NewSort)
    
    RETURN SCOPE_IDENTITY()
    //Jens 
    Tuesday, March 11, 2008 6:17 AM

Answers

  • User-1168511948 posted

    Thanks for taking your time.

    The problem isn't that the stored procedure itself doesn't return a value, because it does indeed return the SCOPE_IDENTITY() when I execute it from like SQL Server Management Studio.

    The problem is that the LINQ Data Layer (.dbml) inside VS2008 doesn't recognize that the SP returns something and therefore I can't get the return value in my code. It worked the first time I tested this and then I used a ISingleResult<type> to collect the return value, but not the next time I tried to do the same thing.

    //Jens

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 12, 2008 12:14 PM

All replies

  • User404199702 posted

    hmmm..im not sure about this too..but im also creating stored procedures...you can return a value from a parameter/variable u've declared which holds the value you want to return...

    e.g

    declare @SCOPE_IDENTITY <type>

    <.....................................>

    return @SCOPE_IDENTITY

    or

    @SCOPE_IDENTITY <type> output

    <..........................................>

    set @SCOPE_IDENTITY =<value>

    return @SCOPE_IDENTITY

    hope this works.... :)

    Wednesday, March 12, 2008 5:51 AM
  • User-1168511948 posted

    Thanks for taking your time.

    The problem isn't that the stored procedure itself doesn't return a value, because it does indeed return the SCOPE_IDENTITY() when I execute it from like SQL Server Management Studio.

    The problem is that the LINQ Data Layer (.dbml) inside VS2008 doesn't recognize that the SP returns something and therefore I can't get the return value in my code. It worked the first time I tested this and then I used a ISingleResult<type> to collect the return value, but not the next time I tried to do the same thing.

    //Jens

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 12, 2008 12:14 PM
  • User-1632306213 posted

    I'm having the exact same problem.

     

    I have a sproc which returns 3 output types and a single row SELECT TOP 1 * FROM #tempTable created in the sproc...

    When I drag it onto the O/R mapper it says the Return Type is NONE which means I can't use a .ToList on the sproc when I call it in LINQ... help?

    Saturday, April 19, 2008 5:31 PM
  • User-1168511948 posted

    Well I still haven't find a good explanation to this but here's what we've figured out... It seems that if you create a SP wich doesn't return anything and add that to your DBML-file it somehow get's "flagged" as to never return anything. Even if you alter the procedure to actually return something, delete it from the DBML, and add it again, it still think it's the first version wich didn't have a return type.

    When I couldn't get a return type, I just copied my SP in a new query and created another one with a new name and added that, then it worked.

    One in my team have been starting to creating a VERY simple SP first that just does a basic select statement (that he know will return something), add that SP to the DBML to see that it in fact get "Return type: Auto generated" and after that he starts to modify his SP to do what it's supposed to do... Haven't tested this myself, but he claims it works... It feels pretty weird that it (sometimes) doesn't recognize the return type for the SP, so it MIGHT be a bug with the thing that generate the LINQ-code after something has been added to the DBML-file.

    //J

    Monday, April 21, 2008 1:56 PM
  • User-1632306213 posted

    In my case I found that if I use a #tempTable in the SPROC LINQ can't deal with that so it says no return type.

     

    As for regenerating it (as in you update the sproc on the server but LINQ doesn't "see it") I found I had to:

    1. Update the sproc on the server (of course)

    2. Delete the sproc's method from LINQ's O/R

    3. BUILD my application/project in VS 2008

    4. CLOSE my VS 2008 project

    5. Re-open VS 2008 project and add, then it seemed to work.

     

    However, I suspect that removing my connection to the server in the server explorer and re-adding it may also get the job done.

    Maybe all those steps aren't needed, but they seemed to work for me at least.  (Granted this assumes LINQ will "like" your sproc and it just isn't seeing that it was changed.  If the sproc has a #tempTable it seems to not want to work).

     

    I also read that you can override LINQ and manually enter a return type in LINQ's markup, but I don't have the details on that off hand.

     

    EDIT: Jaynard - Isn't Yoda's quote "Do or Do Not, There Is No Try"?

    Monday, April 21, 2008 2:52 PM
  • User-1168511948 posted

    Good to see you got it to work. I've also tried to re-build the application, restart vs and several other things like that. Sometimes it work, sometimes it don't... It bugs me not to know WHY this happens, and the easiest way is to see it as a "bug", but it's not a bug until official stated imo... I've got far too little experience to claiming this and that is a bug :P Already been "accusing" the EditIndex on position 0 in a default ListView edit to be a bug :P

    EDIT: Jaynard - Isn't Yoda's quote "Do or Do Not, There Is No Try"?

    It's actually Try not. Do. Or do not. There is no try. but I wanted a shorter version ;)

    Monday, April 21, 2008 3:04 PM
  • User1960614982 posted

     Hello,

    I am facing the same issue. I tried all the steps mentioned above but no luck. Have you found out any solution?

     

    Tuesday, September 9, 2008 2:23 PM
  • User-1632306213 posted

    Can you post the TSQL of your stored procedure?

     

    Does it use any #tempTables?

    Does it have more than one return set (SELECT blah more than once)?

    Those are both sure fire paths to pain and suffering :)

     Go ahead and post the code and I'll look through it to see if anything sticks out that we can adjust to work around it.

     

    (There ARE other options, you can adjust the code or create a... I forget the name as I haven't done it... Partial Method I think to expand on the one LINQ creates for your sproc that hard sets the return type as well, but I've never done that so I can't speak to it)

    Tuesday, September 9, 2008 3:09 PM
  • User1713683418 posted

    I was having this problem because my db changed and the stored procedure would no longer run.  Once I fixed the stored procedure, the Return Type would get set to (Auto-generated Type) in the designer.

    Wednesday, November 19, 2008 6:07 PM
  • User-181355576 posted

    I know this is an old post but I have the same problem.  I have a SP that I send info to the database to insert a user.  When it arrives it first checks to see if the user exist, if not it inserts and then sends back @UserResult = ID.  If the user exist it returns @UserResult = -1.  Now this works in database when I execute it from the database. 

    The linqtoSQL shows the SP (xxx as string, yyy as string, .... byref userid as integer?) as integer   {does the ? after the userid as integer mean anything?  it is an int type in the database}

    When I put the dim i as integer = db.SP(xxx, yyy, i)

    it returns a 0 everytime.  I have used the above before in a dataset (xsd) and it has worked.

    I have put in breakpoints and follow it through, it will insert fine but it does not want to return that value. I am doing this in MVC if that makes a difference.  So that is a learning curve all its own. 

    Wednesday, April 15, 2009 12:56 AM
  • User-1894044024 posted

    Dear *

    I found a very simple solution

    remove the * & write all fields name seperated by comma [,] 

    Monday, July 13, 2009 11:00 AM
  • User42143373 posted

    In my case, I had a @tableVariable (not a #tempTable) and experienced the same problem. It seems that VS2008 doesn't properly recognize both of them.

    After trying almost all tips listed above, I just hardcoded one dummy insert to the table before the final select for the time of generating the Linq classes. It didn't work unfortunately as well, but I tried then the solution mentioned by Jaynard and I can confirm that it works. My "VERY simple SP" was returning the same column names and types like the destination one.

    One in my team have been starting to creating a VERY simple SP first that just does a basic select statement (that he know will return something), add that SP to the DBML to see that it in fact get "Return type: Auto generated" and after that he starts to modify his SP to do what it's supposed to do... Haven't tested this myself, but he claims it works...

    Wednesday, August 25, 2010 6:53 AM
  • User-801401005 posted

     We are having the same problem.

    I find that if I remove the parameter from the stored procedure, compile it, refresh the objects in the server explored and drag the SP onto the surface that it now recognises the return type. It seems somehow related to the parameters (for us anyway). This sort of makes sense to me because the stored procedure needs the parameters to execute.

    This is really frustrating.

    Thursday, September 16, 2010 1:20 AM
  • User-1673377538 posted

    I have same problem, I try remove the connection, close visual studio, specify the column name rather then *, but all seems not work.


    I have Store Procedure with input parameter, no output parameter, declaring temporary table filtering data from temporary table and at the end Select statement to return the filtered data.


    can someone solve this or any alternate solution, can anyone specify the simple way to generate the type manually, anyone know which tool MVC use at the back-end to generate the Class as .net have XSD.exe and others.


    Thanks,

    Thursday, December 9, 2010 2:19 AM
  • User-1673377538 posted

    its work fine I have to change the temporary table to the table variable then to have new definitions 

    1. drop procudure

    2. delete procedure from dbml

    3. reopen the solution

    4. build solution again

    5. create the procedure in SQL server

    6. add the procedure to dbml


    http://stackoverflow.com/questions/927006/linq-to-sql-cant-modify-return-type-of-stored-procedure


    hope it would work fine,


    Thursday, December 9, 2010 3:13 AM