Strange system.void return exception generated when running a mapped stored procedure method in LINQ-to-SQL

Question

Hello,

I have a SQL Server 2008 stored procedure (pseudocode below) that goes as follows:

CREATE PROCEDURE MyProc
AS
BEGIN

 CREATE DummyTable

 INSERT INTO DummyTable
 SELECT xxx

 INSERT INTO DummyTable
 SELECT yyy

 IF EXISTS FinalTable DROP FinalTable

 EXEC sp_RENAME 'DummyTable', 'FinalTable'

END
GO

Note that there is no return type/value for this stored proc. I have this stored proc mapped in my LINQ-TO-SQL designer (ASP.NET MVC2 application). However, when I call the method as follows:

using (MyContext context = new MyContext())
{
 context.MyProc();
}

However, in my logs, I have found that this exception is being generated when this method is run:

System.InvalidOperationException: 'System.Void' is not a valid return type for a mapped stored procedure method. at System.Data.Linq.SqlClient.QueryConverter.TranslateStoredProcedureCall(MethodCallExpression mce, MetaFunction function) at System.Data.Linq.SqlClient.QueryConverter.VisitMappedFunctionCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)

Can anyone help me identify why this exception is being generated?

Thanks, A.

UPDATED: The auto generated code in the L2S designer file is:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.MyProc")]
public void MyProc()
{
 this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
}

Answer 1

What did the generated linq-to-sql code look like? Perhaps you manually changed it to return void?

Creating a stored proc that doesn't give output like so -

CREATE PROCEDURE MyProc
AS
BEGIN
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DropMe')
    DROP TABLE DropMe
END
GO

should generate the following code when adding it to the l2s design surface -

[Function(Name = "dbo.MyProc")]
        public int MyProc()
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
            return ((int)(result.ReturnValue));
        }

If yours is returning void instead, that will cause it to throw that exception.

So if i change the above to this -

[Function(Name = "dbo.MyProc")]
    public void MyProc()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return;
    }

It will throw this when invoked -

Line 17: public void MyProc() Line 18: { Line 19: IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); Line 20: return;// ((int)(result.ReturnValue)); Line 21: }

Source File: C:\Build\blah.cs Line: 19

Stack Trace:

[InvalidOperationException: 'System.Void' is not a valid return type for a mapped stored procedure method.]
System.Data.Linq.SqlClient.QueryConverter.TranslateStoredProcedureCall(MethodCallExpression mce, MetaFunction function) +904265