DLinq User-Defined Function support

Article
06/13/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/CreateMethodCallQuery that create 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.]

In the last post, we looked at DLinq Stored Procedure support. Another cool new DLinq feature in the latest LINQ preview is support for user-defined functions. We’ve added support for mapping methods to user-defined functions (both scalar and table-valued) allowing the UDFs to be used in queries naturally.

You can run SqlMetal from a VS command prompt against the Northwind database specifying the /functions option to generate mapped methods for user-defined functions:

C:\Program Files\Microsoft Visual Studio 9.0\VC>sqlmetal /server:localhost

/database:"Northwind" /namespace:nwind /code:northwind.cs /language:csharp /functions

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 user-defined functions in the database. An excerpt is shown below:

 public partial class Northwind : DataContext {
    . . .
    [Function(Name = "dbo.TotalProductUnitPriceByCategory", IsComposable = true)]
    [return: Parameter(DbType = "Money")]
    public System.Nullable<decimal> TotalProductUnitPriceByCategory(
                   [Parameter(DbType = "Int")] System.Nullable<int> categoryID) {
        return ((System.Nullable<decimal>)
               (this.ExecuteMethodCall(this, 
               ((MethodInfo)(MethodInfo.GetCurrentMethod())), 
               categoryID).ReturnValue));
    }

    [Function(Name="dbo.ProductsUnderThisUnitPrice", IsComposable=true)]
    public IQueryable<ProductsUnderThisUnitPriceResult> ProductsUnderThisUnitPrice(
                   [Parameter(DbType="Money")] System.Nullable<decimal> price) {
        return this.CreateMethodCallQuery<ProductsUnderThisUnitPriceResult>(this, 
               ((MethodInfo)(MethodInfo.GetCurrentMethod())), price);
    }
    . . .
}

In the method bodies for these UDF mapped methods, you see that we call the ExecuteMethodCall context method to create the required expression tree, execute the query and return the result. When the method is used inline in a query this code is not executed – the query translator knows how to translate the method based on metadata (attribute or external). However if the method called outside of a query then the method body is executed to create the method call expression, turn it into a query and execute it.

The first UDF ‘TotalProductUnitPriceByCategory’ takes a category ID and returns the total of all unit prices for products in that category. Here is an example using this function in a query:

        var q = from c in db.Categories
               select new {
                 c.CategoryName,
                 Total = db.TotalProductUnitPriceByCategory(c.CategoryID)
               };

The function is used inline in a query just like you use a UDF inline in a SQL query. Below is the SQL that is generated – the UDF call is translated as you would expect:

SELECT [t0].[CategoryID],

[dbo].[TotalProductUnitPriceByCategory]([t0].[CategoryID]) AS [value]

FROM [Categories] AS [t0]

If the same UDF is executed outside of a query the mapped method builds the expression and creates and executes a query:

       decimal total = db.TotalProductUnitPriceByCategory(8);

SELECT [dbo].[TotalProductUnitPriceByCategory](@p0) AS [value]

The ‘ProductsUnderThisUnitPrice’ method is mapped to a table valued UDF, and returns an IQueryable. As such, it can be used in a query wherever a table or sub-query can be. Here is an example joining a table to the results of a TVF (and the resulting SQL):

       var q = from c in db.Categories
              from p in db.ProductsUnderThisUnitPrice(8.50M)
              where c.CategoryID == p.CategoryID
              select new {
                    c.CategoryID,
                    c.CategoryName,
                    p.ProductName,
                    p.UnitPrice
              };

SELECT [t0].[CategoryID], [t0].[CategoryName],

[t1].[ProductName], [t1].[UnitPrice]

FROM [Categories] AS [t0]

CROSS JOIN [dbo].[ProductsUnderThisUnitPrice](@p0) AS [t1]

WHERE [t0].[CategoryID] = [t1].[CategoryID]

As a final example let’s look at how to map system functions. While DLinq automatically maps many .NET methods (like string/DateTime methods) to corresponding system functions, there may be some system functions that have no .NET counterpart that you’d like to use. In such cases, you can map a method yourself. For example, if you’re just dying to be able to use the SqlServer Soundex function directly in your DLinq queries, you can map it as follows:

      [Function(Name = "Soundex", IsComposable = true)]
     public string SoundEx([Parameter(Name = "Expression")] string expression) {
         return (string)this.ExecuteMethodCall(this, 
                ((MethodInfo)(MethodInfo.GetCurrentMethod())), 
                 expression).ReturnValue;
     }

This function can now be used as follows:

      var q = from e in db.Employees
             orderby db.Soundex(e.FirstName)
             select new {
                 e.FirstName, 
                 Soundex=db.Soundex(e.FirstName)
             };
     string s = db.Soundex("Mathew Charles");

If you use UDFs in your database and are playing around with the new CTP, give them a try in your DLinq queries and please let us know what you think!

DLinq User-Defined Function support

Additional resources