Class DataBlockQueryBuilder

Namespace

Datafication.Server.Core.Query

Assembly

Datafication.Server.Core.dll

Fluent builder for creating DataBlockQueryRequest objects. Provides a type-safe, discoverable API for constructing complex queries.

public class DataBlockQueryBuilder

Inheritance

object

DataBlockQueryBuilder

Examples

var query = new DataBlockQueryBuilder()
    .Select("Name", "Age", "Salary")
    .Where("Age", QueryOperator.GreaterThan, 25)
    .WhereIn("Status", "Active", "Pending")
    .OrderBy("Salary", descending: true)
    .Skip(0).Take(100)
    .Build();

Methods

Aggregate(string, params string[])

Applies an aggregation function to the specified columns.

public DataBlockQueryBuilder Aggregate(string type, params string[] columns)

Parameters

type string

The aggregation type (min, max, mean, sum, count, stddev, variance, percentile).

columns string[]

The columns to aggregate. If empty, all numeric columns are used.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Build()

Builds the DataBlockQueryRequest from the configured options.

public DataBlockQueryRequest Build()

Returns

DataBlockQueryRequest

A new DataBlockQueryRequest instance.

Compute(string, string)

Adds a computed column based on an expression.

public DataBlockQueryBuilder Compute(string resultColumn, string expression)

Parameters

resultColumn string

The name for the new column.

expression string

The expression to evaluate. Use [ColumnName] to reference columns.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Examples

builder.Compute("Total", "[Price] * [Quantity]");

Count()

Counts the rows.

public DataBlockQueryBuilder Count()

Returns

DataBlockQueryBuilder

The builder for method chaining.

CumulativeSum(string, string?)

Adds a cumulative sum column.

public DataBlockQueryBuilder CumulativeSum(string column, string? resultColumn = null)

Parameters

column string

The source column.

resultColumn string

The name for the result column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

DropDuplicates(string[]?, string)

Removes duplicate rows.

public DataBlockQueryBuilder DropDuplicates(string[]? columns = null, string keep = "first")

Parameters

columns string[]

The columns to check for duplicates. If empty, all columns are used.

keep string

Which duplicate to keep: "first", "last", or "none".

Returns

DataBlockQueryBuilder

The builder for method chaining.

DropNulls(string)

Removes rows containing null values.

public DataBlockQueryBuilder DropNulls(string mode = "any")

Parameters

mode string

"any" to drop rows with any nulls, "all" to drop only rows where all values are null.

Returns

DataBlockQueryBuilder

The builder for method chaining.

FillNulls(string, object?, params string[])

Fills null values using the specified method.

public DataBlockQueryBuilder FillNulls(string method, object? constantValue = null, params string[] columns)

Parameters

method string

The fill method: "forward", "backward", "constant", "mean", "median", "mode", "interpolate".

constantValue object

The value to use when method is "constant".

columns string[]

The columns to fill. If empty, applies to all columns.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Format(string)

Sets the output format.

public DataBlockQueryBuilder Format(string format)

Parameters

format string

The format: "json", "csv", "html", or a custom sink name.

Returns

DataBlockQueryBuilder

The builder for method chaining.

FullJoin(string, string)

Performs a full outer join with another DataBlock.

public DataBlockQueryBuilder FullJoin(string otherDataBlockId, string keyColumn)

Parameters

otherDataBlockId string

The ID of the DataBlock to join with.

keyColumn string

The key column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

GroupBy(string, Dictionary<string, string>?, Dictionary<string, string>?)

Groups the data by the specified column with optional aggregations.

public DataBlockQueryBuilder GroupBy(string column, Dictionary<string, string>? aggregations = null, Dictionary<string, string>? resultColumnNames = null)

Parameters

column string

The column to group by.

aggregations Dictionary<string, string>

Dictionary of column name to aggregation type.

resultColumnNames Dictionary<string, string>

Optional custom names for result columns.

Returns

DataBlockQueryBuilder

The builder for method chaining.

InnerJoin(string, string)

Performs an inner join with another DataBlock.

public DataBlockQueryBuilder InnerJoin(string otherDataBlockId, string keyColumn)

Parameters

otherDataBlockId string

The ID of the DataBlock to join with.

keyColumn string

The key column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Lag(string, int, object?, string?)

Adds a lag column (previous row's value).

public DataBlockQueryBuilder Lag(string column, int offset = 1, object? defaultValue = null, string? resultColumn = null)

Parameters

column string

The source column.

offset int

The number of rows to look back (default: 1).

defaultValue object

The value to use when there is no previous row.

resultColumn string

The name for the result column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Lead(string, int, object?, string?)

Adds a lead column (next row's value).

public DataBlockQueryBuilder Lead(string column, int offset = 1, object? defaultValue = null, string? resultColumn = null)

Parameters

column string

The source column.

offset int

The number of rows to look ahead (default: 1).

defaultValue object

The value to use when there is no next row.

resultColumn string

The name for the result column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

LeftJoin(string, string)

Performs a left join with another DataBlock.

public DataBlockQueryBuilder LeftJoin(string otherDataBlockId, string keyColumn)

Parameters

otherDataBlockId string

The ID of the DataBlock to join with.

keyColumn string

The key column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Max(params string[])

Finds the maximum value in the specified columns.

public DataBlockQueryBuilder Max(params string[] columns)

Parameters

columns string[]

The columns to find maximum values.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Mean(params string[])

Calculates the mean (average) of the specified columns.

public DataBlockQueryBuilder Mean(params string[] columns)

Parameters

columns string[]

The columns to average.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Melt(string[], string, string)

Melts (unpivots) the DataBlock from wide to long format.

public DataBlockQueryBuilder Melt(string[] fixedColumns, string meltedColumnName = "variable", string meltedValueName = "value")

Parameters

fixedColumns string[]

The columns to keep fixed (identifier columns).

meltedColumnName string

The name for the column containing original column names.

meltedValueName string

The name for the column containing values.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Merge(string, string, string?, string)

Merges (joins) with another DataBlock.

public DataBlockQueryBuilder Merge(string otherDataBlockId, string keyColumn, string? otherKeyColumn = null, string mode = "inner")

Parameters

otherDataBlockId string

The ID of the DataBlock to merge with.

keyColumn string

The key column in the source DataBlock.

otherKeyColumn string

The key column in the other DataBlock. If null, uses keyColumn.

mode string

The merge mode: "inner", "left", "right", or "full".

Returns

DataBlockQueryBuilder

The builder for method chaining.

Min(params string[])

Finds the minimum value in the specified columns.

public DataBlockQueryBuilder Min(params string[] columns)

Parameters

columns string[]

The columns to find minimum values.

Returns

DataBlockQueryBuilder

The builder for method chaining.

MovingAverage(string, int, string?)

Adds a moving average column.

public DataBlockQueryBuilder MovingAverage(string column, int windowSize, string? resultColumn = null)

Parameters

column string

The source column.

windowSize int

The window size.

resultColumn string

The name for the result column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

OrderBy(string, bool)

Sorts the results by the specified column.

public DataBlockQueryBuilder OrderBy(string column, bool descending = false)

Parameters

column string

The column name to sort by.

descending bool

If true, sorts in descending order; otherwise ascending.

Returns

DataBlockQueryBuilder

The builder for method chaining.

OrderByDescending(string)

Sorts the results by the specified column in descending order.

public DataBlockQueryBuilder OrderByDescending(string column)

Parameters

column string

The column name to sort by.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Percentile(double, params string[])

Calculates the specified percentile of the specified columns.

public DataBlockQueryBuilder Percentile(double percentile, params string[] columns)

Parameters

percentile double

The percentile value (0-100).

columns string[]

The columns to calculate the percentile for.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Pivot(string, string, string, string, string)

Pivots the DataBlock from long format to wide format. Values from the pivot column become new columns, with values aggregated from the value column.

public DataBlockQueryBuilder Pivot(string indexColumn, string pivotColumn, string valueColumn, string aggregationType = "sum", string columnNameFormat = "{pivot}_{value}")

Parameters

indexColumn string

The column to use as row identifier.

pivotColumn string

The column whose unique values become new column names.

valueColumn string

The column containing values to aggregate.

aggregationType string

The aggregation type: "sum", "mean", "min", "max", "count", "stddev", "variance".

columnNameFormat string

Format string for generated column names. Use {pivot} and {value} as placeholders.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Examples

// Pivot sales data by region
builder.Pivot("Category", "Region", "Sales", "sum");
// Result: Category | East_Sales | West_Sales | ...

Pivot(string[], string, string, string, string)

Pivots the DataBlock from long format to wide format using multiple index columns. Values from the pivot column become new columns, with values aggregated from the value column.

public DataBlockQueryBuilder Pivot(string[] indexColumns, string pivotColumn, string valueColumn, string aggregationType = "sum", string columnNameFormat = "{pivot}_{value}")

Parameters

indexColumns string[]

The columns to use as row identifiers.

pivotColumn string

The column whose unique values become new column names.

valueColumn string

The column containing values to aggregate.

aggregationType string

The aggregation type: "sum", "mean", "min", "max", "count", "stddev", "variance".

columnNameFormat string

Format string for generated column names. Use {pivot} and {value} as placeholders.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Examples

// Pivot with multiple index columns
builder.Pivot(new[] { "Year", "Category" }, "Region", "Sales", "sum");

RightJoin(string, string)

Performs a right join with another DataBlock.

public DataBlockQueryBuilder RightJoin(string otherDataBlockId, string keyColumn)

Parameters

otherDataBlockId string

The ID of the DataBlock to join with.

keyColumn string

The key column.

Returns

DataBlockQueryBuilder

The builder for method chaining.

RowNumber(string, string?, string[]?)

Adds a row number column.

public DataBlockQueryBuilder RowNumber(string orderByColumn, string? resultColumn = null, string[]? partitionByColumns = null)

Parameters

orderByColumn string

The column to order by.

resultColumn string

The name for the result column.

partitionByColumns string[]

The columns to partition by.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Sample(int, int?)

Randomly samples the specified number of rows. When specified, Skip and Take are ignored.

public DataBlockQueryBuilder Sample(int count, int? seed = null)

Parameters

count int

The number of rows to sample.

seed int?

Optional seed for reproducible sampling.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Select(params string[])

Specifies which columns to include in the result.

public DataBlockQueryBuilder Select(params string[] columns)

Parameters

columns string[]

The column names to include.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Skip(int)

Skips the specified number of rows.

public DataBlockQueryBuilder Skip(int count)

Parameters

count int

The number of rows to skip.

Returns

DataBlockQueryBuilder

The builder for method chaining.

StandardDeviation(params string[])

Calculates the standard deviation of the specified columns.

public DataBlockQueryBuilder StandardDeviation(params string[] columns)

Parameters

columns string[]

The columns to calculate standard deviation for.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Sum(params string[])

Calculates the sum of the specified columns.

public DataBlockQueryBuilder Sum(params string[] columns)

Parameters

columns string[]

The columns to sum.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Take(int)

Takes the specified number of rows.

public DataBlockQueryBuilder Take(int count)

Parameters

count int

The maximum number of rows to return.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Transpose(string?)

Transposes the DataBlock (swaps rows and columns).

public DataBlockQueryBuilder Transpose(string? headerColumnName = null)

Parameters

headerColumnName string

The column to use as header row.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Variance(params string[])

Calculates the variance of the specified columns.

public DataBlockQueryBuilder Variance(params string[] columns)

Parameters

columns string[]

The columns to calculate variance for.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Where(string, QueryOperator, object?)

Adds a filter condition using the specified operator. Multiple Where calls are combined with AND logic.

public DataBlockQueryBuilder Where(string column, QueryOperator op, object? value)

Parameters

column string

The column name to filter on.

op QueryOperator

The comparison operator.

value object

The value to compare against.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereContains(string, string)

Adds a contains filter condition for string columns.

public DataBlockQueryBuilder WhereContains(string column, string value)

Parameters

column string

The column name to filter on.

value string

The substring to search for.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereEndsWith(string, string)

Adds an ends-with filter condition for string columns.

public DataBlockQueryBuilder WhereEndsWith(string column, string value)

Parameters

column string

The column name to filter on.

value string

The suffix to match.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereEquals(string, object?)

Adds an equality filter condition.

public DataBlockQueryBuilder WhereEquals(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to match.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereGreaterThan(string, object?)

Adds a greater-than filter condition.

public DataBlockQueryBuilder WhereGreaterThan(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to compare against.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereGreaterThanOrEqual(string, object?)

Adds a greater-than-or-equal filter condition.

public DataBlockQueryBuilder WhereGreaterThanOrEqual(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to compare against.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereIn(string, params object[])

Filters rows where the column value is in the specified collection.

public DataBlockQueryBuilder WhereIn(string column, params object[] values)

Parameters

column string

The column name to filter on.

values object[]

The values to include.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereLessThan(string, object?)

Adds a less-than filter condition.

public DataBlockQueryBuilder WhereLessThan(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to compare against.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereLessThanOrEqual(string, object?)

Adds a less-than-or-equal filter condition.

public DataBlockQueryBuilder WhereLessThanOrEqual(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to compare against.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereNot(string, object?)

Filters rows where the column value is not equal to the specified value.

public DataBlockQueryBuilder WhereNot(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to exclude.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereNotEquals(string, object?)

Adds a not-equals filter condition.

public DataBlockQueryBuilder WhereNotEquals(string column, object? value)

Parameters

column string

The column name to filter on.

value object

The value to exclude.

Returns

DataBlockQueryBuilder

The builder for method chaining.

WhereStartsWith(string, string)

Adds a starts-with filter condition for string columns.

public DataBlockQueryBuilder WhereStartsWith(string column, string value)

Parameters

column string

The column name to filter on.

value string

The prefix to match.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Window(string, string, int?, string?, string?, string[]?)

Applies a window function to create a new column.

public DataBlockQueryBuilder Window(string column, string function, int? windowSize = null, string? resultColumn = null, string? orderByColumn = null, string[]? partitionByColumns = null)

Parameters

column string

The source column.

function string

The window function type.

windowSize int?

The window size for moving functions.

resultColumn string

The name for the result column.

orderByColumn string

The column to order by within the window.

partitionByColumns string[]

The columns to partition by.

Returns

DataBlockQueryBuilder

The builder for method chaining.

Operators

implicit operator DataBlockQueryRequest(DataBlockQueryBuilder)

Implicitly converts the builder to a DataBlockQueryRequest.

public static implicit operator DataBlockQueryRequest(DataBlockQueryBuilder builder)

Parameters

builder DataBlockQueryBuilder

The builder to convert.

Returns

DataBlockQueryRequest