capture output parameter from stored proc in LINQ To SQL ExecuteQuery

Question

Is it possible to get an output parameter back from the LINQ To SQL DataContext when you execute a stored procedure?

IEnumerable<Address> result = 
    ExecuteQuery<Address>(((MethodInfo)(MethodInfo.GetCurrentMethod())), 
       address, pageIndex, pageSize, totalCount);

where address, pageIndex and pageSize are input parameters, and TotalCount is an output parameter.

How can you capture the output param?

here is another attempt at doing it but again cannot get the parameter value:

    [Function(Name = "Telecom.AddressSearch")]
        private IEnumerable SearchAddress([Parameter(Name = "address", DbType = "varchar")] string address,
                                             [Parameter(Name = "pageIndex", DbType = "int")] int pageIndex,
                                             [Parameter(Name = "pageSize", DbType = "int")] int pageSize,
                                             [Parameter(Name = "totalCount", DbType = "int")] ref int totalCount)
        {            
            IEnumerable result = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);

            return result;
        }

Answer 1

Scott Guthrie has a blog post that describes how to get LINQ to SQL to work with stored procedures. While his post does not directly answer your question, it provides a clue to something that may work. When defining a method in a .dbml class, "LINQ to SQL maps 'out' parameters in SPROCs as reference parameters (ref keyword)." You might try and use the ref keyword and see if the totalCount gets updated.

   IEnumerable r = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())),
   address, pageIndex, pageSize, ref totalCount);

Update:

I did some more research, and have found a way that should work for you. This is from an MSDN article. You would need to extend your DataContext, but that shouldn't be too big of an issue (it appears you may already be doing this). Check out the article for more information, but the basic piece is this:

[Function(Name="dbo.CustOrderTotal")]
[return: Parameter(DbType="Int")]
public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
    totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
    return ((int)(result.ReturnValue));
}

Where 'this' is your DataContext.

Update 2:

The IExecuteResult has a ReturnValue property. It is of type Object, so you will have to cast it to get the results, but it should allow you to get the Enumerable of the results. In your case, something like this should work:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);
totalCount = ((System.Nullable<decimal>)(result.GetParameterValue(3)));
return (IEnumerable<Address>)result.ReturnValue;