Expression class under System.Linq.Expressions is a very useful class which includes many static members helping you to create expression tree and execute the tree. Whenever you need to execute a dynamically generated logic in SQL Server through an CLR, you might consider using the classes in Expressions name space. In this post, I am going to give you an very simple example to explain how to create and execute an expression tree.

An CLR function with 2 parameters will be created. The logic is below

 return case when a<b then 1 else 0 end

Now I want to implement it through CLR using Expressions name space.

public static SqlBoolean ExpressionTreeTest(SqlInt32 a, SqlInt32 b)
    ParameterExpression p1 = Expression.Parameter(typeof(int)); //parameter a
    ParameterExpression p2 = Expression.Parameter(typeof(int));//parameter b

    BinaryExpression p3 = Expression.LessThan(p1, p2); // create expression tree
    //compile expression tree
    Expression<Func<int, int, bool>> le = Expression.Lambda<Func<int, int, bool>>(p3, p1, p2); 
    Func<int, int, bool> fn = le.Compile();
    //execute tree and return.
    return new SqlBoolean(fn(a.Value, b.Value));

Code above is very straight forward. Line 4 and 5 define the parameters of the expression. Line 7 defines a binary expression tree using less than operator. Now the tree is saved in variable p3. Line 9 and Line 10 compile the tree. Line 12 execute the delegation and return the value.

select dbo.ExpressionTreeTest(1,2)
select dbo.ExpressionTreeTest(2,1)


(1 row(s) affected)


(1 row(s) affected)

Use Expression in CLR

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.