Exploring the Query Object

Most advanced query operations are most easily achieved using LINQ.  However, it is sometimes necessary or preferable to use query objects.  This section summarises the features of the Query object.

Basic Querying Operations

The QueryExpression property specifies the criteria for the query – in effect the “where” clause.

The Order property controls how the results are ordered, and the Page property allows you to select a sub-range of records by their index in the sort order.

For full information about these properties, see the Basic Operations chapter.

Controlling Entity Load

The AggregateName property specifies a named aggregate to load.  An aggregate specifies a pattern of eager loads, allowing associations or blobs to be optionally loaded depending on a particular page or screen’s requirements.  See the Performance and Tuning chapter for details.

Projections

You can use the Projection collection to load a specific set of columns (or computed expressions).  When doing this you must call the IUnitOfWork.Project method instead of IUnitOfWork.Find.  The results are not materialised into entities and do not become part of the unit of work.  You can access the results via an ADO.NET IDataReader for raw access, or have them materialised into data objects using the IUnitOfWork.Project<T> overload.  The latter is similar to LINQ projections using the Select operator.

When performing a projection, you can also set the Distinct property to deduplicate the results.

Examples:

If you want to perform a single column projection which returns a primitive type or a string then you can use the Project<T> overload as shown below:

var query = new Query(typeof(Contribution));
query.Projection.Add("ContributorId");
var results = unitOfWork.Project<int>(query);

To deduplicate the results in the query above, we would use the Distinct property as shown below:

var query = new Query(typeof(Contribution));
query.Projection.Add("ContributorId");
query.Distinct = true;
var results = unitOfWork.Project<int>(query);

If we want to project more than one column to a known type, that type needs to be declared with properties that match the name of the projected columns we are going to be returning. An example with two properties is shown below but the same approach applies regardless of how many columns are involved in the projection.

struct MyProjectionResult
{
  public int ContributorId { get; set; }
  public int ApprovedById { get; set; }
}
var query = new Query(typeof(Contribution));
query.Projection.Add("ContributorId");
query.Projection.Add("ApprovedById");
var results = unitOfWork.Project<MyProjectionResult>(query);

If you require a custom mapping to be applied or don’t require object instances to be returned to you then you can use the Project() method which will return you a DataReader instance.

var query = new Query(typeof(Contribution));
query.Projection.Add("ContributorId");
query.Projection.Add("ApprovedById");
using (var reader = unitOfWork.Project(query))
{
  while (reader.Read())
  {
    // do work
  }
}

LightSpeed 5 includes improved support for conditionals in projections, such as HasValue.

Views

Specify the ViewName property to run the query against a view instead of against the entity’s normal backing table.  See Working with Database Views in the chapter Controlling the Database Mapping for details.

If you are using LINQ you can use the .WithViewName extension method

Controlling Aliasing

Aliasing for a LightSpeed query is specified through the Mappings property. The Mappings object allows you to specify a mapping between a type or query and a name. This alias is then either used automatically when LightSpeed builds the query, or you can use it when building up a query or a join to indicate which alias should be used when building the query. Managing aliasing manually should only be required for very precise control over queries as LightSpeed will generate the required aliasing automatically for all queries.

var query = new Query(typeof(Contribution));
query.Mappings.Add<Contribution>("t0");
query.Mappings.Add<Member>("t1");

One word of caution when manually specifying aliasing for a query is that the Mappings collection is used by LightSpeed to understand all of the types involved in the query, so if a mapping is added in to the collection but not subsequently used then a CROSS JOIN will be applied to join that table in to the query to ensure that any arbitrary criteria that may reference that aliased entry can be resolved.

Joins

Joins allow you to define expressions that will allow multiple entities to participate in a query. A Join as defined as part of a query is directly translated into a SQL join when LightSpeed translates a query into the underlying SQL statements required for the query.

LightSpeed allows you to define three types of joins:

·         Inner: The intersection between the two entity sets as intersecting on the supplied keys

·         Outer: All rows in the left hand entity set paired with corresponding rows in the right hand entity set or null if no corresponding join can be made. This conforms to the syntax of a LEFT OUTER JOIN in SQL.

·         CrossJoin: The Cartesian product of the two entity sets.

To specify a join, you must assign an instance of the Join class to the Join property. Multiple joins can be specified by use of the .And() method which is available on any Join instance. LightSpeed offers several static methods to allow for easy instantiation of Join instances according to the type required and allows for generic specification of the two entity types involved.

For more advanced scenarios such as joining against a sub-query there are additional overloads available. If you are interested in such scenarios please review the API documentation for the static Join instantiation methods named Inner() and Outer().

When performing a join, LightSpeed will automatically opt in all fields from the joined entity or query unless you have explicitly defined a projection for the query. The rational for this is that you are surfacing entities rather than individual columns. If your intention is to only make use of data from specific fields then we would strongly recommend you declare a set of appropriate projections to match the data you intend to use. Review the documentation in the Projections section in this document for more information on how to achieve this.

Examples:

To specify an Inner Join you can use the Inner<TLeft, TRight>() method as shown below:

var query = new Query(typeof(Contribution));
query.Join = Join.Inner<Contribution, Member>("ContributorId", "Id");

To specify an Outer Join you can use the Outer<TLeft, TRight>() method as shown below:

var query = new Query(typeof(Contribution));
query.Join = Join.Outer<Contribution, Member>("ApprovedById", "Id");

To specify a Cross Join you can use the CrossJoin<TLeft, TRight>() method as shown below:

var query = new Query(typeof(Contribution));
query.Join = Join.CrossJoin<Contribution, Member>();

If you need to specify multiple joins, you can use the .And() method to chain your join instances together. An example of this is shown below:

var query = new Query(typeof(Contribution));
query.Join = Join.CrossJoin<Contribution, Member>().And(
                   Join.Inner<Contribution, Comment>("Id", "ContributionId")
                   );

In advanced scenarios you may require LightSpeed to join against a sub-query, for example to surface specific projected fields from the sub-query while using it to filter or scope the top level query. The .Inner() and .Outer() methods provide an overload which allows you to join on another Query instance which will perform a join against that sub query of the type you have nominated. LightSpeed does not currently offer this with CrossJoins and these overloads are not available on the generic versions of these methods.

An example of how this might be used is shown below:

var innerQuery = new Query(typeof(Member), Entity.Attribute("Username") == "jb");
var query = new Query(typeof(Contribution));
query.Join = Join.Outer(typeof(Contribution), innerQuery, "ContributorId", "Id");

Grouping

The Grouping property allows you to declare a grouping which will be applied to the query which will be ultimately translated into a SQL GROUP BY statement.

A grouping is specified by assigning a Group instance to the Group property on the query object. The Group class provides a static method for instantiating Group instances and the Group class provides the .AndBy() method which allows you to specify multiple grouping columns.

If you are performing a grouping query you will always need to obtain your results using a call to Project or Project<T> as LightSpeed will by default only select back the columns which are being grouped on. You can alternatively specify additional columns to be selected by explicitly declaring a projection set for the query, note that if you do so you need to ensure you include the columns being grouped on as this will override the default behaviour. Specifying a Projection set also allows you to add aggregates into the query.

LINQ translations of grouping statements will typically perform two underlying calls, one to perform the grouping query and then a second batch to load the entities involved in the query to assign them into the corresponding resulting grouped collections allowing further client side projections to take place against those entities.

Examples:

To perform a standard grouping operation where the result will be a single column of the grouping key you can specify this as shown below:

var query = new Query(typeof(Contribution));
query.Group = Group.By("ContributorId");
var results = unitOfWork.Project<int>(query);

Alternatively you can specify a Projection set and use the Group.BySelection() method to indicate that LightSpeed should group by every column in the projection set. You need to ensure that this will generate a valid query for your target database, for example aggregates will not be supported by most database providers.

struct MyGroupingResult
{
  public int ContributorId { get; set; }
  public int ApprovedById { get; set; }
}
var query = new Query(typeof(Contribution));
query.Group = Group.BySelection();
query.Projection.Add("ContributorId");
query.Projection.Add("ApprovedById");
var results = unitOfWork.Project<MyGroupingResult>(query);

Alternatively you can use the .AndBy() method to chain your grouping expressions together, so to rewrite the above query using this approach would look like this:

struct MyGroupingResult
{
  public int ContributorId { get; set; }
  public int ApprovedById { get; set; }
}
var query = new Query(typeof(Contribution));
query.Group = Group.By("ContributorId").AndBy("ApprovedById");
var results = unitOfWork.Project<MyGroupingResult>(query);

Finally you can return aggregates as part of the result set by declaring these in your Projection set while independently specifying the grouping keys using Group instances. An example of this is shown below:

struct MyGroupingResult
{
  public int ContributorId { get; set; }
  public int Views { get; set; }
}
var query = new Query(typeof(Contribution));
query.Group = Group.By("ContributorId");
query.Projection.Add("ContributorId");
query.Projection.Add(Entity.Attribute("Views").Function("SUM"));
var results = unitOfWork.Project<MyGroupingResult>(query);

Subexpressions

Subexpressions allow you to define common expressions that can be referenced by name in the query.  See Subexpressions below for details.

Unions and Intersections

You can use the ComposedQueries collection and the ComposeMethod property to compose queries using the SQL UNION, UNION ALL or INTERSECT operator.

Hints

The Hints property allows you to pass index or table hints to databases where this is supported.  See the Performance and Tuning chapter for more information.

Using direct SQL statements

LightSpeed 5 has added the support for specifying a direct SQL statement on the Query object by setting the RawSql property with the statement text. If this property is set then all other properties on the query will be ignored and the specified SQL statement will be directly used. The returning result set is expected to either be a full entity if used with Find or the required projection if used with Project.

Other Querying Properties

The Identifier property is used when querying for a single entity by Id.

The IdentifiersOnly property specifies that LightSpeed should select only entity Ids, not full entities.

The IncludeDeleted property specifies that the query should also return soft-deleted entities.  See the Implementing Storage Policies with LightSpeed chapter for details.

The SearchQuery property invokes full text search.  See Full Text Search above.