.NET Core项目SQL存储公式的前后端计算实现方案问询

Great question! Let's tackle this with optimized C# code, a matching JavaScript implementation, and key best practices to handle your 26+ variables smoothly.

Optimized C# Implementation #

Your initial modular approach is solid, but we can refine it for better performance, type safety, and maintainability—especially with 26 variables. Here's an improved version:

Step 1: Use a Dictionary for Faster Variable Lookup #

Instead of a List<varKeyDto>, a Dictionary<string, decimal> gives you O(1) lookup time, which is far more efficient when dealing with many variables. We'll also use strongly typed numeric values instead of strings to avoid parsing errors later.

Step 2: Regex for Clean Variable Replacement #

Replacing each variable individually with string.Replace works, but a regex can handle all variables in one pass, making the code cleaner and more concise.

Full Optimized Code #

// Optional: A simple class to hold variable-value pairs (or use Dictionary directly)
public class VariableValue
{
    public string Name { get; set; }
    public decimal Value { get; set; }
}

public class FormulaCalculator
{
    public string EvaluateFormula(string formula, Dictionary<string, decimal> variables)
    {
        try
        {
            // Replace all {Variable} placeholders with their numeric values
            var parsedFormula = Regex.Replace(formula, @"\{(\w+)\}", match => 
                variables.TryGetValue(match.Groups[1].Value, out var value) 
                    ? value.ToString() 
                    : throw new KeyNotFoundException($"Variable {match.Groups[1].Value} not found"));

            // Use DataTable.Compute to evaluate the arithmetic expression
            var result = new DataTable().Compute(parsedFormula, null);
            return result.ToString();
        }
        catch (Exception ex)
        {
            // Handle errors (invalid formula, missing variables, etc.)
            return $"Error: {ex.Message}";
        }
    }

    // Example usage
    public void TestCalculation()
    {
        var variables = new Dictionary<string, decimal>
        {
            {"H", 150},
            {"VW", 200},
            {"FA", 20}
        };

        string formula = "{H}+({FA}*2)+{VW}";
        string result = EvaluateFormula(formula, variables);
        Console.WriteLine(result); // Output: 490
    }
}

Key Improvements: #

• Performance: Dictionary lookup is faster than iterating a List for each replacement, especially with 26 variables.
• Type Safety: Using decimal ensures no string-to-number conversion errors mid-calculation, critical for precision-sensitive use cases.
• Error Handling: Explicitly catches missing variables and formula syntax errors, making debugging easier.
• Cleaner Replacement: Regex handles all placeholders in one go instead of multiple repetitive Replace calls.

JavaScript Implementation #

To mirror the C# functionality in JavaScript, we'll follow the same core logic: replace variable placeholders and evaluate the expression. Important note: Only use this if your formulas are trusted (e.g., stored in your own SQL, not user-generated)—eval and Function can pose security risks with untrusted input.

Full JavaScript Code #

function evaluateFormula(formula, variables) {
    try {
        // Replace {Variable} placeholders with their numeric values
        const parsedFormula = formula.replace(/\{(\w+)\}/g, (match, variableName) => {
            if (variables.hasOwnProperty(variableName)) {
                return variables[variableName];
            }
            throw new Error(`Variable ${variableName} not found`);
        });

        // Evaluate the parsed formula (safe only for trusted formulas!)
        const result = eval(parsedFormula);
        return result.toString();
    } catch (error) {
        return `Error: ${error.message}`;
    }
}

// Example usage
const variables = {
    H: 150,
    VW: 200,
    FA: 20
};

const formula = "{H}+({FA}*2)+{VW}";
const result = evaluateFormula(formula, variables);
console.log(result); // Output: 490

Security Note #

If you need to handle untrusted formulas (e.g., user-defined), avoid eval entirely. Instead, use a dedicated arithmetic expression parser library that safely evaluates expressions without exposing security vulnerabilities.

Key Best Practices #

1. SQL Storage: Store formulas as plain text (e.g., NVARCHAR in SQL Server) using the {Variable} placeholder format—it's easy to parse in both C# and JavaScript.
2. Variable Naming: Stick to a consistent convention (e.g., uppercase letters only) to avoid replacement conflicts and make formulas readable.
3. Pre-Validation: Before evaluating, validate that all required variables are present and that the formula has valid arithmetic syntax.
4. Precision: Use decimal in C# and Number (or BigInt for extremely large values) in JavaScript to maintain calculation precision, especially for financial or scientific use cases.

内容的提问来源于stack exchange，提问作者emroo