The tip shows how to evaluate user-defined or dynamic arithmetic formulas easily using the Compute method from the .NET Framework's System.Data assembly.
Introduction
Sometimes, there is a need to evaluate the result of a user-defined or dynamic arithmetic formula. This tip shows a very simple way to do that, without the need to build custom classes or lengthy code. It uses an (apparently) little known method of one of the .NET Framework's assemblies to do all the hard work for you.
Background
I had to develop a web application that, as one small part of its functionality, had to calculate a service cost based on just two variables (Mileage, and number of packages). The application supports multiple pricing options that are defined by the user, originally as Excel formulae. There are several standard formulae but we needed the option to support custom formulae entered by the user for specific customers, too. I'd not created this functionality before and searching threw up many custom-developed solutions, some very complex, and some offering far more functionality than needed. I also stumbled across references to an existing .NET method that appeared to do everything I needed, and on investigation, so it did. In fact, for all the formulae in use by the client, it was possible to pass the original Excel formulae in with very basic changes, allowing the users to continue using their familiar expressions.
Using the Code
The .NET Framework's System.Data
assembly includes the DataTable
object, which exposes a method called Compute
. This takes in a formula, evaluates it, and returns the result. All we need to do is to first substitute actual values for any variables, create a DataTable
object, and call the Compute
method passing in our substituted expression. Say we have a formula like:
IF([miles] < 51, 40, (([miles] - 50) * 0.24) + 40) + [miles] * 0.17
This is used for single packages only, and is expressing the following rules:
If mileage is 50 or less, charge a minimum of £40 + 17p per actual mile.
If mileage is 51 or more, charge £40 for the first 50 miles, plus 24p for mileage over 50 miles, plus 17p per actual mile (for fuel).
Note that the formula uses a named placeholder [miles] for our variable, and uses the Excel IF function.
We'll create a method called Evaluate
:
using System.Data;
public static double Evaluate(string expression)
{
expression = expression.ToUpper().Replace
("IIF(", "IF(").Replace("IF(","IIF(");
using (DataTable dt = new DataTable()) {
return (double)dt.Compute(expression, null);
}
}
Then we can call it as simply as:
double mileage = 60;
double result = Evaluate("IIF([miles]<51,40,(([miles]-50)*0.24)+40)+
[miles]*0.17".Replace("[miles]",mileage));
Note that our Evaluate
method converts the formula to upper case, and replaces any instance of Excel's IF
with the DataTable
's equivalent IIF
. You can omit this replacement if your users know to use the IIF
function. When we call the function, we need to replace any variable placeholders with the actual value. The example above just uses a single placeholder [miles]
but of course, it's trivial to replace additional placeholders if necessary.
Points of Interest
DataTable.Compute
takes a second string
parameter, Filter
. When called on an actual datatable this filters the rows used, but in our case, we can just use an empty datatable and ignore the filter, passing Null
for this parameter. You'll want to enclose your call to Evaluate
in a try... catch
block in case the formula is invalid.
For more information on the Compute
method, see https://msdn.microsoft.com/en-us/library/system.data.datatable.compute(v=vs.110).aspx.
History
- 22nd April, 2018: Initial version