Apply rules to data

In my (C# + SQL Server) application, the user will be able to define rules over data such as:

ITEM_ID = 1 
OR (ITEM_NAME LIKE 'something' AND ITEM_PRICE > 123 
AND (ITEM_WEIGHT = 456 OR ITEM_HEIGHT < 789))

The set of items to validate will always be different but they are not a huge number. However, the number of rules is high (let's say, 100000).

How can I check which ru开发者_如何学JAVAles validated (considering also into account performance) a given set of numbers?

This looks like your "rules" or conditions should be performed in C# instead.

If you are really going to feed 100,000 ORs and ANDs into the WHERE clause of your SQL statement, you are going to have a very hard time scaling your application. I can only imagine the mess of indexes you would have to have to have any arbitrary set of 100,000 conditions be applied to the data set and every permutation perform well.

Instead, I would run a basic select query and read each row and filter it in C# instead. Then you can track which conditions/rules do and don't pass for each row by applying each rule individually and tracking pass/fail.

Of course, if you are querying a very large table, then performance could become an issue, but you stated that "The set of items to validate ... are not a huge number" so I assume it would be relatively quick to bring back all the data for the table and perform your rules in code, or apply some fundamental filtering up front, then more specific filtering back in code.

Out of curiosity, how are the users entering these "rules", like:

ITEM_ID = 1
OR (ITEM_NAME LIKE 'something' AND ITEM_PRICE > 123
AND (ITEM_WEIGHT = 456 OR ITEM_HEIGHT < 789))

Please please please tell me they aren't entering actual SQL queries (in text form) and you are just appending them together, like:

var sql = "select * from myTable where ";
foreach(var rule in rules)
    sql += rule;

Maybe some kind of rule-builder UI that builds up these SQL-looking statements?

You could use some of Microsoft's own parsing engine for T-SQL.

You can find them in the assemblies Microsoft.Data.Schema.ScriptDom.dll and Microsoft.Data.Schema.ScriptDom.Sql.dll.

TSql100Parser parser = new TSql100Parser(false);

IList<ParseError> errors;
Expression expr = parser.ParseBooleanExpression(
        new StringReader(condition),
        out errors
    );

if (errors != null && errors.Count > 0)
{
    // Error handling
    return;
}

If you don't get any errors, the string is a valid filter expression. Though there might be some harmful expressions.

If you wish, you could run the expression trough your own visitor to detect any unwanted constructs (such as sub-queries). But be aware that you would have to override almost all of the 650 overloads, for both Visit(...) and ExplicitVisit(...). Partial classes would be good here.

When you are satisfied, could then build a complete SELECT statement, with all of the expressions:

var schemaObject = new SchemaObjectName();
schemaObject.Identifiers.Add(new Identifier {Value = "MyTable"});

var queryExpression = new QuerySpecification();
queryExpression.FromClauses.Add(
    new SchemaObjectTableSource {SchemaObject = schemaObject});

// Add the expression from before (repeat as necessary) 
Literal zeroLiteral = new Literal
{
    LiteralType = LiteralType.Integer,
    Value = "0",
};
Literal oneLiteral = new Literal
{
    LiteralType = LiteralType.Integer,
    Value = "1",
};
WhenClause whenClause = new WhenClause
{
    WhenExpression = expr, // <-- here
    ThenExpression = oneLiteral,
};
CaseExpression caseExpression = new CaseExpression
{
    ElseExpression = zeroLiteral,
};
caseExpression.WhenClauses.Add(whenClause);
queryExpression.SelectElements.Add(caseExpression);

var selectStatement = new SelectStatement {QueryExpression = queryExpression};

... and turn it all back into a string:

var generator = new Sql100ScriptGenerator();
string query;
generator.GenerateScript(selectStatement, out query);

Console.WriteLine(query);

Output:

SELECT CASE WHEN ITEM_ID = 1
                 OR (ITEM_NAME LIKE 'something'
                     AND ITEM_PRICE > 123
                     AND (ITEM_WEIGHT = 456
                          OR ITEM_HEIGHT < 789)) THEN 1 ELSE 0 END
FROM   MyTable

If this expression gets too large to handle, you could always split up the rules into chunks, to run a few at the time.

Though, to be allowed to redistribute the Microsoft.Data.Schema.ScriptDom.*.dll files, you have to own a licence of Visual Studio Team System (Is this included in at least VS Pro/Ultimate?)

Link: http://blogs.msdn.com/b/gertd/archive/2008/08/22/redist.aspx