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

Merging resultsets: Union() and UnionAll()

To merge two resultsets in a query, you can use the QuerySpec extension methods Union() and UnionAll which mimic their SQL counterparts UNION and UNION ALL. As with the SQL statements, the resultsets of the two queries have to match.

Here's an example which uses Union to merge two resultsets and also uses a nested Union call to merge a third resultset into the second query first. This way you can control which set is unioned first.

var qf = new QueryFactory();
var q1 = qf.Customer
           .Where(CustomerFields.CompanyName.StartsWith("B"));
var q3 = qf.Customer
           .Where(CustomerFields.CompanyName.EndsWith("N"));
var q2 = qf.Customer
           .Where(CustomerFields.CompanyName.EndsWith("S"))
           .UnionAll(q3);
var q4 = q1.Union(q2);
var results = adapter.FetchQuery(q3, new EntityCollection<CustomerEntity>());

Limitations

• A query which is unioned into another query using Union or UnionAll can't contain a nested query in the projection nor can it prefetch path definitions nor paging directives.
• Due to the fluent API, you can't union a query with a custom projection with an entity query and vice versa.

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

Client side paging vs. server side paging

Paging in general happens on the server, however the LLBLGen Pro runtime framework switches to client-side paging at the datareader level if the paging query can't be generated without causing errors on the server. This happens if a DISTINCT violating construct is present in the SQL query, e.g. a type that's not to be used with DISTINCT or an order by on a field that's not in the projection. The datareader-level paging is efficient (it discards any rows before the page to read and stops reading data once the page to read is fully read) however can be slower than a server-side paging query.

To determine whether the framework switched to client-side paging / distinct filtering / row limiting, it will append to the SQL query output to the ORMQueryExecution tracer a string which signals this: "Requires client side paging", "Requires client side distinct filtering" and "Requires client side limiting", if the ORMQueryExecution tracer is set to level 4 (verbose). See ORM Support classes tracing for details on this tracer.

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 results = await RetrievalProcedures.FetchCustomersOnCountryQsTypedViewAsync(
            new QueryFactory().GetCustomersOnCountryTypedViewProjection(), "USA", 
            CancellationToken.None);

Alternatively, you can use FetchQueryFromSource to do the same, however it's recommeded to use the generated method.

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.

Specifying a Temporal table predicate (SQL Server 2016+ / DB2 v10+)

To specify a temporal table predicate on a target, the following methods are available:

• query.ForSystemDate(TemporalPredicate[, param1[, param2]]). TemporalPredicate is a string, which is the predicate to emit, e.g. "AS OF". param1 and param2 are optional parameters of type DateTime, to be used with the predicate. You can specify just one temporal table predicate per target. Specifying more than one will make the last one be used.
• query.ForBusinessDate(TemporalPredicate[, param1[, param2]]). Equal to ForSystemDate, but now it uses a BusinessDate directive, instead of a SystemDate directive. query.ForBusinessDate is supported only on DB2 v10+.

where query is either a EntityQuery<T> or DynamicQuery<T>, as long as T is either an entity type or a POCO typed view type. Of course specifying these methods on elements which have no versioning table assigned to them in the RDBMS will result in an error inside the database.

Examples

The following example queries the Employee entity set using the System Time with a BETWEEN predicate, specifying two dates. The database will automatically merge matching results from the history table as well as the active table into one resultset.

var qf = new QueryFactory();
var q = qf.Employee
            .ForSystemTime("BETWEEN {0} AND {1}", fromDate, toDate)
            .Where(EmployeeFields.EmployeeId.Equal(1));

The following example shows a query using the Business Time with an AS OF predicate. Business Time predicates are supported on DB2 only as SQL Server 2016 doesn't support Business Time versioning.

var qf = new QueryFactory();
var q = qf.PolicyBus
            .ForBusinessTime("AS OF {0}", new DateTime(2010, 4, 1))
            .Where(PolicyBusFields.Id.Equal(1414));

Specifying a target hint / index hint (SQL Server / MySQL / Google Cloud Spanner)

To specify a hint on an entity / poco typed view fetched using QuerySpec, you can use the extension method query.WithHint(hint). hint is a string, e.g. "NOLOCK" and is the actual hint to apply. query is either a EntityQuery<T> or DynamicQuery<T>, as long as T is either an entity type or a POCO typed view type. The string specified has to contain the arguments for the hint, it's placed inside the query as-is. You can specify as many WithHint statements as needed, they're appended. For SQL Server, we support target hints using WithHint. For MySQL we support index hints. For Google Cloud Spanner we support join hints and secondary index hints.

Target hint example (SQL Server specific)

The following example shows the target hint support for SQL Server. Here a query using a join specifies for one join operand two hints which are applied to the target the element, in this case the "Customer" entity, is mapped on.

var qf = new QueryFactory();
var q = qf.Customer
                .WithHint("NOLOCK")
                .WithHint("FORCESEEK")
                .From(QueryTarget.InnerJoin(qf.Order.WithHint("FORCESEEK"))
                        .On(CustomerFields.CustomerId.Equal(OrderFields.CustomerId)))
                .Where(OrderFields.EmployeeId.GreaterThan(4));

Index hint example (MySQL specific)

The following example shows the specification of an index hint on the entity "Address" to specify the database to use that particular index in the query.

var qf = new QueryFactory();
var q = qf.Address
            .WithHint("USE INDEX (idx_fk_city_id)")
            .Where(AddressFields.City.Contains("Stadt"));

Join hint with secondary hint example (Google Cloud Specific)

You can specify join hints and secondary index hints with one element. The runtime will assign the join hint with the join operator and the other hints with the table name, as shown in the following example:

var qf = new QueryFactory();
var q = qf.TypesTab
          .From(QueryTarget.InnerJoin(qf.Notnulltab.WithHint("FORCE_JOIN_ORDER=TRUE")
                                        .WithHint("FORCE_INDEX=NOTNULLTABIDX"))
                           .On(TypesTabFields.Id.Equal(NotnulltabFields.Id)))
          .Where(TypesTabFields.Id.GreaterEqual(1000))
          .OrderBy(TypesTabFields.Id.Ascending());

Specifying query / optimizer hints (SQL Server, MySQL, Oracle)

If, for some specific queries, you need to specify a query hint or optimizer hint in a QuerySpec query, you can. Query hints and Optimizer hint support is available for SQL Server, MySQL and Oracle. For Oracle you can use the query hint system also to specify target hints, as they're specified at the same location as query hints in the SQL query.

Specifying hints with a query

To specify a hint with a query, the same method as for target hints is used, WithHint, however a directive argument has to be specified to describe the nature of the hint: WithHint(hintstring, HintType). By default the HintType is set to TargetHint. To specify a query hint, HintType.QueryHint should be specified with the WithHint call.

Limitations

The runtime doesn't check whether the hint is valid on the select statement it's specified with. This means that if a unioned query contains a query hint, it will be emitted with that unioned query's SQL. However on SQL Server this isn't allowed.

Example

var qf = new QueryFactory();
var q = qf.Customer
          .From(QueryTarget.InnerJoin(qf.Order)
                .On(CustomerFields.CustomerId.Equal(OrderFields.CustomerId)))
          .Where(OrderFields.EmployeeId.GreaterThan(4))
          .WithHint("MERGE JOIN", HintType.QueryHint)
          .WithHint("FORCE ORDER", HintType.QueryHint);

SELECT DISTINCT [Northwind].[dbo].[Customers].[Address],
                [Northwind].[dbo].[Customers].[City],
                [Northwind].[dbo].[Customers].[CompanyName],
                [Northwind].[dbo].[Customers].[ContactName],
                [Northwind].[dbo].[Customers].[ContactTitle],
                [Northwind].[dbo].[Customers].[Country],
                [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
                [Northwind].[dbo].[Customers].[Fax],
                [Northwind].[dbo].[Customers].[Phone],
                [Northwind].[dbo].[Customers].[PostalCode],
                [Northwind].[dbo].[Customers].[Region]
FROM   ([Northwind].[dbo].[Customers]
        INNER JOIN [Northwind].[dbo].[Orders]
            ON [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID])
WHERE  (([Northwind].[dbo].[Orders].[EmployeeID] > @p1))
OPTION (MERGE JOIN, FORCE ORDER)

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

Query tagging

QuerySpec has the ability to let you mark (tag) the SQL query or queries generated by the runtime with a tag. This tag is a string which is added as a comment before the actual query string. This way you can easily track back the SQL query on a database server with the actual calling code without a profiler / stacktrace. It's a low-impact way to make sure you can track down information at runtime in the future.

Requirements

As the tag is a string and added to the query as a comment, it can't contain */ and /* character sequences, as they're the comment markers in SQL. The string isn't checked for these characters by the runtime.

Usage

To mark / tag a query, use the MarkWithTag(string) extension method for QuerySpec. The runtime will then add the passed in string as a comment to the generated SQL, at the front. The MarkWithTag() method is available on any QuerySpec query.

Prefetch paths, nested queries and query tagging

A prefetch path fetched through the query specified, or nested queries fetched through a query, all use the same tag as their source of origin is the same.

Caching, execution plans and tags

The tag is added (at the front) to the sql string in the DbCommand of an IRetrievalQuery right before execution. This avoids the SQL query not being usable for determining resultset caches. Execution plans can differ based on the added comment as the RDBMS likely will use a hash for the string to determine whether it's already known or a new query. This means that you should think ahead what to use for the tag string: an ever changing value might not be a good idea in this case.

It's also likely not a good idea to tag every query, but only the ones which you might expect problems with at runtime.

Creating tags based on call path

Creating tags based on the call origin might be good in some cases. You can use the CallerFilePath, CallerLineNumber and CallerMemberName attributes in .NET code to create a string with the values created by the compiler. See the .NET Reference documentation of these attributes for details.

Tracing and query tags

In the ORMQueryExecution tracer, which emits which queries were executed, the tag is appended to the output as well, below the parameters. This way queries which are logged through the trace framework can also be related to origins based on the used tag.

Example

The following code which tags the query with the string "QuerySpec/EntityFetches/QueryTagTest_Scalar"

var qf = new QueryFactory();
var q = qf.Customer
          .Where(CustomerFields.Country == "UK")
          .Select(Functions.CountRow())
          .MarkWithTag("QuerySpec/EntityFetches/QueryTagTest_Scalar");
var c = new DataAccessAdapter().FetchScalar<int>(q);

produces the following SQL

/* QuerySpec/EntityFetches/QueryTagTest_Scalar */ SELECT TOP(@p2) COUNT(*) AS [CF]
FROM   [Northwind].[dbo].[Customers]
WHERE  (([Northwind].[dbo].[Customers].[Country] = @p3))