none
No return type for Store Procedure in LINQ to SQL RRS feed

  • Question

  • When I map the store procedure to data function using LINQ to SQL, some times the tool can generate the Return Type(Auto-generated Type), but sometimes the Return Type is none even the same store procedure.
    I do not know the reasons.
    I'd appreciated if some one give me some advices.
    Saturday, November 28, 2009 7:44 AM

Answers

  • In most simple stored procedures the LINQ to SQL designer can infer the shape of the results and use that to create a strongly typed result. But in some cases LINQ to SQL can't infer this information. The most common case seems to be when using temp tables within the procedure.

    If this is an issue you can always go into the DBML file and add the result shape manually.
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Sunday, November 29, 2009 9:48 PM
    Answerer

All replies

  • In most simple stored procedures the LINQ to SQL designer can infer the shape of the results and use that to create a strongly typed result. But in some cases LINQ to SQL can't infer this information. The most common case seems to be when using temp tables within the procedure.

    If this is an issue you can always go into the DBML file and add the result shape manually.
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Sunday, November 29, 2009 9:48 PM
    Answerer
  • Just like you said, I use the temp table in store procedure.
    Thanks for your help.

    Monday, November 30, 2009 12:48 AM
  • Hi,

    If you want, you can modify your stored procedure to use Table Variables instead of Temporary Tables . I believe LINQ designer will be able to infer your result set for table variables.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Wednesday, December 2, 2009 10:49 AM
  • I'm having the same problem with the LINQ to SQL designer generating a stored procedure wrapper that returns an int instead of an actual result set.

    However, in my case the stored procedure just selects from a permanent table and the code is really very simple.  Can someone tell me why this is happening with this code?


    ALTER PROCEDURE [dbo].[asp_get_standard_pricing_packages_by_business_id]
        @BusinessID    BusinessID
    AS
        SELECT * from tbl_StandardPricingPackage where BusinessID = @BusinessID
    GO

    Here's the table:

    CREATE TABLE [dbo].[tbl_StandardPricingPackage]
    (
        [PackageID] [dbo].[PricingPackageID] IDENTITY(1,1) NOT NULL,
        [Description] [varchar](255) NOT NULL,
        [Price] [money] NOT NULL,
        [BusinessID] [dbo].[BusinessID] NOT NULL,
     CONSTRAINT [PK_tbl_StandardPricingPackage] PRIMARY KEY CLUSTERED
    )
    GO
    Thursday, December 10, 2009 12:11 PM
  • I'm having the same problem with the LINQ to SQL designer generating a stored procedure wrapper that returns an int instead of an actual result set.

    However, in my case the stored procedure just selects from a permanent table and the code is really very simple.  Can someone tell me why this is happening with this code?


    ALTER PROCEDURE [dbo].[asp_get_standard_pricing_packages_by_business_id]
        @BusinessID    BusinessID
    AS
        SELECT * from tbl_StandardPricingPackage where BusinessID = @BusinessID
    GO

    Here's the table:

    CREATE TABLE [dbo].[tbl_StandardPricingPackage]
    (
        [PackageID] [dbo].[PricingPackageID] IDENTITY(1,1) NOT NULL,
        [Description] [varchar](255) NOT NULL,
        [Price] [money] NOT NULL,
        [BusinessID] [dbo].[BusinessID] NOT NULL,
     CONSTRAINT [PK_tbl_StandardPricingPackage] PRIMARY KEY CLUSTERED
    )
    GO

    Most likely because the input parameter uses an alias type rather than one of the built-in system types. The designer sometimes don't play well with alias types...


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Thursday, December 10, 2009 12:19 PM
    Answerer
  • My way is better. See: http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/. Basically I use FMTONLY to force the resultset to be returned to LINQ to SQL.
    Monday, June 7, 2010 3:16 AM
  • This doesn't work either.
    Wednesday, September 29, 2010 12:59 PM
  • Hi All,

    As per me; David DeWinter answer was right. That means issue is with temp table.
    You need to remove temp table from your store proc and use Table variable.
    It should be something like this:-

    CREATE TABLE TempWork(
    WorkId INT,
    WorkDetails Varchar(50)
    )
    
    You can see detail approach in my article:-
    http://sharepoint.infoyen.com/2012/09/02/table-variables-ms-sql/

    Thanks & Regards,
    Avinash
    My Technical Blog:- http://sharepoint.infoyen.com/




    • Edited by Avinash Dad Wednesday, September 5, 2012 8:32 AM
    Tuesday, September 4, 2012 2:44 PM