Adding support for sql functions

Adding strongly typed support for any sql function

SqlFu builders comes with support for common sql functions, but in practice you might need specific db functions. This page is a tutorial on how to implement the needed functions as extensions methods to be used with a sql builder. It's a medium difficulty task and it requires basic knowledge of using expressions.

Example to add support for T-SQl function: DateDiff

DATEDIFF ( datepart , startdate , enddate )

Step 1: Create/Add your extension method

Datediff has 3 arguments and the first one is basically an enum, while the other 2 are either a column or a value. At this point we don't care about it, only the types are relevant. Also, because we 're dealing with several arguments and some of them can be columns, I decided to 'attach' this method to the table. Note, that this methods are to be used only inside the query builder, which gets the table as parameter.

 public enum TSqlDatePart
    {
        Year,
        Day,
        Hour
        //etc
    }

 public static class MyExtensions
    {
        public static int DateDiff<T>(this T table, TSqlDatePart part, DateTime startDate, DateTime endDate)
        {
           throw new NotSupportedException("This shouldn't be called directly, only inside an expression of a sql builder or helper");
        }
    }

This is the signature definition and its purpose is to be available as an extension method inside a sql builder. The method is never invoked, it exists only to have it on intellisense.

Step 2: Implementing the real functionality

We're going to need a class to store all the new methods and that class must derive from DbProviderExpressions.

 public class MyFunctions : DbProviderExpressions
    {
        public MyFunctions()
        {
            //required so that the builder will know which function has the real functionality
            //the first lambda is there to identify the extension method. The numbers and value of the arguments are random values. They don't matter, you can put anything that allows it to compile
            LinkMethods(() => 1.DateDiff(TSqlDatePart.Day, DateTime.Now, DateTime.Now), DateDiff);
        }
        
        //the real implementation. this is the default signature for every method implementing a sql function  
        private void DateDiff(MethodCallExpression method, StringBuilder sb, IGenerateSqlFromExpressions writer)
        {
            sb.Append("datediff(");
            //'method' represents the invocation of the extension method and provides us with the function's arguments
            // the first argument is the table and we ignore it
            //the second argument is the 'datepart' represented by the enum. GetValue() is a helper method to get the value from an expression node
            // basically here we do an enum to string
            sb.Append(method.Arguments[1].GetValue().ToString()).Append(",");
            //the next argument is either a column or a datetime value. When it's either a column or a value (constant) always invoke `writer.GetColumnsSql` for the expression (as method call's argument). If it's just a value, use 'method.Arguments[pos].GetValue()'
            // we write the 'startdate' argument which is third argument of our extension method
            sb.Append(writer.GetColumnsSql(method.Arguments[2])).Append(",");
            // we write the 'enddate' argument
            sb.Append(writer.GetColumnsSql(method.Arguments[3]));
            sb.Append(")");
        }
    }

Basically, all we do is to write the sql function by interpreting the method call expression. For more examples take a look at the common functions implementation.

Step 3: Telling the db provider to use our new class

Remember that MyFunctions inherits from DbProviderExpressions so it is a provider for expressions. The best place to tell SqlFu what to use is in the configuration.

 SqlFuManager.Configure(c =>
 {
     var provider=new SqlServer2012Provider(SqlClientFactory.Instance.CreateConnection);
     provider.ReplaceExpressionsProvider(new MyFunctions());
     c.AddProfile(provider,cnx_string);            
 });

And that's it! You can now add support for any sql function you want for any db engine.

Usage example

_db.QueryValue(q => q.From<User>().Select(d => d.DateDiff(TSqlDatePart.Hour, d.CreatedOn, DateTime.Now.AddHours(3))));