Querying Store Procedures(SP) returning Multiple Result Sets in LINQ to SQL (L2S)
L2S (working with designer in VS2008) works fine as long as SPs return data from a single table; problem arises when the SP returns multiple result sets and things become complicated when the data in the result sets is from a combination of tables.
Store Procedure modeled on NorthWind Database returning multiple results sets -
ALTER PROCEDURE [dbo].MySP
AS
BEGIN
SET NOCOUNT ON;
select Employees.FirstName,Employees.LastName,[Orders].OrderId,[Order Details].UnitPrice
from Employees,[Orders],[Order Details]
where Employees.EmployeeId = Orders.EmployeeId and Orders.OrderId = [Order Details].OrderId
select o.OrderId,o.OrderDate,d.Quantity,p.ProductName,s.CompanyName
from Orders o,[Order Details] d,Products p,Suppliers s
where o.OrderID = d.OrderID and d.ProductID = p.ProductId and p.SupplierID = s.SupplierID
END
When we drag and drop the SP onto the designer, the method created by SP looks similar like this
[Function(Name="dbo.MySP")]
public ISingleResult<MySPResult> MySP1()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<MySP1Result>)(result.ReturnValue));
}
The corresponding MySPResult class -
public partial class MySPResult
{
private string _FirstName;
private string _LastName;
private int _OrderId;
private decimal _UnitPrice;
public MySPResult()
{
}
[Column(Storage="_FirstName", DbType="NVarChar(10) NOT NULL", CanBeNull=false)]
public string FirstName
{
get
{
return this._FirstName;
}
set
{
if ((this._FirstName != value))
{
this._FirstName = value;
}
}
}
[Column(Storage="_LastName", DbType="NVarChar(20) NOT NULL", CanBeNull=false)]
public string LastName
{
get
{
return this._LastName;
}
set
{
if ((this._LastName != value))
{
this._LastName = value;
}
}
}
[Column(Storage="_OrderId", DbType="Int NOT NULL")]
public int OrderId
{
get
{
return this._OrderId;
}
set
{
if ((this._OrderId != value))
{
this._OrderId = value;
}
}
}
[Column(Storage="_UnitPrice", DbType="Money NOT NULL")]
public decimal UnitPrice
{
get
{
return this._UnitPrice;
}
set
{
if ((this._UnitPrice != value))
{
this._UnitPrice = value;
}
}
}
}
When the SP is called from the code, it will return data resulting only from the first select statement. To ensure that SP returns data from all the result sets from the SP certain changes needs to be done.
To begin with let us create another entity in the designer.cs say MySP1Result. The properties in this class will be same as the output from the second select statement in the SP.
public partial class MySP1Result
{
private int _OrderId;
private System.Nullable<System.DateTime> _OrderDate;
private short _Quantity;
private string _ProductName;
private string _CompanyName;
public MySP1Result()
{
}
[Column(Storage="_OrderId", DbType="Int NOT NULL")]
public int OrderId
{
get
{
return this._OrderId;
}
set
{
if ((this._OrderId != value))
{
this._OrderId = value;
}
}
}
[Column(Storage="_OrderDate", DbType="DateTime")]
public System.Nullable<System.DateTime> OrderDate
{
get
{
return this._OrderDate;
}
set
{
if ((this._OrderDate != value))
{
this._OrderDate = value;
}
}
}
[Column(Storage="_Quantity", DbType="SmallInt NOT NULL")]
public short Quantity
{
get
{
return this._Quantity;
}
set
{
if ((this._Quantity != value))
{
this._Quantity = value;
}
}
}
[Column(Storage="_ProductName", DbType="NVarChar(40) NOT NULL", CanBeNull=false)]
public string ProductName
{
get
{
return this._ProductName;
}
set
{
if ((this._ProductName != value))
{
this._ProductName = value;
}
}
}
[Column(Storage="_CompanyName", DbType="NVarChar(40) NOT NULL", CanBeNull=false)]
public string CompanyName
{
get
{
return this._CompanyName;
}
set
{
if ((this._CompanyName != value))
{
this._CompanyName = value;
}
}
}
}
Next we need to change the return type of MySP method. By default return type is ISingleResult<T>. The return type needs to be changed to IMultipleResults (this indicates that SP is returning multiple result sets) and the method also needs to be adorned with attributes which indicate the types returned by the SP.
[Function(Name="dbo.MySP")]
[ResultType(typeof(MySPResult))]
[ResultType(typeof(MySP1Result))]
public IMultipleResults MySP()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return (IMultipleResults)(result.ReturnValue);
}
The following code retrieves the results by executing the SP
using(NWDBDataContext nw = new NWDBDataContext())
{
nw.DeferredLoadingEnabled = false;
var result = nw.MySP();
foreach(var o in result.GetResult<MySPResult>())
{
//do something
}
foreach(var o in result.GetResult<MySP1Result>())
{
//do something
}
}
Note: The definitive LINQ Guide suggests to use ExecuteMethodCallWithMultipleResults for retrieving multiple result sets. But it looks like this method has been removed from the RTM version of Orcas and querying works fine with ExecuteMethodCall with additional changes mentioned.
