Linq to SQL Stored procedure vs Functions

When it comes to Linq to SQL, there is a very interesting difference between using a stored procedure or a table function (or user defined functions).  In this post, I want to discuss the similarities and differences between the two and which one should be used in a given scenario.

Stored Procedures

Stored Procedures can be dragged and dropped on the right hand side of the designer.  This will then add the stored procedure as a method on your Data Context. If the stored procedure accepts parameters, then these will be translated into parameters on the method.

The method code for CustOrderHis stored procedure would be:

[Function(Name=”dbo.CustOrderHist”)]
public ISingleResult CustOrderHist([Parameter(Name=”CustomerID”, DbType=”NChar(5)”)] string customerID)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
    return ((ISingleResult)(result.ReturnValue));
}

You could then call it in your code:

DataClasses1DataContext dc = new DataClasses1DataContext();
var sproc = dc.CustOrderHist(“ALFKI”);

If we look at the code produced, the first important part is the FunctionAttribute which defines which stored procedure will be called in the database. The next important part is the GetCurrentMethod which allows Linq to access the method to execute. According to the documentation, the method “Returns a MethodBase object representing the currently executing method”. This is then passed into the ExecuteMethodCall which will result in the query being executed against the database.

Once the result has been returned, it is converted into a ISingleResult, which implements IEnumerable, and returned to the calling client. As ISingleResult is a generic class, an object with the same name as the sproc is created with the same properties as returned by the stored procedure.

However, if you know your stored procedure returns a object already in your system, for example GetCustomer might return a row from your Customer table, then you can use that object as the generic type – ISingleResult. This saves having unnecessary objects in your system.

When using stored procedures they can only return items of type ISingleResult or IMultipleResult (discussed later). This causes a number of problems when using the created objects.  Firstly, in your client code you will have to manually convert the result to something like ToList() if you want to bind the objects to a data source. If you convert the objects to a list then they will not be change-tracked. Finally, you cannot benefit from deferred loading when using stored procedures as the call to the database is executed when the method is called.

If your stored procedure returns two record sets, for example something like below

SELECT * FROM Customers

SELECT * FROM Orders

Then there will be a problem with a method returning ISingleResult. This is where IMultipleResult comes in handy. However just dragging the stored procedure like above will not generate the correct code. Instead, you will have to add your own partial method for the code.

Given this code, we will need to convert it so it can work correctly.

[Function(Name=”dbo.ReturnCustomerAndOrder”)]
public ISingleResult ReturnCustomerAndOrder()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((ISingleResult)(result.ReturnValue));
}

The code for it to correctly access two or more different tables would look something like this:

        [Function(Name=”dbo.ReturnCustomerAndOrder”)]
        [ResultType(typeof(Customer))]
        [ResultType(typeof(Order))]
        public IMultipleResults ReturnCustomerAndOrder()
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
            return ((IMultipleResults)(result.ReturnValue));
        }

NOTE: Important to include this in a partial class otherwise the designer will override your changes.

To access one of the set of results, you need to explicitly request it using the GetResult<> method.

DataClasses1DataContext dc = new DataClasses1DataContext();
IMultipleResults sproc = dc.ReturnCustomerAndOrder();
List orders = sproc.GetResult().ToList();

foreach (Order o in orders)
{
    Console.WriteLine(o.OrderID);
}

This allows you to deal with both situations for stored procedures. This is also what you will need to do if your sproc returns a single object but of different types.

User Defined Functions

Like stored procedures, user defined functions can be added as a method on your data context.  User defined functions are similar to stored procedures, however you have the ability to defined what is being returned from the function, this allows Linq to correctly understand the sql and produce correct code to match.

The function below returned as table, as such it is called a table valued function.

CREATE FUNCTION [dbo].[AllOrdersInWeek52Function]()
    RETURNS TABLE
AS
RETURN (SELECT * FROM Orders
        WHERE DATEPART(ww, OrderDate) = 52)

When this code is added to the data context, the following code is produced.

[Function(Name=”dbo.AllOrdersInWeek52Function”, IsComposable=true)]
public IQueryable AllOrdersInWeek52Function()
{
    return this.CreateMethodCallQuery(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
}

As you can see, this returns an IQueryable object, unlike the sproc which returned ISingleResult.  This means that you can take full advantage of Linq when querying against this object in the database.

Other advantages of functions is that they can be executed against the database, inline with an existing linq to sql query.  This means that we can do more advanced queries solely in the database without having to bring everything in memory to perform the same task. For example, if we can the function below which takes a date and returns an int representing the week of the year.

CREATE FUNCTION WeekOfYear(@Date DateTime)
RETURNS Int
AS
BEGIN
RETURN (CAST(DATEPART(ww, @Date) AS INT))
END

Then we can use that within our linq query.

DataClasses1DataContext dc = new DataClasses1DataContext();
var q = from o in dc.Orders
        where dc.WeekOfYear(o.OrderDate) == 23
        select o;

foreach (var item in q)
{
    Console.WriteLine(item.OrderID);
}

Now, instead of WeekOfYear being performed in memory over the entire table as would be required without the function then its included within the generatored SQL.

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [dbo].[WeekOfYear]([t0].[OrderDate]) = @p0

I’ve highlighted the important section, as you can see this will result in a much better execution.

Summary

In summary, user defined functions provide you with more flexibility and allow you to gain some great advantages over the queries being executed. Very powerful and something to keep in mind.  Stored procedures have some limitations, but can solve certain scenarios.