Linq to Sql and Stored Procedures
Yesterday I discussed Linq to SQL and interoperability with existing database technology.
Today I'll look at how Linq to SQL handles stored procedures. Much of the work is done by the database designer, which creates methods that invoke your stored procedures.
At its core, all calls into stored procedures call DataContext.ExecuteMethodCall(). ExecuteMethodCall returns an IExecuteResult object. IExecuteResult provides a Result parameter to access the result object.
Attributes on each method call provide type information about the call. For example, this method call takes an NChar(15) as a parameter and returns an int:
[Function(Name="dbo.Customers Count By Region")]
[return: Parameter(DbType="Int")]
public
int CustomersCountByRegion([Parameter(DbType="NVarChar(15)")] string param1)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
return ((int)(result.ReturnValue));
}
You call that method thusly:
int count = db.CustomersCountByRegion("WA");
That's a simple scalar method that returns an integer.
Of course, many stored procedures return a set of results. Methods that return a sequence of objects return a type of ISingleResult<T>. In this instance, you get a sequence of CustomerByCityResult objects:
ISingleResult<CustomersByCityResult> result = db.CustomersByCity("London");
CustomerByCityResult is created by the Linq to SQL designer as a partial class when you add the stored procedure to you data model. That class is an autogenerated class that contains properties and fields for each of the columns returned by that stored procedure.
Some stored procedures return different record sets depending on parameters. That's support by retrieving the result as an object that implements IMultipleResult. Then, you can use GetResult<T> to retrieve the result as one of the types returned by the stored procedure.
For example, this (rather contrived) method returns either the full customer record, or only selected columns from the customer table:
IMultipleResults result = db.WholeOrPartialCustomersSet(1);
IEnumerable<WholeCustomersSetResult> shape1 = result.GetResult<WholeCustomersSetResult>();
result = db.WholeOrPartialCustomersSet(2);
IEnumerable<PartialCustomersSetResult> shape2 = result.GetResult<PartialCustomersSetResult>();
But wait, there's more. Some Stored procedures return records of more than one type. Then, you can retrieve the different record types by calling GetResult<T> using the different type parameter:
IMultipleResults result = db.GetCustomerAndOrders("SEVES");
IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();
IEnumerable<OrdersResultSet> orders = result.GetResult<OrdersResultSet>();
Finally, you can use the Linq to SQL framework to work with stored procedures that use out parameters. In all cases, the output parameters are cast as ref parameters, even if the parameter is used only as an out parameter:
db.CustomerTotalSales(customerID, ref totalSales);
The DataContext.ExecuteMethodCall() can also be used to access User Defined Functions on the DataContext object. The return value is the same (either ISingleResult<T>, or IMultipleResults). However, the wizard generated code may generate a call to either DataContext.ExecuteMethodCall, or DataContext.CreateMethodCallQuery(), depending on the action of the user defined function.
Next, we'll come back with an overview of the DataContext methods.