Queries

There are two ways to query with Componyx.Data. Write SQL directly for complex or unusual queries, or chain the Fluent Syntax query builder for the straightforward cases, filtering, sorting and paging on one or two tables, where a builder reads more clearly than a SQL string. Both execute through the same underlying methods and both return parameterized commands.

Writing SQL directly

Build a command with CreateCommand, supply parameters with CreateParameter and execute it. This is the natural choice once a query needs subqueries, database-specific SQL or anything else that would be awkward to express through a chain of method calls.

var cmd = dal.CreateCommand(
    "SELECT * FROM Customer WHERE City = @City AND Active = @Active",
    new List<IDataParameter>
    {
        dal.CreateParameter("@City", "Rotterdam"),
        dal.CreateParameter("@Active", true)
    });

var customers = dal.Query<Customer>(cmd);
var customersAsync = await dal.QueryAsync<Customer>(cmd);

The parameter marker shown here, @, is SQL Server's. It depends on your database provider and is available on the DAL instance as ParameterMarker if you need to build parameter names for a different one.

Query<T>(cmd) maps the result onto T, including its relations, the same as a Fluent Syntax query does. For anything other than a mapped list, use:

Scalar<T>(cmd) / ScalarAsync<T>(cmd) - a single value.
NonQuery(cmd) / NonQueryAsync(cmd) - a command with no result set, the affected row count is returned.
QueryAsReader(cmd) / QueryAsReaderAsync(cmd) - a raw DbDataReader.
QueryAsDataSet(cmd) / QueryAsDataSetAsync(cmd) - a DataSet.

Fluent Syntax

Fluent Syntax starts from dal.Query<T>() and chains filter, sort and grouping methods from there. Each method appends a fixed SQL fragment, there is no expression tree to translate, so what you chain is close to what runs, the same predictability as writing the SQL by hand, with less of it to write for common cases.

Where

Where takes a property reference, either a lambda or a dot-separated property name for traversing relations, followed by a comparison method that supplies the condition and its value.

var customer = new Customer();

dal.Query<Customer>()
    .Where(() => customer.City).EqualTo("Rotterdam")
    .ToList();

Traverse a relation by chaining property expressions in a list:

var order = new Order();
var product = new Product();

dal.Query<Order>()
    .Where([() => order.Items, () => product.Name]).EqualTo("Widget")
    .ToList();

Comparison methods

Each follows a Where, And or Or call:

EqualTo, NotEqualTo
GreaterThan, GreaterThanOrEqualTo
LessThan, LessThanOrEqualTo
StartsWith, NotStartsWith
EndsWith, NotEndsWith
Contains, NotContains
Between, NotBetween
In, NotIn

Values passed to any comparison method are sent as query parameters, never concatenated into the SQL text. Comparing to null generates IS NULL or IS NOT NULL automatically.

And / Or and grouping

dal.Query<Customer>()
    .Where(() => customer.City).EqualTo("Rotterdam")
    .And(() => customer.Active).EqualTo(true)
    .ToList();

Where opens its own group by default, so chained And / Or calls stay together correctly without manual parentheses. Pass openGroups to And or Or and closeGroups to a comparison method, to build explicit nested groups for more complex conditions.

Join conditions

Automatic relation loading already generates joins for you, see Mapping. JoinOn adds an extra condition to a relation's existing join, rather than to the query's WHERE clause, when a relation should only load matching rows instead of all of them.

dal.Query<Customer>()
    .JoinOn(() => customer.Orders).Where(() => order.Status).EqualTo("Shipped")
    .ToList();

Ordering, grouping and distinct

dal.Query<Customer>()
    .OrderBy(() => customer.Name)
    .OrderBy(() => customer.City, descending: true)
    .ToList();

dal.Query<Customer>()
    .GroupBy(() => customer.City)
    .Distinct(() => customer.City)
    .ToList();

Paging

dal.Query<Customer>()
    .OrderBy(() => customer.Name)
    .Skip(20).Take(10)
    .ToList();

Set dal.PageSize once and call TakePage(pageIndex) instead of managing skip and take by hand. dal.TotalItemCount is populated after execution when paging is active.

The generated query depends on dal.SqlResultLimiter, matched to your database: ROW_NUMBER() OVER for SQL Server, ROWNUM for Oracle and LIMIT / LIMIT OFFSET for MySQL, PostgreSQL and SQLite.

Paging accounts for joined one-to-many relations. Limiting a joined result set directly would paginate by row rather than by object, a Customer with five Orders becomes five joined rows, so a naive page of 10 could return anywhere from two to ten distinct customers depending on where their orders happen to fall across the page boundary. Componyx.Data determines the distinct set of object keys first, applies the limit to that and only then joins the related data back in, so a page of 10 is always 10 distinct objects.

Mixing in raw SQL

SqlSelect, SqlWhere, SqlHaving and SqlOrderBy append raw SQL text to the corresponding clause of a Fluent Syntax query, for the cases in between, mostly a chain of methods, with one clause that needs raw SQL. Property names passed alongside the text are resolved to column names and substituted in.