Class Query

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a SQL SELECT query with support for filtering, joining, grouping, ordering, and unions.

public class Query : ISelectItem, IFromItem, IElement

Inheritance: object

Query

Implements: ISelectItem

IFromItem

IElement

Examples

Basic query with filtering and ordering:

Query query = new Query();
query.AddFromItem(usersTable);
query.AddSelectItem(usersTable["username"]);
query.AddSelectItem(usersTable["email"]);
query.AddSelectItem(usersTable["age"]);
query.AddFilter(usersTable["status"], "active");
query.AddFilter(usersTable["age"], ConditionalType.GreaterThan, 18);
query.AddOrderClause(new OrderClause(usersTable["username"], OrderClauseType.Ascending));

using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("username"));
    }
}

Fluent API for query building:

Query query = Query.Create()
    .From(usersTable)
    .Select(usersTable["username"])
    .Select(usersTable["email"])
    .Where(new Criterion<string>(usersTable["status"], "active"))
    .OrderBy(usersTable["username"]);

using (ResultSet results = manager.Retrieve(query))
{
    // Process results
}

Query with JOIN:

Join join = new Join(ordersTable, usersTable, "user_id", JoinType.Inner);

Query query = new Query();
query.AddFromItem(join);
query.AddSelectItem(usersTable["username"]);
query.AddSelectItem(ordersTable["order_date"]);
query.AddSelectItem(ordersTable["total"]);
query.AddFilter(ordersTable["total"], ConditionalType.GreaterThan, 100m);

// SQL: SELECT users.username, orders.order_date, orders.total
//      FROM orders
//      INNER JOIN users ON orders.user_id = users.user_id
//      WHERE orders.total > 100

Query with GROUP BY and aggregate functions:

Query query = new Query();
query.AddFromItem(ordersTable);
query.AddSelectItem(ordersTable["user_id"]);

Function countFunc = new Function(FunctionType.Count, ordersTable["id"]);
countFunc.SetAlias("order_count");
query.AddSelectItem(countFunc);

Function sumFunc = new Function(FunctionType.Sum, ordersTable["total"]);
sumFunc.SetAlias("total_spent");
query.AddSelectItem(sumFunc);

query.AddGroupByItem(ordersTable["user_id"]);

Criterion<int> havingCriterion = new Criterion<int>(
    countFunc,
    ConditionalType.GreaterThan,
    5
);
query.SetHavingFilter(new Filter(havingCriterion));

// SQL: SELECT user_id, COUNT(id) AS order_count, SUM(total) AS total_spent
//      FROM orders
//      GROUP BY user_id
//      HAVING COUNT(id) > 5

Subquery in filter:

// Find users with above-average age
Query avgAgeQuery = new Query();
avgAgeQuery.AddFromItem(usersTable);
avgAgeQuery.AddSelectItem(new Function(FunctionType.Avg, usersTable["age"]));

Criterion<Query> ageCriterion = new Criterion<Query>(
    usersTable["age"],
    ConditionalType.GreaterThan,
    avgAgeQuery
);

Query mainQuery = new Query();
mainQuery.AddFromItem(usersTable);
mainQuery.AddSelectItem(usersTable["username"]);
mainQuery.AddSelectItem(usersTable["age"]);
mainQuery.SetWhereFilter(new Filter(ageCriterion));

// SQL: SELECT username, age FROM users
//      WHERE age > (SELECT AVG(age) FROM users)

Remarks

Query provides a fluent, type-safe API for building SELECT statements that work across all supported database systems. Queries can be used for data retrieval (Manager.Retrieve or Manager.Search), subqueries in filters, or subqueries in record field values.

Table of Contents

Class Query

Examples

Remarks

Constructors

Fields

Properties

Methods