How dbml should be written when a stored procedure returns multiple result sets?

Question

Is there any way to also modify dbml so that the method in designer.cs stays as IMultipleResults?

I have a stored procedure that returns 2 sets of data. When I drag & drop the procedure to dbml, xml and designer.cs generated by dbml

<Function Name="dbo.MultiResultsTest" Method="MultiResultsTest">
<Parameter Name="iCustomerID" Type="System.Int32" DbType="Int" />
<Parameter Name="iProductID" Type="System.Int32" DbType="Int" />
<ElementType Name="MultiResultsTestResult">
  <Column Name="CustomerID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
  <Column Name="Name" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
  <Column Name="Phone" Type="System.String" DbType="VarChar(14)" CanBeNull="true" />
  <Column Name="Email" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
  <Column Name="Address" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
</ElementType>

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.MultiResultsTest")]
public ISingleResult<MultiResultsTestResult> MultiResultsTest([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> iCustomerID, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> iProductID)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), iCustomerID, iProductID);
    return ((ISingleResult<MultiResultsTestResult>)(result.ReturnValue));
}

And to get the multiple results I modified the designer.cs to like this:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.MultiResultsTest")]
[ResultType(typeof(Customer))]
[ResultType(typeof(Product))]
public IMultipleResults MultiResultsTest([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> iCustomerID, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> iProductID)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), iCustomerID, iProductID);
    return (IMultipleResults)(result.ReturnValue);
}

This works fine but because of the dbml, when I add more to it and save the method in designer.cs reverts back to the ISingleResult one.

Answer 1

After bit of searching and trying I found this way works!

<Function Name="dbo.MultiResultsTest" Method="MultiResultsTest">
<Parameter Name="iCustomerID" Type="System.Int32" DbType="Int" />
<Parameter Name="iProductID" Type="System.Int32" DbType="Int" />
<ElementType Name="CustomerResult">
  <Column Name="CustomerID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
  <Column Name="Name" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
  <Column Name="Phone" Type="System.String" DbType="VarChar(14)" CanBeNull="true" />
  <Column Name="Email" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
  <Column Name="Address" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
</ElementType>
<ElementType Name="ProductResult">
  <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
  <Column Name="ProductName" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
  <Column Name="Price" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
  <Column Name="ModelNumber" Type="System.String" DbType="VarChar(MAX)" CanBeNull="true" />
</ElementType>

This generates the following method in designer.cs

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.MultiResultsTest")]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(CustomerResult))]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(ProductResult))]
public IMultipleResults MultiResultsTest([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> iCustomerID, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] System.Nullable<int> iProductID)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), iCustomerID, iProductID);
    return ((IMultipleResults)(result.ReturnValue));
}