Query types

QuerySpec defines the following query types:

  • EntityQuery<T>. This is a query which will result in the fetch of an entity. T is the type of the entity class.
  • DynamicQuery. This is a query which will result in the fetch of a projection.
  • DynamicQuery<T>. This is effectively a DynamicQuery (it derives from DynamicQuery), but it has a projector defined which will return the results as elements of type T.

All query classes derive from the shared base class, QuerySpec. The developer defines the query by using methods on one of these elements. The methods can be used in any order.

Tip

QuerySpec's query classes as well as other objects used in QuerySpec are not immutable like in Linq. This means that if you cache a query object and alter it later on, the actually cached object is altered, you don't get a copy of it like in Linq.

To avoid strange errors, instead of caching creating query objects which are re-used in multiple queries, create methods which produce these queries and call them each time you need the query object produced by these methods. You can alter a created QuerySpec query object at any time after it's been created.

QueryFactory

EntityQuery<T> and DynamicQuery instances are created using a factory. This factory is called QueryFactory, a generated class which is capable to create DynamicQuery instances (using Create() or Create(alias) and EntityQuery<T> instances using Create<T> / Create<T>(alias) or one of the generated properties, one for each entity type.

It mimics LinqMetaData: instead of producing an IQueryable<T>, it produces in the generated properties an EntityQuery<T>, where it inserts an elementcreator, as well as an alias.

Important!

It's recommended to use one QueryFactory instance per complete query definition. It's not recommended to cache a QueryFactory instance in a static/shared variable because it contains a counter for unique artificial aliases, which are created for elements which don't get a manual alias assigned to them and aliasing is required.

As a QueryFactory is a lightweight object (no object overhead to create a new instance), it's not performance intensive to create one for each query.

Aliases

Queries can be aliased, by specifying the alias with the factory creation method, or by using the extension method .As(alias) on a query. Aliasing isn't done automatically like with Linq to LLBLGen Pro: if an element has to be aliased, the developer has to specify the alias manually. .As(string) can also be used for aliasing expressions and fields.

To specify the alias a field is targeting, the .Source(alias) extension method is used, which is easier than the fluent method SetObjectAlias() which is part of the low-level API.

Referring to aliased expressions

To refer to aliased expressions, e.g. in the Orderby clause, one can use extension methods defined on string. For example, to sort on an expression with the alias "FullName", do:

query.OrderBy("FullName".Ascending())

This assumes the source isn't aliased. Another way to do this is by using the QueryFactory class and create a field using a name:

// qf is a QueryFactory instance
query.OrderBy(qf.Field("FullName").Ascending())

If the source is aliased, this is a better method, because one can simply append a .Source() call:

// qf is a QueryFactory instance
query.OrderBy(qf.Field("FullName").Source("E").Ascending())

Alternatively, one can use the overload which accepts the source as the first parameter:

// qf is a QueryFactory instance
query.OrderBy(qf.Field("E", "FullName").Ascending())

TargetAlias vs. Alias

A query has an alias, which is the alias of the complete query. If the query results in a derived table, this alias will be the alias of the derived table. It can sometimes be handy to alias the target of the query separately.

This is the TargetAlias. The target alias is by default the empty string, so the target of the query isn't aliased. For DynamicQuery instances, the TargetAlias has no effect, as the aliases for the targets are specified manually.

Elements inside a query should refer to the target alias, if set. Elements outside a query, should refer to the alias, if set. Elements are outside a query, if the query itself represents a derived table.

An EntityQuery<T> joined in a join isn't a derived table thus not a separate query. When the query becomes a derived table on its own because additional clauses are added, elements referring to the query should refer to its alias instead and the query needs an alias.

In general you'll most of the time use Alias and not TargetAlias. QuerySpec comes with a lot of examples which illustrate the various uses of aliases.

The QuerySpec class

The base class for the query classes is the QuerySpec class. EntityQuery<T> and DynamicQuery derive from this class. It defines the following elements:

  • Alias. This is the alias of the query as a set. Optional. Set in constructor or with the extension method As(string alias).
  • From(operand). This specifies the FROM clause, which is the RelationCollection the query will be using. It's not necessary to specify a From() clause if the projection contains only fields from the same entity, or when the query is an EntityQuery<T>. Operand is a join, series of joins chained together or a query.
  • Where(predicate). This defines the WHERE clause for the query. The predicate is an IPredicate instance which can be a single predicate object or a predicate expression.
  • AndWhere(predicate) / OrWhere(predicate). Similar to Where however they append the predicate to an existing predicate with And resp. Or.
  • OrderBy(sort-clause, [sort- clause]…). This defines the ORDER BY clause for the query. The sort-clause is a SortClause instance, and one or more of them can be defined.
  • Limit(number). This defines the maximum number of elements to retrieve in the query, typical ending up as TOP or LIMIT or other limit construct available for the RDBMS. 0 means no limit. Use Limit in combination with Offset(number) to page through a resultset.
  • Offset(number). This defines the offset of the actual resultset to consume: number of rows to skip at the front of the resultset. 0 means no offset specified. Use Offset() in combination with Limit to page through a resultset.
  • Page(pageNumber, pageSize). This defines the page to fetch from the total resultset of the query. It's an alternative to a combination of Offset() and Limit().
Info

When using Page on a query, and the query has a projection which targets a derived table in the From() clause, it's key to use a typed projection when paging takes place on a database which creates a temp table (SQL Server 2000). It otherwise will produce strings for the values of fields which are projected from the derived table as they have as type 'object'. Another alternative is to use the .CastTo<type>() extension method on the fields in the projection.

  • CorrelatedOver(correlationFilter). This defines the correlation relation over which this query is related to a parent query. Used to define the correlation between a nested query in the projection and the parent query or a correlated subquery with its outer query. The correlationRelation is an EntityRelation which is used to produce the correlation predicate.

The EntityQuery<T> class

This is the type which is used to fetch entities. It's created using an instance of the QueryFactory class. It derives from the class QuerySpec and adds the following elements to the QuerySpec set of elements:

  • Exclude(field, [field]…). This specifies the fields to exclude from the fetch. One or more fields can be specified
  • Include(field, [field]…). Similar to Exclude() but now for specifying fields to include in the fetch
  • InnerJoin(operand).On(predicate) / LeftJoin(operand).On(predicate) / RightJoin(operand).On(predicate) / FullJoin(operand).On(predicate). This defines a join of the entity query with a related element, the operand, using the predicate specified in On(). See Joinsbelow for more information. These methods are only valid inside a From() clause.
  • CrossJoin(operand). This defines a cross-join between the entity query and the related element, the operand. See Joins for more information. This method is only valid inside a From() clause
  • Select(projection elements). This creates a DynamicQuery with the projection elements as specified as the projection and filter/relations as specified on the EntityQuery<T>
  • TargetAs(alias). Specifies the alias of the target, which is in general not aliased. This is a different alias than the one specified on the query using .As(alias), as that's the alias of the query as a whole, not the target.
  • OfEntityType<T>(). This defines the type filter to filter the results on. E.g. if the query is for employees, this filter can limit the results by applying a type filter for T. The type-predicate is appended with And to the where clause in the query.
  • NotOfEntityType<T>(). Similar to OfEntityType<T>(), but now negated.
  • Except<T>(query<T>). This adds a predicate to the EntityQuery<T>'s where clause which filters out any entity which is also present in query<T>.
  • Intersect<T>(query<T>). This does the opposite of Except<T> and adds a predicate to the EntityQuery<T>'s Where clause which filters out any entity which isn't in query<T>
  • Except<T>(IEnumerable<T> elements). This adds a predicate which filters the EntityQuery<T> based on the elements specified which is true for only the entities which are not an element of elements. This is done based on a pk comparison.
  • Intersect<T>(IEnumerable<T> elements). This adds a predicate which filters the EntityQuery<T> based on the elements specified which is true for only the entities which match an element in elements. This is done based on a pk comparison.
  • WithPath(PrefetchPath). This specifies the specified prefetch path as the prefetch path to use for the EntityQuery<T>. Ignored if the EntityQuery<T> is wrapped in another element, e.g. a derived table, so only used if the path is specified on the outer query which is actually executed.
  • ContextToUse. This property can be set to a Context instance which is used when fetching entities into a new entity collection created by the query spec fetch methods. When a collection is passed to the fetch methods, this property is ignored: the collection to fetch has to be set to the context before calling the fetch method. This way, one can decide to ignore the context on the query.
  • CastTo<TNew>. This changes the generic type argument T of the EntityQuery<T> to TNew and returns an EntityQuery<TNew>. CastTo<TNew> won't apply a type filter.

The DynamicQuery class

This is the type which is used to fetch projections. It's created using an instance of the factory QueryFactory. It derives from the QuerySpec class and adds the following elements to the QuerySpec set of elements.

  • Distinct(). This specifies whether the query should be returning unique rows. By default the query will return all rows.
  • Select(projection spec). This specifies the projection of the query.
  • GroupBy(field, [field]…). This specifies a group by clause for the dynamic query. The fields to group on have to be specified. If the projection is larger, the missing fields are automatically appended.
  • Having(predicate). This specifies a having clause for the groupby clause. Ignored if the groupby clause is not specified.
  • WithProjector<T>(Func<ProjectionRow, T> projectorFunc). This method creates a DynamicQuery<T> from the DynamicQuery it operates on and specifies the projectorFunc as the projector to use to produce typed instances of every row returned by the DynamicQuery. DynamicQuery<T> is a DynamicQuery and can be used where-ever a DynamicQuery can be used.
  • SelectFrom(DynamicQuery<T> query). This method creates a DynamicQuery<T> and creates a projection which targets every field in query's projection and copies the projection lambda. It then calls From() to pass query as the single argument. Useful when appending arguments to queries when wrapping in a derived table is required.
  • Except<T>(DynamicQuery). This adds a predicate to the DynamicQuery's Where clause which filters out any row which is also present in the specified DynamicQuery. It's required that the projections of both are equal.
  • Intersect<T>(DynamicQuery). This adds a predicate to the DynamicQuery's Where clause which filters out any row which is not present in the specified DynamicQuery. It's required that the projections of both are equal.

The DynamicQuery<T> is a DynamicQuery class with a typed resultset and doesn't add any public functionality to DynamicQuery.