Linq2Sql Designer Turns Store Procedure Multiple Result Set to Single

Question

In a linq2sql class, I call a stored procedure that returns multiple result sets and it works.

Whenever I add a new procedure in the linq2sql designer and it stealth converts the aforementioned stored procedure from IMultipleResults to ISingleResult in designer.cs.

I replaced the code with a previous version and it works, but why does this happen?

How do I prevent the designer from changing code that works?

Every time I add a new sp, I have to undo what the designer does.

This

[Function(Name="dbo.GetCustomerOrderDetails")]
[ResultType(typeof(GetCustomerOrderSummaryResult))]
[ResultType(typeof(GetOrderLineDetailsResult))]

public IMultipleResults GetCustomerOrderDetails([Parameter(DbType="UniqueIdentifier")] System.Nullable<System.Guid> custGuid, [Parameter(DbType="VarChar(75)")] string email, [Parameter(DbType="Int")] System.Nullable<int> orderId)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), custGuid, email, orderId);
       return (IMultipleResults)result.ReturnValue;
}

gets changed to this by the linq2sql designer

[Function(Name="dbo.GetOrderLineDetails")]
public ISingleResult<GetOrderLineDetailsResult> GetOrderLineDetails([Parameter(DbType="Int")] System.Nullable<int> orderId)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), orderId);
    return ((ISingleResult<GetOrderLineDetailsResult>)(result.ReturnValue));
}

which I eventually undo manually.

Answer 1

Instead of using stored procedures, use User defined functions. They will let you define a return type. LinqToSql can then read that return type and create classes accordingly. Otherwise your kind've stuck with what it generates (as far as I know)

Answer 2

The derived DataContext class that or designer creates is declared as a partial class. Create another file that extends that partial class, move your specific code that the or designer keeps changing, plus the GetOrderLineDetailsResult class, into that file. Then remove the sp from or designer. OR designer should now leave your code untouched.