Expressions

This guide covers creating calculated fields using expressions and arithmetic operations with the fluent query syntax.

Overview

Expressions allow you to create calculated fields that don't exist as columns in your database. Velocity provides powerful expression capabilities through:

Expression - Container for calculated fields
ArithmeticOperator - Mathematical operations
ArithmeticType - Operation types (+, -, *, /)
Literal<T> - Constant values in expressions

Basic Expressions

Creating an Expression

Expressions consist of operands (columns or literals) combined with operators:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

Table products = schema["products"];

// Calculate: price * 1.1 (10% markup)
Expression markupPrice = new Expression("markup_price", [
    products["price"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    new Literal<decimal>(1.1M)
]);

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"],
        markupPrice
    ])
    .From(products);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        string name = row.GetFieldString("product_name");
        decimal originalPrice = row.GetFieldDecimal("price");
        decimal markup = row.GetFieldDecimal("markup_price");

        Console.WriteLine($"{name}: ${originalPrice:F2} → ${markup:F2}");
    }
}

Generated SQL:

SELECT product_name, price, (price * 1.1) AS markup_price
FROM products

Arithmetic Operations

Available Operators

ArithmeticType enumeration provides mathematical operators:

ArithmeticType	Operator	Description	Example
`Add`	`+`	Addition	`price + tax`
`All`	`*`	Wildcard for `COUNT(*)`	`COUNT(*)`
`CloseBracket`	`)`	Closing parenthesis for grouping	`(a + b) * c`
`Divide`	`/`	Division	`total / count`
`Multiply`	`*`	Multiplication	`quantity * price`
`OpenBracket`	`(`	Opening parenthesis for grouping	`(a + b) * c`
`Subtract`	`-`	Subtraction	`revenue - cost`

Addition

Table order_items = schema["order_items"];

// Calculate total: unit_price + shipping
Expression totalWithShipping = new Expression("total_with_shipping", [
    order_items["unit_price"],
    new ArithmeticOperator(ArithmeticType.Add),
    order_items["shipping_cost"]
]);

Query query = new Query()
    .Select([
        order_items["product_name"],
        totalWithShipping
    ])
    .From(order_items);

Generated SQL:

SELECT product_name, (unit_price + shipping_cost) AS total_with_shipping
FROM order_items

Subtraction

Table sales = schema["sales"];

// Calculate profit: revenue - cost
Expression profit = new Expression("profit", [
    sales["revenue"],
    new ArithmeticOperator(ArithmeticType.Subtract),
    sales["cost"]
]);

Query query = new Query()
    .Select([
        sales["sale_id"],
        profit
    ])
    .From(sales);

Generated SQL:

SELECT sale_id, (revenue - cost) AS profit
FROM sales

Multiplication

Table order_items = schema["order_items"];

// Calculate line total: quantity * unit_price
Expression lineTotal = new Expression("line_total", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
]);

Query query = new Query()
    .Select([
        order_items["product_name"],
        order_items["quantity"],
        order_items["unit_price"],
        lineTotal
    ])
    .From(order_items);

Generated SQL:

SELECT product_name, quantity, unit_price, (quantity * unit_price) AS line_total
FROM order_items

Division

Table products = schema["products"];

// Calculate price per unit: total_price / package_quantity
Expression pricePerUnit = new Expression("price_per_unit", [
    products["total_price"],
    new ArithmeticOperator(ArithmeticType.Divide),
    products["package_quantity"]
]);

Query query = new Query()
    .Select([
        products["product_name"],
        pricePerUnit
    ])
    .From(products);

Generated SQL:

SELECT product_name, (total_price / package_quantity) AS price_per_unit
FROM products

Parentheses

Use OpenBracket and CloseBracket to control operator precedence:

Table order_items = schema["order_items"];

// Calculate: (quantity + bonus) * unit_price
Expression adjustedTotal = new Expression("adjusted_total", [
    new ArithmeticOperator(ArithmeticType.OpenBracket),
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Add),
    order_items["bonus"],
    new ArithmeticOperator(ArithmeticType.CloseBracket),
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
]);

Query query = new Query()
    .Select([
        order_items["product_name"],
        adjustedTotal
    ])
    .From(order_items);

Generated SQL:

SELECT product_name, ((quantity + bonus) * unit_price) AS adjusted_total
FROM order_items

Complex Arithmetic

Multiple Operations

Combine multiple operations in a single expression:

Table order_items = schema["order_items"];

// Calculate: (quantity * unit_price) * (1 - discount)
Expression discountedTotal = new Expression("discounted_total", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    new ArithmeticOperator(ArithmeticType.OpenBracket),
    new Literal<decimal>(1.0M),
    new ArithmeticOperator(ArithmeticType.Subtract),
    order_items["discount"],
    new ArithmeticOperator(ArithmeticType.CloseBracket)
]);

Query query = new Query()
    .Select([
        order_items["product_name"],
        discountedTotal
    ])
    .From(order_items);

Generated SQL:

SELECT product_name, (quantity * unit_price * (1.0 - discount)) AS discounted_total
FROM order_items

Nested Calculations

Table financial_data = schema["financial_data"];

// Calculate: (revenue - cost) / revenue * 100 (profit margin %)
Expression profitMargin = new Expression("profit_margin_pct", [
    financial_data["revenue"],
    new ArithmeticOperator(ArithmeticType.Subtract),
    financial_data["cost"],
    new ArithmeticOperator(ArithmeticType.Divide),
    financial_data["revenue"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    new Literal<decimal>(100M)
]);

Query query = new Query()
    .Select([
        financial_data["product_name"],
        profitMargin
    ])
    .From(financial_data);

Generated SQL:

SELECT product_name, ((revenue - cost) / revenue * 100) AS profit_margin_pct
FROM financial_data

Literals

Typed Literals

Use Literal<T> for constant values:

// Numeric literals
new Literal<int>(42)
new Literal<decimal>(3.14M)
new Literal<double>(2.718)

// String literals
new Literal<string>("Hello")
new Literal<string>(" ")

// Boolean literals
new Literal<bool>(true)
new Literal<bool>(false)

// Date literals
new Literal<DateTime>(DateTime.Now)
new Literal<DateOnly>(DateOnly.FromDateTime(DateTime.Now))

Literals in Expressions

Table products = schema["products"];

// Add fixed tax amount
Expression priceWithTax = new Expression("price_with_tax", [
    products["price"],
    new ArithmeticOperator(ArithmeticType.Add),
    new Literal<decimal>(5.00M) // Fixed $5 tax
]);

// Calculate percentage
Expression discountedPrice = new Expression("discounted_price", [
    products["price"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    new Literal<decimal>(0.85M) // 15% discount
]);

Expressions in WHERE Clauses

Use expressions in filter conditions:

Filter by Calculated Value

Table order_items = schema["order_items"];

// Calculate line total
Expression lineTotal = new Expression("line_total", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
]);

// Filter: line total > 100
Filter filter = new Filter(
    new Criterion<decimal>(
        lineTotal,
        ConditionalType.GreaterThan,
        100.00M
    )
);

Query query = new Query()
    .Select([
        order_items["product_name"],
        lineTotal
    ])
    .From(order_items)
    .Where(filter);

Generated SQL:

SELECT product_name, (quantity * unit_price) AS line_total
FROM order_items
WHERE (quantity * unit_price) > 100.00

Multiple Expression Filters

Table products = schema["products"];

// Profit margin expression
Expression profitMargin = new Expression("profit_margin", [
    products["price"],
    new ArithmeticOperator(ArithmeticType.Subtract),
    products["cost"],
    new ArithmeticOperator(ArithmeticType.Divide),
    products["price"]
]);

// Filter: profit margin > 0.3 (30%) AND price < 100
Filter filter = new Filter([
    new Criterion<decimal>(profitMargin, ConditionalType.GreaterThan, 0.3M),
    new BooleanItem(BooleanType.And),
    new Criterion<decimal>(products["price"], ConditionalType.LessThan, 100.00M)
]);

Query query = new Query()
    .Select([
        products["product_name"],
        profitMargin
    ])
    .From(products)
    .Where(filter);

Expressions with Aggregates

Combine expressions with aggregate functions:

Calculate Aggregates on Expressions

Table order_items = schema["order_items"];

// Sum of line totals
Expression lineTotalExpr = new Expression("line_total", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
]);

Expression totalRevenue = new Expression("total_revenue",
    new Aggregate(AggregateType.Sum, lineTotalExpr)
);

Query query = new Query()
    .Select([
        order_items["order_id"],
        totalRevenue
    ])
    .From(order_items)
    .GroupBy(order_items["order_id"]);

Generated SQL:

SELECT order_id, SUM(quantity * unit_price) AS total_revenue
FROM order_items
GROUP BY order_id

Average of Calculated Values

Table sales = schema["sales"];

// Average profit margin
Expression profitMargin = new Expression("profit_margin", [
    sales["revenue"],
    new ArithmeticOperator(ArithmeticType.Subtract),
    sales["cost"],
    new ArithmeticOperator(ArithmeticType.Divide),
    sales["revenue"]
]);

Expression avgMargin = new Expression("avg_profit_margin",
    new Aggregate(AggregateType.Average, profitMargin)
);

Query query = new Query()
    .Select([
        sales["product_category"],
        avgMargin
    ])
    .From(sales)
    .GroupBy(sales["product_category"]);

Complete Examples

Example 1: Sales Report with Calculations

Table order_items = schema["order_items"];

// Calculate multiple values
Expression lineTotal = new Expression("line_total", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
]);

Expression discountAmount = new Expression("discount_amount", [
    lineTotal,
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["discount_percent"]
]);

Expression finalTotal = new Expression("final_total", [
    lineTotal,
    new ArithmeticOperator(ArithmeticType.Subtract),
    discountAmount
]);

Query query = new Query()
    .Select([
        order_items["product_name"],
        order_items["quantity"],
        order_items["unit_price"],
        lineTotal,
        discountAmount,
        finalTotal
    ])
    .From(order_items);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    decimal grandTotal = 0;

    Console.WriteLine("Sales Report");
    Console.WriteLine("============");
    Console.WriteLine();

    foreach (Result row in results)
    {
        string product = row.GetFieldString("product_name");
        int qty = row.GetFieldInteger("quantity");
        decimal unitPrice = row.GetFieldDecimal("unit_price");
        decimal total = row.GetFieldDecimal("line_total");
        decimal discount = row.GetFieldDecimal("discount_amount");
        decimal final = row.GetFieldDecimal("final_total");

        Console.WriteLine($"{product}");
        Console.WriteLine($"  Quantity: {qty} × ${unitPrice:F2} = ${total:F2}");
        Console.WriteLine($"  Discount: -${discount:F2}");
        Console.WriteLine($"  Final: ${final:F2}");
        Console.WriteLine();

        grandTotal += final;
    }

    Console.WriteLine($"Grand Total: ${grandTotal:F2}");
}

Example 2: Tiered Discount Pricing

Table order_items = schema["order_items"];

// Calculate base total: quantity * unit_price
Expression baseTotal = new Expression("base_total", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"]
]);

// Calculate discount amount: base_total * discount_rate
Expression discountAmount = new Expression("discount_amount", [
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["discount_rate"]
]);

// Calculate final price: (quantity * unit_price) * (1 - discount_rate)
Expression finalPrice = new Expression("final_price", [
    new ArithmeticOperator(ArithmeticType.OpenBracket),
    order_items["quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    order_items["unit_price"],
    new ArithmeticOperator(ArithmeticType.CloseBracket),
    new ArithmeticOperator(ArithmeticType.Multiply),
    new ArithmeticOperator(ArithmeticType.OpenBracket),
    new Literal<decimal>(1.0M),
    new ArithmeticOperator(ArithmeticType.Subtract),
    order_items["discount_rate"],
    new ArithmeticOperator(ArithmeticType.CloseBracket)
]);

Query query = new Query()
    .Select([
        order_items["product_name"],
        baseTotal,
        discountAmount,
        finalPrice
    ])
    .From(order_items)
    .OrderBy(finalPrice, OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Tiered Discount Pricing");
    Console.WriteLine("=======================");

    foreach (Result row in results)
    {
        string product = row.GetFieldString("product_name");
        decimal base_ = row.GetFieldDecimal("base_total");
        decimal discount = row.GetFieldDecimal("discount_amount");
        decimal final = row.GetFieldDecimal("final_price");

        Console.WriteLine($"{product}");
        Console.WriteLine($"  Base: ${base_:F2}  Discount: -${discount:F2}  Final: ${final:F2}");
        Console.WriteLine();
    }
}

Generated SQL:

SELECT product_name,
       (quantity * unit_price) AS base_total,
       (quantity * unit_price * discount_rate) AS discount_amount,
       ((quantity * unit_price) * (1.0 - discount_rate)) AS final_price
FROM order_items
ORDER BY final_price DESC

Example 3: Inventory Value Report

Table products = schema["products"];

// Calculate inventory value and markup
Expression inventoryValue = new Expression("inventory_value", [
    products["stock_quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    products["cost"]
]);

Expression retailValue = new Expression("retail_value", [
    products["stock_quantity"],
    new ArithmeticOperator(ArithmeticType.Multiply),
    products["price"]
]);

Expression potentialProfit = new Expression("potential_profit", [
    retailValue,
    new ArithmeticOperator(ArithmeticType.Subtract),
    inventoryValue
]);

// Filter: only items in stock
Filter inStockFilter = new Filter(
    new Criterion<int>(
        products["stock_quantity"],
        ConditionalType.GreaterThan,
        0
    )
);

Query query = new Query()
    .Select([
        products["product_name"],
        products["stock_quantity"],
        inventoryValue,
        retailValue,
        potentialProfit
    ])
    .From(products)
    .Where(inStockFilter)
    .OrderBy(potentialProfit, OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    decimal totalCost = 0;
    decimal totalRetail = 0;

    Console.WriteLine("Inventory Value Report");
    Console.WriteLine("======================");
    Console.WriteLine();

    foreach (Result row in results)
    {
        string product = row.GetFieldString("product_name");
        int qty = row.GetFieldInteger("stock_quantity");
        decimal cost = row.GetFieldDecimal("inventory_value");
        decimal retail = row.GetFieldDecimal("retail_value");
        decimal profit = row.GetFieldDecimal("potential_profit");

        Console.WriteLine($"{product} ({qty} units)");
        Console.WriteLine($"  Cost Value: ${cost:N2}");
        Console.WriteLine($"  Retail Value: ${retail:N2}");
        Console.WriteLine($"  Potential Profit: ${profit:N2}");
        Console.WriteLine();

        totalCost += cost;
        totalRetail += retail;
    }

    Console.WriteLine("======================");
    Console.WriteLine($"Total Inventory Cost: ${totalCost:N2}");
    Console.WriteLine($"Total Retail Value: ${totalRetail:N2}");
    Console.WriteLine($"Total Potential Profit: ${(totalRetail - totalCost):N2}");
}

Best Practices

Name expressions clearly
- Use descriptive names that explain what is calculated
- Helps with code maintenance and debugging
Consider performance
- Expressions calculated for every row
- Complex calculations can slow queries
- Consider pre-calculating values when possible
Watch for division by zero
- Check for zero divisors in your data
- Use CASE statements via functions for safety
- Handle NULLs appropriately
Use appropriate data types
- Match literal types to column types
- Use decimal for currency calculations
- Avoid implicit type conversions
Parenthesize complex expressions
- Use ArithmeticType.OpenBracket and ArithmeticType.CloseBracket to control precedence
- Ensures correct operator precedence
- Makes expressions more maintainable
Test with real data
- Verify calculations with known values
- Check edge cases (NULL, zero, negative)
- Validate results match expectations
Document business logic
- Comment complex calculations
- Explain formulas and their purpose
- Makes code more understandable

Next Steps

Grouping and Aggregation - Use expressions with aggregates
Filtering - Filter by calculated values
Sorting - Sort by expressions

Table of Contents

Expressions

Overview

Basic Expressions

Creating an Expression

Arithmetic Operations

Available Operators

Addition

Subtraction

Multiplication

Division

Parentheses

Complex Arithmetic

Multiple Operations

Nested Calculations

Literals

Typed Literals

Literals in Expressions

Expressions in WHERE Clauses

Filter by Calculated Value

Multiple Expression Filters

Expressions with Aggregates

Calculate Aggregates on Expressions

Average of Calculated Values

Complete Examples

Example 1: Sales Report with Calculations

Example 2: Tiered Discount Pricing

Example 3: Inventory Value Report

Best Practices

Next Steps