Building a rule engine in c#

In my current project in work i needed a simple rule engine that processes rules that are saved in the database. In the Database i have the following table:

The column Property stores the name of the property of the overtaken class. The column Operator stores the logical operator which is used to evaluate the rule and the column Value stores the constant to evaluate against. To try this rules we have the Person class.

    public class Person
    {
        public string Name { get; set; }
        public int Age { get; set; }
        public int Children { get; set; }
    }

This class has the properties Name, Age and Children. The rule class stores the rules that are defiened in the database table.

    public class Rule
    {
        private bool propertySet = false;
        public string PropertyName { get; set; }
        public Operator Operator_ { get; set; }
        public object Value { get; set; }

        public Rule(Operator operator_, object value)
        {
            this.Operator_ = operator_;
            this.Value = value;
        }

        public Rule(string propertyName, Operator operator_, object value)
        {
            this.Operator_ = operator_;
            this.Value = value;
            this.PropertyName = propertyName;
            if(!string.IsNullOrEmpty(propertyName))
                this.propertySet = true;
        }
    }

The RuleLoader class loads the rules from the database and stores it in Rule objects. This is the code to load the rules from the database.

            RuleLoader ruleLoader = new RuleLoader();
            Rule firstRule = ruleLoader.Load(1);
            Rule secondRule = ruleLoader.Load(2);
            Rule thirdRule = ruleLoader.Load(3);

Now we have the rules and what we need is a Person to apply the rules to.

Person person = new Person() { Name = "Mathias", Age = 35, Children = 2 };

Now we will define the rule engine that evaluates the values of the property of the Person objects against the definied rules from the database. To do so we need some reflection and some objects from the System.Linq.Expressions namespace. This is the RuleEngine class that generates a Func that we use to evaluate the overtaken rule.

    public class RuleEngine
    {
        public Func<T, bool> CompileRule<T>(Rule rule)
        {
            if (string.IsNullOrEmpty(rule.PropertyName))
            {
                ExpressionBuilder expressionBuilder = new ExpressionBuilder();
                var param = Expression.Parameter(typeof(T));
                Expression expression = 
                    expressionBuilder.BuildExpression<T>(rule.Operator_, rule.Value, param);
                Func<T, bool> func = 
                    Expression.Lambda<Func<T, bool>>(expression, param).Compile();
                return func;
            }
            else
            {
                ExpressionBuilder expressionBuilder = new ExpressionBuilder();
                var param = Expression.Parameter(typeof(T));
                Expression expression = 
                    expressionBuilder.BuildExpression<T>(
                    rule.PropertyName, rule.Operator_, rule.Value, param);
                Func<T, bool> func = 
                    Expression.Lambda<Func<T, bool>>(expression, param).Compile();
                return func;
            }
        }
    }

The RuleEngine class uses the ExpressionBuilder class to build the Expression and compiles it to a lambda expression. This lambda expression does the actual work when a rule is evaluated.

    public class ExpressionBuilder
    {
        public Expression BuildExpression<T>(
            Operator ruleOperator, object value, ParameterExpression parameterExpression)
        {
            ExpressionType expressionType = new ExpressionType();
            var leftOperand = parameterExpression;
            var rightOperand = 
                Expression.Constant(Convert.ChangeType(value, typeof(T)));
            var expressionTypeValue = 
                (ExpressionType)expressionType.GetType().GetField(
                Enum.GetName(typeof(Operator), ruleOperator)).GetValue(ruleOperator);
            var binaryExpression = 
                Expression.MakeBinary(expressionTypeValue, leftOperand, rightOperand);
            return binaryExpression;
        }

        public Expression BuildExpression<T>(
            string propertyName, Operator ruleOperator, object value, 
            ParameterExpression parameterExpression)
        {
            ExpressionType expressionType = new ExpressionType();
            var leftOperand = MemberExpression.Property(parameterExpression, propertyName);
            var rightOperand = Expression.Constant(Convert.ChangeType(value, value.GetType()));
            FieldInfo fieldInfo = 
                expressionType.GetType().GetField(Enum.GetName(typeof(Operator), ruleOperator));
            var expressionTypeValue = (ExpressionType)fieldInfo.GetValue(ruleOperator);
            var binaryExpression = 
                Expression.MakeBinary(expressionTypeValue, leftOperand, rightOperand);
            return binaryExpression;
        }
    }

Here we see the usage of the RuleEngine class. We call the CompileResult method and get back a Func that evaluates us the overtaken parameter (in this case of type Person).

     RuleEngine ruleEngine = new RuleEngine();
     var firstRuleFunc = ruleEngine.CompileRule<Person>(firstRule);
     var secondRuleFunc = ruleEngine.CompileRule<Person>(secondRule);
     var thirdRuleFunc = ruleEngine.CompileRule<Person>(thirdRule);
     var result = firstRuleFunc(person) &&
         secondRuleFunc(person) && thirdRuleFunc(person);

So we know that the Person object

( Person person = new Person() { Name = "Mathias", Age = 35, Children = 2 }; )

passes all the defiend rules from the database.

In the second part of this post i will extend the rule engine to handle collections:
Building a rule engine in c# (part 2: extending the rule engine to handle collections) In this third part of the post series called Building a rule engine in c# (part 3: extending the rule engine to handle aggregations of collections) i extend the rule engine to handle aggregation for collections. In the fourth part of this blog series i write about implementing an expression evaluator to define more complex expressions Building a rule engine in c# (part 4: extending the rule engine to evaluate defined expressions) and in the fifth part building a rule engine in c# (part 5: bringing it all together) i give an overview about the usage of the ruleengine.codeplex.com project i created to bring the code parts in that post series together.

Advertisements

New tsql commands in sql server 2012 (part 5)

In this post i will write about the FORMAT, TRY_CONVERT, PARSE and the TRY_PARSE command.

FORMAT
With the format command it is possible to format a datetime with valid .NET Framework format string.

declare @datetime datetime = getdate();
declare @language nvarchar(5);

set @language = 'en-gb';

select format(@datetime, 'yyyy/MM/dd', @language) as result, @language [language];

set @language = 'de-de';

select format(@datetime, 'yyyy/MM/dd', @language) as result, @language [language];

This is the result despite of the .net format string (‘en-gb’ or ‘de-de’).

TRY_CONVERT
The TRY_CONVERT command does work as the CONVERT command, but if the conversion fails the TRY_CONVERT command does not throw an error, despite of it returns null.

SELECT try_convert(datetime, 'date', 101) as Date

This is the result, as you see the command returns null because it can not convert the string ‘date’ to an correct datetime.

PARSE and TRY_PARSE

In the following query i try to convert the german date string ‘Montag, 13 Dez 2010’ to datetime. With
the language string ‘de-DE’ it works fine but with the language string ‘en-GB’ the parse command produces an error.

declare @language nvarchar(5);

set @language = 'de-DE';

SELECT PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

set @language = 'en-GB';

SELECT PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

Result of the PARSE query:

If we try the same query with the TRY_PARSE command, the parsing with the english language string fails but try_parse reports no error, instead of it returns null.

declare @language nvarchar(5);

set @language = 'de-DE';

SELECT TRY_PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

set @language = 'en-GB';

SELECT TRY_PARSE('Montag, 13 Dez 2010' AS datetime USING @language) AS Result

Result of the TRY_PARSE query:


New tsql commands in sql server 2012 (Part 4)

Today i will write about two new tsql commands. The first one is choose command. The second one is the iif command. Here is an example of an query that uses choose and iif. The choose command takes the first parameter wich is an integer evaluates this integer and chooses the corresponding parameter from the parameter list. If you use choose(2,’one’,’two’,’three’) the result is ‘two’. The iif command is simular to the case command but easier to write and it has only one condition. When you write the case command case when (id <= 4) then 'first' else 'second' end you also could write iif(id <= 4, 'first', 'second').

select top 6 
        id, 
        data, 
        case when (id <= 4) then 'small' else 'big' end as [case],
        iif(id <= 4, 'small', 'big') as iif,
        choose(id, 'first', 'second', 'third', 'fourth', 'fifth') as choose
from 
        TestData

That is the result:


New tsql commands in Sql Server 2012 (Part 3)

In the new version of the sql server 2012 it is possible to easily implement paging. To do so you need the offset x rows command (x defines the starting row of your query) and the fetch next x rows only command. (x defines the rowcount of your query).

declare @counter int = 0
while(@counter < 50)
begin
    select 
        id, 
        data, 
        iif(id <= 5, 'small', 'big')
    from 
        testData
    order by id
        offset @counter ROWS
    FETCH NEXT
        5 ROWS ONLY;
    set @counter = @counter + 5;
end

Result of the query.

As you see with the offset and the fetch next rows command it is very easy to generate custom paging at database level.


New tsql commands in Sql Server 2012 (Part 2)

The new tsql feature i am writing about today is the with result set feature. Until now if you wanted to change the column name or datatype of the result set of an stored procedure you needed a temptable.

This is the procedure we use to return a dataset.

create procedure testProcedure
(
    @maxid int
)
as
    select 
            id,
            data 
    from 
            testData 
    where 
            id < @maxid
    

Here is the pre sql server 2012 tsql code to rename or change the datatype of the result set of the procedure.

DECLARE @tempTable TABLE (MyIdbigint, MyText nvarchar(MAX))
 
INSERT INTO @tempTable
EXEC testProcedure @maxid = 5
 
SELECT * FROM @tempTable

If you execute this tsql code you get the following result:

In sql server 2012 you could use the with result set command, to rename or change the datatype of the result set of an stored procedure.

EXEC testProcedure @maxid = 5
WITH RESULT SETS
(
    (
        MyId bigint,
        MyText nvarchar(max)
    )
)

If you execute this tsql code you get the following result: