This section describes documentation of the general constructs for writing a query with QuerySpec, like filtering, sorting, grouping and specifying projections. Not all constructs are described in this section, as many have their own, more specific sections elsewhere in the QuerySpec documentation.
The Query Factory is the starting place for writing queries: it provides easy properties to create EntityQuery<T> instances, where T is the type of the entity you'd like to fetch, and also provides methods to create DynamicQuery instances using the Create() method. In this section both methods are used to give you a good overview how to get started with writing queries using QuerySpec.
To specify the source of a query, QuerySpec offers the query.From() method on a QuerySpec derived object. In many queries it's not necessary to use From(): only in DynamicQuery instances where it's not clear what the source is by looking at the projection, or when you want to specify Joins, From() is required. For more in-depth information about From() and Joins, please consult the Joins section. There can be one From() call on a QuerySpec query: calling it multiple times will overwrite a previous call's result.
The following code snippets illustrate the usage of From(). It also gives an example when From() isn't necessary.
// No From() needed, as qf.Customer creates an EntityQuery<CustomerEntity> which // already defines the source: the entity 'Customer' var qf = new QueryFactory(); var q = qf.Customer;
// From usage for specifying a join on an EntityQuery<CustomerEntity>. var q = qf.Customer .From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId==OrderFields.OrderId)) .Where(OrderFields.EmployeeId==4);
// From usage for specifying a join on a DynamicQuery var qf = new QueryFactory(); var q = qf.Create() .Select(OrderFields.CustomerId) .From(qf.Customer.InnerJoin(qf.Order).On(CustomerFields.CustomerId==OrderFields.CustomerId));
To specify a filter on a query's resultset, we typically use a 'where' clause in SQL, and in QuerySpec this is the same, by using the query.Where(predicate) method on a QuerySpec derived object. Where(predicate) has sibling methods: query.AndWhere(predicate) and query.OrWhere(predicate). AndWhere(predicate) appends the specified predicate to the existing predicates of the query the method is called on using the And operator. OrWhere(predicate) does the same but using the Or operator. The example below illustrates the usage of Where() to specify a filter on an existing query.
As argument for Where() you can specify any predicate (and thus also a PredicateExpression). See for more information about predicates: Creating predicates with fields and expressions, Expressions and the documentation on the predicate system (Adapter, SelfServicing).
// Where usage with a simple predicate
var qf = new QueryFactory();
var q = qf.Customer
.Where(CustomerFields.Country=="Netherlands");
// Where usage with a predicate expression
var qf = new QueryFactory();
var q = qf.Customer
.Where((CustomerFields.Country=="Netherlands")
.And(CustomerFields.City=="The Hague"));
To order / sort a resultset, in SQL order by is used. QuerySpec uses the same method for sorting/ordering a query's resultset: query.OrderBy(). OrderBy() accepts one or more sort clauses, separated by a ','. Below, two examples are given to illustrate the usage: one with a single sort clause, the other one with two.
// OrderBy usage with a single sort clause: sort on Country, ascending
var qf = new QueryFactory();
var q = qf.Customer
.OrderBy(CustomerFields.Country.Ascending());
// OrderBy usage with two sort clauses: sort on Country, ascending, then on City, descending
var qf = new QueryFactory();
var q = qf.Customer
.OrderBy(CustomerFields.Country.Ascending(),
CustomerFields.City.Descending());
To specify the actual shape of the query's resultset, in SQL we use the select keyword. In QuerySpec, specifying the shape of a resultset is done using the equivalent Select() method. Select() is only used when the resultset isn't an entity fetch: an EntityQuery<T> has its projection built-in: it results in a resultset which is used to fetch entities, and the shape is determined from the mapping of the entity type specified.
For more information about formulating projections in QuerySpec, please see the Projections section.
// Select usage with a lambda, resulting in a typed DynamicQuery<T> query
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
EmployeeId = OrderFields.EmployeeId.ToValue<int?>(),
CompanyName = CustomerFields.CustomerId.ToValue<string>()
})
.From(qf.Order.InnerJoin(OrderEntity.Relations.CustomerEntityUsingCustomerId))
.Distinct();
// Select usage with an untyped projection, resulting in an untyped DynamicQuery query
var qf = new QueryFactory();
var q = qf.Create()
.Select(CustomerFields.CustomerId, CustomerFields.CompanyName, OrderFields.OrderId)
.From(qf.Customer.InnerJoin(qf.Order).On(CustomerFields.CustomerId == OrderFields.CustomerId))
.Where(CustomerFields.Country == "Germany");
Grouping a resultset results in a resultset which doesn't represent entities. QuerySpec does support grouping of data, but only with DynamicQuery or DynamicQuery<T> instances. To get a DynamicQuery or DynamicQuery<T>, we first have to use Select() as described above with Select(). The examples below illustrate GroupBy() in combination of Select(), Having() clauses and aggregates
// GroupBy usage where the query is grouped on Customer.Country and an aggregate
// is used to count the number of customers per country. As the source of the query
// can be determined from the projection, no From clause is required.
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
Country = CustomerFields.Country.ToValue<string>(),
NumberOfCustomers = Functions.CountRow().ToValue<int>()
})
.GroupBy(CustomerFields.Country);
// GroupBy and Having usage in a complex query which gets the list of countries with the number of customers
// who have purchased more than 10 orders.
var qf = new QueryFactory();
// QuerySpec supports HAVING clauses, this query variant uses it instead of a where clause outside the groupby query
var q = qf.Create()
.Select(CustomerFields.Country.Source("g"), qf.Field("g", "NumberOfCustomers"))
.From(qf.Create()
.Select(CustomerFields.Country.Source("c"),
Functions.CountRow().As("NumberOfCustomers"),
qf.Field("c", "NumberOfOrders").Sum().As("TotalNumberOfOrders"))
.From(qf.Customer
.Select(CustomerFields.Country,
qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.CountRow().As("NumberOfOrders"))
.As("c"))
.GroupBy(CustomerFields.Country.Source("c"))
.Having(qf.Field("c", "NumberOfOrders").Sum().GreaterThan(10))
.As("g"))
.OrderBy(CustomerFields.Country.Source("g").Ascending());
To limit the number of rows returned by a query, QuerySpec offers the query.Limit(n) method. Limit(n) will limit the number of rows returned to n, if there are more rows in the resultset than n. Specifying 0 means there's no limit set. To start fetching at a given index in the resultset, QuerySpec offers query.Offset(m). Offset(m) will skip the first m rows. Specifying 0 means no skipping takes place. Using Limit() and Offset() in the same query allows the user to page through a resultset, similar to Page().
It's recommended to specify an ordering with Limit and Offset, to assure the right rows are returned.
// Limit usage where the first 10 rows are returned.
var q = qf.Customer
.From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId==OrderFields.OrderId))
.Where(OrderFields.EmployeeId==4)
.OrderBy(CustomerFields.CustomerId.Ascending())
.Limit(10);
// Limit usage where the first 4 rows are skipped and then the first 10 rows are returned.
var q = qf.Customer
.From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId==OrderFields.OrderId))
.Where(OrderFields.EmployeeId==4)
.OrderBy(CustomerFields.CustomerId.Ascending())
.Offset(4)
.Limit(10);
Additionally to using a combination of Limit(n) and Offset(m), QuerySpec offers a paging method: query.Page(pageNumber, pageSize). PageNumber and PageSize are 1-based. Specifying 0 for pageSize and/or pageNumber will result in no paging. It's recommended to specify an ordering with Page(), to assure the right rows are returned.
// Page usage where the 3rd page of 4 entries is returned.
var q = qf.Customer
.From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId==OrderFields.OrderId))
.Where(OrderFields.EmployeeId==4)
.OrderBy(CustomerFields.CustomerId.Ascending())
.Page(3, 4);
It is possible to fetch a Typed View using QuerySpec. The following example illustrates fetching a subset of the fields of a Typed View using QuerySpec by using its FetchAsDataTable method as a Typed View is a typed DataTable.
// Fetching three fields of the Typed View 'Invoices'. It doesn't matter whether the
// Select call returns a typed DynamicQuery or an untyped DynamicQuery.
var qf = new QueryFactory();
var q = qf.Create()
.Select(InvoicesFields.CustomerId, InvoicesFields.CustomerName, InvoicesFields.OrderId)
.Where(InvoicesFields.Country.StartsWith("U"));
var tv = new InvoicesTypedView();
new DataAccessAdapter().FetchAsDataTable(q, tv);
// Fetching three fields of the Typed View 'Invoices'. It doesn't matter whether the
// Select call returns a typed DynamicQuery or an untyped DynamicQuery.
var qf = new QueryFactory();
var q = qf.Create()
.Select(InvoicesFields.CustomerId, InvoicesFields.CustomerName, InvoicesFields.OrderId)
.Where(InvoicesFields.Country.StartsWith("U"));
var tv = new InvoicesTypedView();
new TypedListDAO().FetchAsDataTable(q, tv);
A Typed List is a pre-defined query, generated in code, combined with a typed DataTable, the Typed List class. It is possible to fetch Typed Lists using QuerySpec, besides the conventional methods for fetching Typed Lists (Adapter, SelfServicing).
Every TypedList has a new method, called GetQuerySpecQuery(queryFactory), which produces a DynamicQuery and which is usable everywhere a DynamicQuery is usable. To fetch the DynamicQuery, use the FetchAsDataTable method:
var tl = new OrderCustomerTypedList(); var q = tl.GetQuerySpecQuery(new QueryFactory()); // Append filters, orderby etc. here q.Where(OrderFields.EmployeeId==4); // then fetch it: // Adapter: new DataAccessAdapter().FetchAsDataTable(q, tl); // Selfservicing: // new TypedListDAO().FetchAsDataTable(q, tl);
A QueryFactory instance has to be passed to the GetQuerySpecQuery method as it's recommended to use the same QueryFactory instance in all statements which work on a query object: if you want to append other elements to the query produced by the GetQuerySpecQuery method, you can re-use the same QueryFactory instance as you passed to the method.
Using a Table Valued Function in QuerySpec is as simple as it is in Linq: simply specify the method available on the QueryFactory representing the Table Valued Function call instead of the entity that's mapped onto the resultset. The following example illustrates fetching Customer entities by using the Table Valued Function CustomersFromCountry which resultset the entity Customer is mapped on. As the method representing the Table Valued Function returns an EntityQuery<CustomerEntity>, we can use the returned EntityQuery as if we have used queryFactory.Customer:
// Usage of Table Valued Function 'CustomersFromCountry' to fetch Customer instances
var q = qf.CustomersFromCountry("Germany").As("R")
.Where(CustomerFields.CompanyName.Source("R").StartsWith("S"));
// SQL produced by query above:
SELECT LPA_R1.CustomerID As CustomerId, ...
FROM dbo.fn_CustomersFromCountry(@country) AS LPA_R1
WHERE LPA_R1.CompanyName LIKE 'S%'
It's mandatory that Table Valued Function calls are aliased in a QuerySpec query. The problem is that otherwise the elements aren't linkable to eachother: the example above, the Where predicate has to reference the elements returned by the function call; without the alias this won't work, as the predicate would look like it references an unaliased element while there are only aliased elements in the FROM clause of the SQL query.
LLBLGen Pro also supports mapping a Typed View onto the resultset of a Table Valued Function. To fetch such a Typed View using QuerySpec, the Query Factory offers a way to convert a Table Valued Function call as returned by the TvfCallFactory in a DynamicQuery, through the Create() method. The examples below illustrate the usage of this method.
// Typed View fetch using the Table Valued Function it's mapped on by passing it
// to the Create method on the query factory.
// 'O' is the alias for the Table Valued Function resultset.
var qf = new QueryFactory();
var q = qf.Create(TvfCallFactory.GetOrdersForCustomer("O", "ALFKI"));
var tv = new OrdersForCustomerTypedView();
new DataAccessAdapter().FetchAsDataTable(q, tv);
// Same Typed View fetch but now appended with a Where clause, as the
// result of Create() is a DynamicQuery like any other.
var qf = new QueryFactory();
var q = qf.Create(TvfCallFactory.GetOrdersForCustomer("O", "ALFKI"))
.Where(OrdersForCustomerFields.EmployeeId.Source("O") == 4);
var tv = new OrdersForCustomerTypedView();
new DataAccessAdapter().FetchAsDataTable(q, tv);
// Typed View fetch using a Table Valued Function without parameters.
// 'C' is the alias for the Table Valued Function resultset.
var qf = new QueryFactory();
var q = qf.Create(TvfCallFactory.FnCustomersIndexer("C"))
.Where(FnCustomersIndexerFields.Country.Source("C") == "Germany");
var tv = new FnCustomersIndexerTypedView();
new DataAccessAdapter().FetchAsDataTable(q, tv);