none
LINQ to SQL uses CONVERT when calling my Stored Procedure RRS feed

  • Question

  • I have a stored procedure which I like to use with LINQ to SQL. I got it added to dbml just fine and manage to call it but then the weird stuff happens. I'm getting the "Cannot find either column "MySchema" or the user-defined function or aggregate "MySchema.pi_MyStoredProcedure", or the name is ambiguous." -error. Tried to figure out why this is happening since I can execute the proc just fine from SSMS. SQL Profiler managed to catch the query and it was like this:

    exec sp_executesql N'SELECT CONVERT(Int,[MySchema].[pi_MyStoredProcedure](@p0)) AS [value]',N'@p0 nvarchar(4000)',@p0=N'ParameterValue'

    I get the same "cannot find..." -error from SSMS when I try to execute this. I suspect this may have something to do with the return type of my stored proc. I had to set it by hand in the .dbml XML code. That looks like this:

    <Function Name="MySchema.pi_MyStoredProcedure" Method="pi_MyStoredProcedure" IsComposable="true">
        <Parameter Name="MyParameter" Type="System.String" DbType="NVarChar(512)" />
        <Return Type="System.Int32" />
      </Function>

    What am I doing wrong here? Every online tutorial I've managed to find tell me to do this but obviously there's something else I'm missing? What is it? 

    Sunday, September 2, 2012 12:02 PM

All replies

  • Hi Antti;

    I have tried building a query that returns a single integer when calling the stored procedure through Linq to SQL when the stored procedure returns a collection of rows and also returns a single integer value and also a stored procedure that just returns a single integer value without issue.

    I created two stored procedures to cover the two above test. as follows:

    -- Returns All employees and an integer value
    CREATE PROCEDURE [dbo].[EmployeesAndCount] As
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	declare @e1 int
    
    	SELECT @e1 = COUNT(*) FROM Employees 
    
    	SELECT * FROM Employees
    
    	RETURN @e1
    END
    
    -- Just retuns an integer value
    CREATE PROCEDURE [dbo].[EmployeeCount] As
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	declare @e1 int
    
    	SELECT @e1 = COUNT(*) FROM Employees 
    
    	RETURN @e1
    END

    I then dragged the two stored procedures onto my DBML without modifying it in any way. Then I used the below code to get the results.

    // This code gets the collection of employees and also returns a integer count of the number of employees.
    // Create the data context
    var ctx = new DataClasses1DataContext( );
    // Execute the stored procedure and assign the employee collection to EmployeeRecs
    var employeeRecs = ctx.EmployeesAndCount( );
    // Get the returned value from the stored procedure by getting it from employeeRecs query results
    int employeeCount = (int)employeeRecs.ReturnValue;

    The following code just returns a integer value from a stored procedure.

    // Create the data context
    var ctx = new DataClasses1DataContext( );
    // Execute stored procedure and return integer value
    var empCount = ctx.EmployeeCount( );

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, September 2, 2012 2:52 PM
  • Seems like you are doing it just like I am and still I don't get it working. The only difference was that I didn't have the NOCOUNT set to ON, but it didn't work with that either. 
    Monday, September 3, 2012 5:41 AM
  •  

    What version of Visual Studio and .Net Framework are you using?

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, September 3, 2012 1:36 PM