May 142012
 

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.

[Microsoft.SqlServer.Server.SqlFunction]
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

(1 row(s) affected)


-----
0

(1 row(s) affected)


*/

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

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

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