General usage

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.

Specifying the from clause: From()

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));

Filtering the resultset: Where()

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. 

// 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"));

Ordering the resultset: OrderBy()

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());

Specifying a projection: Select()

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 the resultset: GroupBy() and Having()

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());

Limiting and offsetting in the resultset: Limit() / Offset()

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);

Paging through the resultset: Page()

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);

Fetching a Typed View

This section describes how to fetch a Typed View using QuerySpec. It depends on the OutputType setting value and the mapped target (table, stored procedure resultset etc.) what code you have to use.

Typed View mapped onto a table / view

It depends on the OutputType setting of the typed view how the typed view is fetched. See the examples below for the various OutputSetting values

OutputValue: TypedDataTable

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);

OutputType: PocoWithQuerySpecQuery

A property is created in the QueryFactory class which creates a DynamicQuery<T> with a typed Select which fills the Typed View row with values, without a From clause. The property is named after the typed view, similar to the entity query properties are named after the entity name: TypedViewName. It is possible to join with the typed view query, using normal constructs:

var qf = new QueryFactory();
var q = qf.Invoices
            .From(QueryTarget.InnerJoin(qf.Order)
                    .On(InvoicesFields.OrderId.Equal(OrderFields.OrderId)))
        .Where(OrderFields.EmployeeId == 2);
var results = new DataAccessAdapter().FetchQuery(q);    

var qf = new QueryFactory();
var q = qf.Invoices
            .From(QueryTarget.InnerJoin(qf.Order)
                    .On(InvoicesFields.OrderId.Equal(OrderFields.OrderId)))
        .Where(OrderFields.EmployeeId == 2);
var results = new TypedListDAO().FetchQuery(q);   

Here the typed view Invoices is joined with a normal entity 'Order'. The join uses QueryTarget to append the join to the target of the generated query of the typed view.

Typed View mapped onto a Stored Procedure Resultset

Here, the OutputType setting has to be PocoWithQuerySpecQuery to fetch the typed view using QuerySpec. Retrieving a poco typed view mapped onto a stored procedure resultset comes down to using two parts:

1. Obtain the projection lamdba from the QueryFactory. This projection is produced by the method TypedViewNameTypedViewProjection
2. Pass it to the method in RetrievalProcedures which fetches the actual typed view.

This looks like the following:

var results = RetrievalProcedures.FetchCustomersOnCountryQsTypedView(
         new QueryFactory().GetCustomersOnCountryTypedViewProjection(), "USA");

var qf = new QueryFactory();
var rows = await new DataAccessAdapter()
                .FetchQueryFromSourceAsync(qf.GetCustOrdersDetailQsTypedViewProjection(), 
                            RetrievalProcedures.GetCustOrdersDetailCallAsQuery(10254));

Typed View mapped onto a Table-valued function

Depending on the OutputValue setting of the typed view, a different fetch strategy has to be used

OutputType: TypedDataTable

To fetch a Typed View mapped onto a table-valued function and with OutputType is TypedDataTable using QuerySpec, use the following steps. The Query Factory offers a way to convert a Table Valued Function call as returned by the TvfCallFactory into 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);

// 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 TypedListDAO().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 TypedListDAO().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 TypedListDAO().FetchAsDataTable(q, tv);

OutputType: PocoWithQuerySpecQuery

A method is generated in the QueryFactory class which has the name of the Table-Valued-Function which returns the typed view row, similar to the Linq variant and the methods which are generated for the entity returning Table-Valued-Function calls. The method accepts an alias string and for each parameter of the Table-Valued-Function Call the method has a corresponding argument. The method signature for a TypedView mapped onto a tvfcall resultset is kept equal with the rest of the typedview methods to differentiate them from the entity returning methods.

Joining is possible too, similar to the join constructs with typed views on tables/views:

var qf = new QueryFactory();
var q = qf.OrdersForCustomerTV("C", "ALFKI")
        .From(QueryTarget.InnerJoin(qf.Employee)
                       .On(TvfOrdersForCustomerTVFields.EmployeeId.Source("C")
                                .Equal(EmployeeFields.EmployeeId)))
            .Where(EmployeeFields.EmployeeId.Equal(4));
var results = new DataAccessAdapter().FetchQuery(q);

var qf = new QueryFactory();
var q = qf.OrdersForCustomerTV("C", "ALFKI")
        .From(QueryTarget.InnerJoin(qf.Employee)
                       .On(TvfOrdersForCustomerTVFields.EmployeeId.Source("C")
                                .Equal(EmployeeFields.EmployeeId)))
            .Where(EmployeeFields.EmployeeId.Equal(4));
var results = new TypedListDAO().FetchQuery(q);

Fetching a Typed List

A Typed List is a pre-defined query, generated in code, combined with a typed DataTable or a POCO class, the Typed List class. Which one is generated for you depends on the OutputType setting value of the Typed List.

OutputType: TypedDataTable

It is possible to fetch Typed Lists using QuerySpec, besides the conventional methods for fetching Typed Lists (Adapter, SelfServicing).

Every TypedList has a 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: 
new DataAccessAdapter().FetchAsDataTable(q, tl); 

var tl = new OrderCustomerTypedList(); 
var q = tl.GetQuerySpecQuery(new QueryFactory()); 
// Append filters, orderby etc. here
q.Where(OrderFields.EmployeeId==4);
// then fetch it: 
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.

OutputType: PocoWithQuerySpecQuery

A class is generated for the Typed List: TypedListNameRow, and a method is added to the QueryFactory class for QuerySpec, called GetTypedListNameTypedList() which returns a DynamicQuery<T>, where T is the name of the class generated for the TypedList, TypedListNameRow.

To fetch the query, one has to use one of the usual QuerySpec fetch methods for fetching a DynamicQuery<T> . The returned DynamicQuery<T> can be further configured by appending where clauses, order by clauses like one would do with a normal DynamicQuery<T> query.

Example:

var qf = new QueryFactory();
var q = qf.GetOrderDetailsQsTypedList()
        .Where(ProductFields.ProductName.Source("P").StartsWith("A"));
var results = new DataAccessAdapter().FetchQuery(q);

var qf = new QueryFactory();
var q = qf.GetOrderDetailsQsTypedList()
        .Where(ProductFields.ProductName.Source("P").StartsWith("A"));
var results = new TypedListDAO().FetchQuery(q);

Here the typed list OrderDetailsQs is fetched and a filter is appended to it. As the typed list has aliased entities, the alias has to be specified in the filter as well to refer to the right entity.

Another example, this time with an additional join

var qf = new QueryFactory();
var q = qf.GetCustomerOrderOrderDetailTypedList()
        .From(QueryTarget.InnerJoin(qf.Product)
                        .On(OrderDetailFields.ProductId.Equal(ProductFields.ProductId)))
        .Where(ProductFields.ProductName.StartsWith("A"));
var results = new DataAccessAdapter().FetchQuery(q);

var qf = new QueryFactory();
var q = qf.GetCustomerOrderOrderDetailTypedList()
        .From(QueryTarget.InnerJoin(qf.Product)
                        .On(OrderDetailFields.ProductId.Equal(ProductFields.ProductId)))
        .Where(ProductFields.ProductName.StartsWith("A"));
var results = new TypedListDAO().FetchQuery(q);

Here the typed list CustomerOrderOrderDetail is fetched and an additional entity, product, is joined with the query over the OrderDetail - Product relationship. No entity is aliased in the typed list, so we don't have to specify the alias here.

Using a Table Valued Function

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%'

Fetching a Typed View mapped onto a Table Valued Function resultset.

To fetch a typed view mapped onto a table-valued function resultset using QuerySpec, see Fetching a Typed View earlier in this section