Spring Linq CTP finally hits the shelves!

Article
05/10/2006

[Updated on 8/30/2007: Code samples below updated for Beta2. The main thing we did was simplify the code required by providing framework methods ExecuteMethodCall that creates the required expression trees (where you had to create them before). You'll also notice that there is a single mapping attribute for both sprocs and functions, with IsComposible being used to distinguish between the two.]

The latest and greatest LINQ preview has finally been released! (you can download here: https://msdn.microsoft.com/netframework/future/) In addition to fixing a lot of bugs since the last CTP and making additions and modifications based on community feedback, we’ve also added some cool new features. I’d like to use the next few posts to explore a sampling of some of these new features (you can read about more of them in the C# readme here). The topic for today: Stored-Procedure Support.

We’ve added support for mapping methods to stored procedures allowing you to invoke them in a strongly typed manner as methods on the DataContext. To see this in action, you can run SqlMetal against the Northwind sample database specifying the /sprocs option to generate mapped methods for stored-procedures:

C:\Program Files\Microsoft Visual Studio 9.0\VC>sqlmetal /server:localhost
/database:"Northwind" /namespace:nwind /code:northwind.cs /language:csharp /sprocs

If you take a look at the generated code, you’ll see that methods have been added to the Northwind data context that map to the stored procedures in the database. An excerpt is shown below. The [Parameter] attributes aren't strictly necessary if your method parameters have the same names as the sproc params in the database, and explicit type mapping is not needed, but I've included them for completeness.

     public partial class Northwind : DataContext {
        . . .
        [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 Nullable<decimal> totalSales)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, 
                ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
            totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
            return ((int)(result.ReturnValue));
        }

        [Function(Name="dbo.Sales by Year")]
        public ISingleResult<SalesByYearResult> SalesByYear(
            [Parameter(Name="Beginning_Date", DbType="DateTime")] 
                Nullable<DateTime> beginning_Date, 
            [Parameter(Name="Ending_Date", DbType="DateTime")] 
                Nullable<DateTime> ending_Date)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, 
                ((MethodInfo)(MethodInfo.GetCurrentMethod())), beginning_Date, ending_Date);
            return ((ISingleResult<SalesByYearResult>)(result.ReturnValue));
        }

        [Function(Name="dbo.Get Customer And Orders")]
        [ResultType(typeof(GetCustomerAndOrdersResult1))]
        [ResultType(typeof(GetCustomerAndOrdersResult2))]
        public IMultipleResults GetCustomerAndOrders(
            [Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, 
                ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
            return ((IMultipleResults)(result.ReturnValue));
        }
        . . .
    }

The ‘CustOrderTotal’ method is mapped to a simple scalar sproc that takes an input parameter and returns an output parameter. Here’s how you would call it (all samples below assume a Northwind context instance ‘db’):

     decimal? totalSales = 0;
    db.CustOrderTotal("ALFKI", ref totalSales);

The ‘SalesByYear’ method demonstrates how a rowset returning sproc is mapped. For sprocs returning rowsets, a class is automatically generated for the rowset. Here is how you would call this sproc and access its result set:

     DateTime begin = new DateTime(1996, 11, 5);
    DateTime end = new DateTime(1997, 3, 15);
    ISingleResult<SalesByYearResult> sales = db.SalesByYear(begin, end);
    var bigSales = sales.Where(s => s.Subtotal > 5000.00m);

Important points to note here:

The return type ISingleResult<SalesByYearResult> implements IEnumerable allowing the returned rows to be enumerated, and also allows the sproc return value to be accessed if needed (via the ReturnValue property).
In cases where a sproc returns both a result set AND an out parameter, all results are read and buffered automatically (triggered by the first out parameter access) prior to the mapped method returning, since reading all the results off the wire is the only way to get the out parameter values.

Finally the ‘GetCustomerAndOrders’ method shows how sprocs that return multiple result-sets are mapped. From the database metadata, we can determine what possible rowset shapes can be returned from the sproc, but not in what order. Thus it is up to you to request the results in the correct order, as shown below:

     IMultipleResults results = db.GetCustomerAndOrders("ALFKI");
    var cust = results.GetResult<GetCustomerAndOrdersResult1>().First();
    var orders = results.GetResult<GetCustomerAndOrdersResult2>().ToArray();

If you are hand mapping sproc methods you'll likely want to use names more appropriate than Result1, Result2, etc. Using the DLinq designer, you can map your sprocs manually and rename things appropriately. You might also want to map the results to existing types (i.e. use Customers and Orders in the last example).

Note there are some limitations to the types of sprocs we can automatically generate code for. For example, the following categories of sprocs cannot be auto generated:

Sprocs using temporary tables – Since using FMTONLY true when executing a sproc (the mechanism we use to determine result shapes) doesn’t execute any statements, any CREATE TABLE statements used in a sproc to create a temporary table will not be executed. This means that if the sproc returns a result set formed by querying such a temporary table, any attempt to get the metadata will fail, since the table doesn’t exist
Sprocs using dynamic SQL to return result sets – If a sproc contains conditional logic that is used to build a dynamic SQL statement, then execute it to return a result set, there is no way to get metadata for the result set, since the query used to generate it can be variable, and is only known at runtime.

If you have sprocs that fall into these categories, SqlMetal will skip generation for them and log an error message. Of course you have the option of hand mapping them yourself.

As you can see such method mapping makes stored procedures convenient to call, by encapsulating the invocation as a simple method call, and providing data binding. The SampleQueries project (LINQ Preview\Samples\C#\SampleQueries) includes some additional sproc samples. Give them a try, and please let us know what you think! Next post we'll take a look at DLinq support for User-Defined Functions (UDFs), and see how you can use them inline in your queries.

Spring Linq CTP finally hits the shelves!

Additional resources