2

I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties: - key - value - comparer (less than and greater than as a start, preferably with the ability to add additional types of comparers or rules in the future)

As a basic use case example, these threshold values would be used to raise an event if some parameter in the system exceeds a threshold.

With that in mind, I would appreciate any feedback or input in how precicely to go about this. In the past I have seen such a task done by either using a lookup table containing the types of the comparers, or storing some type of formula in a column and afterwards parsing it using NCalc (.NET) or something similar. Both approaches have drawbacks- the lookup table doesn't seem very practical, and parsing stuff seems very unstable.

Any suggestions on what would be sensible in this scenario would be greatly appreciated. I am not an expert in DB design, so any input will be valuable.

1 Answer 1

2

For a database design, all one needs is the left, operator, and right.

Something like this:

X > 10

A table like this:

RuleSetID   PropertyName    Operand TargetValue 
1            Weight          LessThan   200

Then one can use System.Linq.Expressions namespace to dynamically create expressions for evaluation.

For more information on this .Net framework feature, please visit this link:

https://msdn.microsoft.com/en-us/library/system.linq.expressions(v=vs.110).aspx

All kinds of operators are supported, greater than, less than etc.

Now, some code to compile the expressions:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using RulesEngine.Model;
using System.Diagnostics;

namespace RulesEngine.Engine
{
    public class ExpressionBuilder
    {
        public Func<T, bool> Compile<T>(Rule rule, Type type)
        {
            try
            {
                var param = Expression.Parameter(type);
                Expression expression = BuildExpression<T>(rule, param);
                return Expression.Lambda<Func<T, bool>>(expression, param).Compile();
            }
            catch (Exception exception)
            {
                //We would want to log this failure so the rule can be corrected.
                Debug.WriteLine(exception.ToString()); 
                //Return an expresssion that would always return false
                Expression<Func<T, bool>> defaultLambda = x => false;
                return defaultLambda.Compile();
            }

        }

        private Expression BuildExpression<T>(Rule rule, ParameterExpression param)
        {
            var left = MemberExpression.Property(param, rule.PropertyName);
            var propertyType = typeof(T).GetProperty(rule.PropertyName).PropertyType;
            ExpressionType binaryExpressionType;

            if (ExpressionType.TryParse(rule.Operand, out binaryExpressionType))
            {
                var right = Expression.Constant(Convert.ChangeType(rule.TargetValue, propertyType));
                return Expression.MakeBinary(binaryExpressionType, left, right);
            }
            else
            {
                var methodName = propertyType.GetMethod(rule.Operand);
                var parameterType = methodName.GetParameters()[0].ParameterType;
                var right = Expression.Constant(Convert.ChangeType(rule.TargetValue, parameterType));
                return Expression.Call(left, methodName, right);
            }
        }
    }
}

A Rule would be a class to hold the definition from the database.

using System;
using System.Collections.Generic;
using System.Linq;

namespace RulesEngine.Model
{
    public class Rule
    {
        public string PropertyName { get; private set; }
        public string Operand { get; private set; }
        public string TargetValue { get; private set; }

        public Rule(string propertyName, string operand, string targetValue)
        {
            PropertyName = propertyName;
            Operand = operand;
            TargetValue = targetValue;
        }
    }
}

Now we create an object to hold the instance data. (Replace foo with your threshold instance data).

public class Foo
{
    public string Name { get; private set; }
    public int Weight { get; private set; }
    public int Size { get; private set; }

    public Foo(string name, int weight, int size)
    {
        Name = name;
        Weight = weight;
        Size = size;
    }
}

And finally putting it all together...comparing instance data with the rule.

private static void RunExample()
        {
            var rule = new Rule("Weight","Equal", "100");
            var foo = new Foo("FooName", 100, 200);


            var expressionBuilder = new ExpressionBuilder();
            Func<Foo, bool> fooRule = expressionBuilder.Compile<Foo>(rule, foo.GetType());

            Console.WriteLine("Is weight equal to 100 for foo?" + fooRule(foo)); //true
        }

Since it's generic you can use it as a mini rules engine that can take any instance data and compare it against a set of rules to return true/false.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.