Query API

The Quino data driver includes a powerful query API defined as a .NET API based on the IQuery type. The examples below show how to use this API to formulate type-safe queries that can be mapped to the database.

Mapped vs. Local

The querying API allows a product to declare its request in a normalized form so that the data driver can map it to an SQL that the underlying database understands.

Optimally, everything that the product requests can be mapped to the database query language and therefore executed on the database rather than in local code.

As we'll see in the example below (and in the Expressions chapter, it is possible to include query components that cannot be mapped to the database.

In these cases, the data driver maps what it can and then uses .NET Linq to apply what could not be mapped locally in the product itself. See Performance implications for more information.

Creating a query

The following example shows the easiest way to create a query:

var query = Session.CreateQuery<Person>();

The example above uses a generated class to select the type of object on which to base the query, but a product can also use several overloads, as in the following example, which creates the same query as above, but passing an IMetaClass instead.

var query = Session.CreateQuery(PunchclockModel.Person.Metadata);

While the two queries above address all entities of a given type, the following example creates a query for the time-entries of a single person.

var query = Session.CreateQuery(person, PunchclockModel.Person.TimeEntries);

All of the queries above can be used with other APIs, like GetList(), Delete(), or CreateCursor().

Lists

In the example below, we obtain a list for the simple query defined above.

var query = Session
  .CreateQuery<Person>()
  .Where(PunchclockModel.Person.LastName, "Smith");

var list = Session.GetList<Person>(query);

foreach (var person in list) { /* */ }

GetList() returns an IDataList<T>, which has its own query. Instead of creating a query first, as above, a product can create the list directly and manipulate the query afterward.

var list = Session.GetList<Person>();

list.Query.Where(PunchclockModel.Person.LastName, "Smith");

foreach (var person in list) { /* */ }

There is also a fluent API that lets you adjust the query while creating the list.

var list = Session.GetList<Person>(
  q => q.Where(PunchclockModel.Person.LastName, "Smith")
);

foreach (var person in list) { /* */ }

Cursors

By definition, a list loads all data into memory at once. To avoid this, a product can use a cursor. A cursor returns an IDataCursor<T>, which is an IEnumerable<T> instead of an IDataList<T> (which extends IList<T>).

using (var cursor = Session.CreateCursor<Person>())
{
  foreach (var person in cursor) { /* */ }
}

There is also a fluent API that adjusts the query while creating the cursor.

using (var cursor = Session.CreateCursor<Person>(q => q.Where(PunchclockModel.Person.LastName, "Smith")))
{
  foreach (var person in cursor) { /* */ }
}

Count

A product can obtain the count matching a query.

var count = Session.GetCount<Person>();

There is also a fluent API that adjusts the query before getting the count.

var count = Session.GetCount<Person>(
  q => q.Where(PunchclockModel.Person.LastName, "Smith")
);

Restrictions

At its core, a restriction is an IExpression. As we already saw in the examples above, a product generally uses a rich API of extension methods to apply restrictions.

The examples below use the generated metadata to reference properties from the model. This way of building expressions is statically typed, so it will survive refactoring. When something changes in the generated code, the referenced constants will no longer be available and the build will fail. See the end of this section for an example that uses the Expressions language.

An IQuery or IQueryTable supports many overloads of Where, such as the following query that finds all people with a certain last name:

var query = Session
  .CreateQuery<Person>()
  .WhereEquals(PunchclockModel.Person.LastName, "Smith")
  .Query;

The method above is a shortcut that assumes the equality operator. The underlying method accepts any operator, such as the following query that finds all people whose last name starts with "S" or "s":

var query = Session
  .CreateQuery<Person>()
  .Where(
    PunchclockModel.Person.LastName,
    ExpressionOperator.BeginsWithCI,
    "S"
  ).Query;

Most of these Where() methods wrap a CreateExpression() method so that a product can compose expressions in more complex ways.

The next example shows how to test for the condition above or another, similar condition.

var query = Session.CreateQuery<Person>();
var startsWithS = query.CreateExpression(
  PunchclockModel.Person.LastName,
  ExpressionOperator.BeginsWithCI,
  "S"
);
var startsWithT = query.CreateExpression(
  PunchclockModel.Person.LastName,
  ExpressionOperator.BeginsWithCI,
  "T"
);

query.Or(startsWithS, startsWithT);

The Or method takes either an IEnumerable<IExpression> or a variable list of IExpression parameters, so the pattern above can be arbitrarily extended with more expressions.

A product can also create the expression for Or instead of applying it directly. Here, we nest one Or inside of another.

var query = Session.CreateQuery<Person>();
var startsWithS = query.CreateExpression(
  PunchclockModel.Person.LastName,
  ExpressionOperator.BeginsWithCI,
  "S"
);
var startsWithT = query.CreateExpression(
  PunchclockModel.Person.LastName,
  ExpressionOperator.BeginsWithCI,
  "T"
);

var matchesLastName = query.CreateOr(startsWithS, startsWithT);

query.Or(
  query.CreateExpressionEquals(p.FirstName, "Bob"),
  matchesLastName
);

The previous query can also be formulated using the Expressions language:

query.Where("((LastName %~ 'S') || (LastName %~ 'T')) || FirstName == 'Bob'");

Though this may be clearer to read, unlike the other queries, it will not fail at compile-time if it is no longer valid. Instead, it will fail at run-time if anything changes in the model.

Sorts

Like a restriction, a sort has an IExpression that describes how to sort and a direction.

The following query orders by last name.

var query = Session
  .CreateQuery<Person>()
  .OrderBy(PunchclockModel.Person.LastName);

The following query orders by last name, but descending.

var query = Session
  .CreateQuery<Person>()
  .OrderByDescending(PunchclockModel.Person.LastName);

Joins

A product can join related data in order to restrict on or sort by data in sub-objects.

For example, the following query sorts people by the company name.

var query = Session
  .CreateQuery<Person>()
  .Join(PunchclockModel.Person.Company)
    .OrderBy(PunchclockModel.Company.Name)
  .Query;

Joins can be nested as well. The following query sorts people by the company zip-code.

var query = Session
  .CreateQuery<Person>()
  .Join(PunchclockModel.Person.Company)
    .Join(PunchclockModel.Company.Address)
      .OrderBy(PunchclockModel.Address.ZipCode)
  .Query;

A query can also join multiple tables. The following query extends the previous example to return only people with salaries above a certain level.

var query = Session
  .CreateQuery<Person>()
  .Join(PunchclockModel.Person.Company)
    .Join(PunchclockModel.Company.Address)
      .OrderBy(PunchclockModel.Address.ZipCode)
  .Query
  .Join(PunchclockModel.Person.Profile)
    .Where(
      PunchclockModel.Profile.Salary, 
      ExpressionOperator.GreaterThanEqual
      minSalary
    )
  .Query;

Join Condition

The JoinCondition is also available for restriction. The mapper creates the default join condition for the relation between the joined classes. The following example extends the join condition rather than introducing restrictions on the table.

var query = Session
  .CreateQuery<Person>()
  .Join(PunchclockModel.Person.Company)
    .OrderBy(PunchclockModel.Company.Name)
    .JoinCondition
      .Where(
        PunchclockModel.Company.Name,
        ExpressionOperator.ContainsCI,
        "AG"
      )
  .Query;

Join Type

TODO Document the various join types and how to use them

• All

• WhereExists

• WhereNotExists

Aliases

TODO Describe how to use aliases to resolve ambiguities when a product joins the same relation multiple times

Distinct

An unjoined query will already be distinct. However, it is possible for a query with joined 1-n relations to return the same object multiple times. To avoid this, a product can make the query "distinct".

var query = Session
  .CreateQuery<Person>()
  .SetDistinct()
  .Join(PunchclockModel.Person.TimeEntries)
    // ... restrictions
  .Query;

Grouping

There is no support for grouping.

A product can set IQuery.Distinct to get only unique objects.

A product can use either custom SQL or retrieve data with Quino and group locally with .NET Linq.

Aggregation

There is no support for aggregation.

A product can use either custom SQL or retrieve data with Quino and apply aggregations locally with .NET Linq.

Custom SQL

Quino provides several ways of integrating SQL into a query.

• ICustomCommandBuilder: A high-level API to build custom/parameterized SQL

• IQuery.CustomCommandText: A low-level API to replace or extend parts of a query

• Using ADO: Get access to the underlying ADO driver for the database

  • DevOps -> Quino 10 -> Developer Documentation -> Data -> ADO

Integration with Metadata

The examples above show how to build queries and obtain data in business logic. A product can use use this API into metadata using IQueryAspects.

Custom Data

A query has IQuery.CustomData, a dictionary of data that a product can add to a query and then use from IQueryAspect objects in the model. Quino's TimelineAspect uses custom data to determine how to restrict data that rests on a timeline.

TODO Include example of setting and reading custom data (using the timeline)

TODO Describe how to override the query factory to include custom data by default

Performance Implications

While very convenient, even essential, this behavior has performance implications. A product should be aware of how a restriction or sorting could affect the performance of a query.

Performance Restrictions

For example, suppose a product wants to get all active people.

var query = Session
  .CreateQuery<Person>()
  .WhereEquals(PunchclockModel.Person.Active, true)
  .Query;

The data driver can map this entire query to SQL, for optimal performance.

Now suppose that we rewrite the restriction as C#, as shown below.

var query = Session
  .CreateQuery<Person>()
  .Where(query.ExpressionFactory.CreateDelegate<Person>(p => p.Active))
  .Query;

In this case, the driver cannot map any restrictions to the database, so it must get all people from the database and filter them locally. Even if the product asks for a list matching this query, the driver does its best to avoid loading everything into memory at once, but must still create all the people in order to filter them.

With restrictions, it suffices to include another restriction in order to limit the performance impact. For example, the speed of the query in the next example is not really impacted.

var query = Session
  .CreateQuery<Person>()
  .WhereEquals(PunchclockModel.Person.LastName, "Smith")
  .Where(query.ExpressionFactory.CreateDelegate<Person>(p => p.Active))
  .Query;

In this case, the relatively restrictive restriction to the last name will drastically reduce the amount of data returned by the database.

Sorts Performance

Similarly, if a product uses a sort that cannot be mapped to the database, the driver must retrieve all matching data and perform the sort (and any subsequent sorts) locally.