none
LINQ to SQL not handling sp_executeSQL or INTO #TEMP RRS feed

  • Question

  • Hello

    I have been trying to map a SPROC containing a SQL string that I concatenate together. It is then executed like this:

    EXEC sp_executesql @sql

    When I drag this to the LINQ/SQL designer, the return type generated is INT. It is not recognizing it as returning a table.

    So I then changed the SQL command to execute without using sp_executesql. The query is a SELECT into a #TEMP cursor. This did not work either. The designer could not recognize the #TEMP table either. When I changed it to not to use a temp table, then it worked.

    Sounds like this might be related to a similiar problem documented here:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2950555&SiteID=1

    Is my description been repeated? Any work arounds?

    Thanks
    Harold





    Tuesday, March 18, 2008 1:26 AM

Answers

  • LINQ to SQL can't figure out the resulting row types of stored procedures that use those techniques; the information is not available from the service published by the server.  To work around the problem, you'll have to define the mapping to the stored procedure manually, either in the DBML file directly or via methods & mapping attributes that you add to the DataContext in code (via partial classes).

     

     

     

    Tuesday, March 18, 2008 9:51 PM
    Moderator

All replies

  • LINQ to SQL can't figure out the resulting row types of stored procedures that use those techniques; the information is not available from the service published by the server.  To work around the problem, you'll have to define the mapping to the stored procedure manually, either in the DBML file directly or via methods & mapping attributes that you add to the DataContext in code (via partial classes).

     

     

     

    Tuesday, March 18, 2008 9:51 PM
    Moderator
  • Do you have an example of how to do this?

     

    Thanks.

     

    David

     

     

    Wednesday, October 29, 2008 12:38 AM
  • If anybody at Microsoft reads this forum - please have the people who wrote the LINQ tools go and visit with the people who wrote the Table Adapter class tools.  Adding a stored procedure to a DataSet would generate the correct, strongly-typed output of the stored procedure that would work in this person's situation (above) and many other situations where LINQ fails in this regard.  

    I really like LINQ - but this is a gaping hole in the implementation of LINQ and it makes very little sense since the code to derive the strongly-typed output of a stored procedure already exists for DataSets/TableAdapters.

    Sean
    Friday, March 13, 2009 5:51 AM
  • LINQ to SQL actually relies on the SQL Client to tell it what the stored procedure will return and the limitation of not handling temporary tables and dynamic SQL is part of that.

    Would be interesting to find out what the people behind Table Adapter did though, I agree.

    [)amien
    Friday, March 13, 2009 7:25 AM
    Moderator
  • Just check this link

    http://devio.wordpress.com/2009/03/26/select-from-temp-table-in-stored-procedure-with-linq-to-sql/

     

    Wednesday, April 7, 2010 8:45 AM