LLBLGen Pro QuerySpec: the basics
Last time I introduced LLBLGen Pro QuerySpec, a new fluent API for specifying queries for LLBLGen Pro. As promised I'll write a couple of blogposts about certain aspects of the new API and how it works. Today I'll kick off with the basics.
Two types of queries: EntityQuery<T> and DynamicQuery
There are two types of queries in QuerySpec: entity queries (specified with objects of type EntityQuery<T>, where T is the type of the entity to return) and dynamic queries, which are queries with a custom projection (specified with objects of type DynamicQuery or its typed variant DynamicQuery<T>). The difference between them is mainly visible in the number of actions you can specify on the query. For example, an entity query doesn't have a way to specify a group by, simply because fetching entities is about fetching rows from tables/views, not rows from a grouped set of data. Similarly, DynamicQuery doesn't have a way to specify an entity type filter, simply because it's about fetching a custom projection, not about fetching entities. This difference guides the user of the API with writing the final query: the set of actions to specify, e.g. Where, From, OrderBy etc., is within the scope of what the query will result in.
A custom projection is any projection (the list of elements in the 'SELECT' statement returned by a query) which isn't representing a known, mapped entity. This distinction between entity queries and dynamic queries might sound odd at first, but it will be straightforward once you've worked with a couple of queries.
QuerySpec queries are specifications, they're not executed when enumerated, in fact you can't enumerate them. To obtain the result-set, you have to execute the queries. I'll dig deeper in how to execute QuerySpec queries in a follow up post.
The starting point: the QueryFactory
To get started with a query, the user has to decide what kind of objects the query has to produce: entity objects or objects which contain the result of a custom projection. The question is rather easy if you formulate it like this: "Do I want to obtain one (or more) instances of a known entity type, or something else?". If the answer to that is: "One (or more) instances of a known entity type" the query you'll need is an EntityQuery<T>, in all other cases you'll need a DynamicQuery.
If you change your mind half-way writing your query, no worries: you can create a DynamicQuery from an EntityQuery<T> with the .Select(projection) method and can define a DynamicQuery to return entity class instances, so there's always a way to formulate what you want.
To create a query, we'll use a factory. This factory is a small generated class called QueryFactory. It's the starting point for all your queries in QuerySpec: to create a query, you need an instance of the QueryFactory:
var qf = new QueryFactory();
The QueryFactory instance offers a couple of helper methods and a property for each known entity type, which returns an EntityQuery<entitytype> instance, as well as a method to create a DynamicQuery instance.
We'll first focus on entity queries.
Formulating your query, getting started.
QuerySpec is defined to be close to SQL, so it contains the same building blocks for queries as SQL does. Not all elements are mandatory. For example, not all queries need ordering, a where filter etc. and e.g. entity queries have a fixed projection so you don't have to specify it.
- A projection which defines the result-set shape (the 'SELECT' list). Automatic for Entity Queries, specified with .Select(projection) for Dynamic Queries.
- A source set from which the result-set has to be obtained (the 'FROM' clause). Automatic for a query if a single source (entity) is used, otherwise required for both query types, specified with .From(join list).
- A filter on the source set to obtain a subset of the complete set (the 'WHERE' clause). Optional, specified with .Where(predicate).
- An ordering definition to return the result-set rows in a given order (the 'ORDER BY' clause). Optional, but recommended when a limit or paging directive is specified. Specified with .OrderBy(sort clauses).
- A grouping specification to group source set rows on a subset of fields (the 'GROUP BY' clause). Optional, only available in Dynamic Queries. Specified with .GroupBy(field list).
- A filter on the grouped set of rows after a grouping action (the 'HAVING' clause). Optional, only available in Dynamic Queries, and only used with queries with a grouping specification specified with .GroupBy(). Specified with .Having(expression list).
- A limit specification to limit the number of rows in the result-set (the 'TOP', 'LIMIT', 'FIRST' or equivalent clause). Optional, specified with .Limit(number).
- A specification which filters out duplicate rows in the result-set (the 'DISTINCT' clause). Optional. Automatic for entity queries, if the engine decides it's more efficient to use distinct. Specified with .Distinct().
- A specification to obtain a specific range of rows in the result-set (a Paging directive). Optional, specified with .Page(pageNumber, pageSize).
There are other specific elements per query type, e.g. prefetch paths for entity queries, a typed projection lambda mechanism for dynamic queries, but we'll get to that in a later post.
Entity Queries, getting started.
To create an entity query in its most simplest form, we can simply use one of the properties exposed by a QueryFactory instance. Say we want to formulate a query for Customer entities. The QueryFactory class, which is generated for our domain, exposes a property for each entity in the domain and each property returns an EntityQuery<entitytype>, so the Customer property returns an EntityQuery<CustomerEntity>:
var qf = new QueryFactory(); var q = qf.Customer;
the variable 'q' contains an EntityQuery<CustomerEntity> instance and can be used to fetch customer entity instances. As it doesn't contain any filter, executing it will fetch all customer instances. This is the base line: a query which specifies the complete set of all instances of a given entity type, in this case Customer entities.
In general, you'd want to specify a subset of this complete set. To do so, you specify a Where clause. For example, let's specify a query for all customer entities from the USA:
var qf = new QueryFactory(); var q = qf.Customer .Where(CustomerFields.Country=="USA");
This query specification is similar to our first specification, but it now defines a filter on the complete set of 'Customer' entities. The .Where method accepts a predicate object, which can be a single predicate (e.g. field == value) or a predicate expression, which can be a combination of predicates concatenated with AND, OR, AND NOT, or OR NOT.
The mechanism to formulate the predicates is the native LLBLGen Pro query api mechanism, and which is documented extensively in the runtime framework documentation. The generated code base contains helper classes to specify a field of a given entity, like the CustomerFields.Country specification above, and the runtime framework uses operator overloading to formulate predicates under the hood. QuerySpec offers additional methods to help you formulate predicates, as shown in the following example which is equal to the one above:
var qf = new QueryFactory(); var q = qf.Customer .Where(CustomerFields.Country.Equal("USA"));
For now we'll leave the predicate specification semantics and focus on the query definitions themselves.
Now that we can filter our set of entities, we can look at how we can do more advanced things. For example, let's specify a query for all Customer entities which have an order on file which was filed by employee with employee id '2'. This is a query which has a filter on a related entity. To do that we can use several mechanisms, one of them is a join. This comes down to: defining a large set with Customer and Order data (the source set), filter that data with a filter and obtain the result-set from that filtered set. In short: join customer with order, use a where clause on the order fields, select the customer fields:
var qf = new QueryFactory(); var q = qf.Customer .From(QueryTarget.InnerJoin(qf.Order) .On(CustomerFields.CustomerId==OrderFields.CustomerId)) .Where(OrderFields.EmployeeId==2);
We need to specify the source set to be a joined set between Customer and Order, so when filtering on the fields from Order in that set, we automatically filter the rows of Customer. To do that in SQL you'd use Customer INNER JOIN Order ON .... Here we do the same thing, we specify an INNER JOIN between Customer and Order in the From() call. As the query we're specifying it on is already a Customer query, we use QueryTarget instead of re-specifying qf.Customer. In case you're wondering why the .InnerJoin isn't defined on qf.Customer instead of the From() clause, it's because it introduced ambiguity which is explained more in detail in the document in the QuerySpec beta archive available to LLBLGen Pro v3 customers.
In a follow up post we'll go deeper into what's available in QuerySpec with respect to joins and filters.
Dynamic Queries, getting started.
After we've seen how to create entity queries, it's now time to show how to create Dynamic Queries. This is done roughly in the same way as entity queries:
var qf = new QueryFactory(); var q = qf.Create();
The variable 'q' now contains a DynamicQuery instance, but we can't really do much with it: it's completely empty. To specify a query, we have to specify a projection using the .Select() method. Let's specify a query for all country names available in the Customer set, with the additional requirement that duplicates have to be filtered out. LLBLGen Pro is clever enough that when you specify CustomerFields.Country, you're fetching from 'Customer' so you don't have to specify a source set.
var qf = new QueryFactory(); var q = qf.Create() .Select(CustomerFields.Country) .Distinct();
This query is an untyped query, the resultset will be a list of object arrays (with 1 value in each array). To make it a typed query, we have to specify a typed projection. In the untyped variant, 'q' is a DynamicQuery. In the typed variant, 'q' will be a DynamicQuery<T>, where 'T' is the type of the objects returned by the query when it's executed. To create a typed variant, we use a different overload of .Select(), namely the one which accepts a lambda:
var qf = new QueryFactory(); var q = qf.Create() .Select(()=>CustomerFields.Country.ToValue<string>()) .Distinct();
The variable 'q' is now a DynamicQuery<string>, due to the lambda specification in the Select(). One other new element in the Select call is the .ToValue<T>() call. QuerySpec will parse the lambda specification in the Select() method and will transform the objects which have calls to known methods to elements for the projection and leave all other code alone. This allows you to formulate complex projection statements with code which isn't convertible to SQL statements. For each row to project into the resultset, the lambda is ran at runtime, executing all code you've specified there. In our simple example above, it simply returns the value for CustomerFields.Country.
Executing the query above will result in a List<string> with all country values from the Customer set.
Dynamic Queries can, similar to Entity Queries, contain definitions for larger source sets from which you project your final result-set. Let's say you want all CompanyName values for all customers who have an order on file filed by employee with employee id '2':
var qf = new QueryFactory(); var q = qf.Create() .Select(()=>CustomerFields.CompanyName.ToValue<string>()) .From(qf.Customer.InnerJoin(qf.Order) .On(CustomerFields.CustomerId == OrderFields.CustomerId)) .Where(OrderFields.EmployeeId==2) .Distinct();
Here we first specify the projection, using a .Select() method call. We then specify the source-set by using a .From() method call and after that we specify the filter on that source-set by using the .Where() method call. To limit duplicates, we specify .Distinct().
There's much more possible than this tiny scratch of the surface. I'll go deeper into the possibilities of QuerySpec in a follow up post.
QuerySpec is a free add-on for LLBLGen Pro v3 customers and is currently in beta. The complete archive with sourcecode is downloadable from the customer area on our website.