Miscellaneous constructs

QuerySpec defines a variety of constructs to help you build queries more efficiently. These constructs are described briefly below.

Specifying a constant

To specify a constant, e.g. in the projection or in an expression, use Functions.Constant(object). Object has to be a constant value. This method will create a DbFunctionCall("{0}", object) object.

WITH TIES support

For the databases PostgreSQL 13+, Oracle 12C+ and SQL Server 2005+ we support the WITH TIES SQL command, which is used when a resultset is limited to a specified set of rows. In QuerySpec this is done using the .Limit(n) extension method. If there are duplicate rows at the end of the n rows, using just .Limit(n) will make the database return the first n rows and which ones of the duplicates you'll get is undefined.

Using the extension method .WithTies() additionally to .Limit(n), the generated SQL query will have the WITH TIES SQL command specified which will make the database return all duplicate rows at the end of the n rows. See the example below:

var qf = new QueryFactory();
var q = qf.Customer.OrderBy(CustomerFields.City.Ascending()).Limit(14).WithTies();
var results = adapter.FetchQuery(q);

On Northwind this would return 14 rows without WithTies(), however in the query above it'll return 16 rows, including the 2 rows with the same City value as the last row.

Exists/NotExists/Any/All

Any and All, two Linq query operators are directly related to Exists/Not Exists in SQL. To produce an All() predicate for a query, the developer can simply call query.All(predicate) and for an any call, simply call query.Any(predicate). These methods wrap a call to Functions.Exists/NotExists and produce an IPredicate object.

The All(predicate) method produces a NOT EXISTS(NOT PREDICATE) predicate. Any simply produces EXISTS(PREDICATE). To easily write filters, it's also possible to use query.WhereExists() and query.WhereNotExists().

Example

The following example creates an Any(predicate) predicate on the query in q and obtains the result using a scalar fetch, answering the question "Are there any customers with customerid=='CHOPS'?". 

var qf = new QueryFactory();
var q = qf.Customer.Select(CustomerFields.CustomerId, CustomerFields.CompanyName);
var result = adapter.FetchScalar<bool>(
                qf.Create().Select(q.Any(CustomerFields.CustomerId == "CHOPS")));

Another example where a set of entities (Customer) is fetched based on a filter on a related entity (Order) using Any: 

var qf = new QueryFactory();
var q = qf.Customer.TargetAs("C")
            .Where(qf.Order
                    .Any((OrderFields.EmployeeId == 4)
                        .And(OrderFields.CustomerId
                            .Equal(CustomerFields.CustomerId.Source("C")))));

Contains

In QuerySpec, Contains is used to create a predicate which compares a value to a set. In SQL this can be done in multiple ways. Each usage of Contains in QuerySpec result in a predicate.

There are multiple usages:

  • query.Contains(entity). This creates a predicate which looks like: EXISTS(query.Where((pkfield1==entity.pkfield1value) AND (pkfield2==entity.pkfield2value) AND..)
  • query1.Contains(query2). This creates a predicate which is true if there's a row in query1 which is equal to a row in query2. In general this is used in a situation where query2 results in 1 element. It's a nested EXISTS predicate.
  • query.Contains(object). This creates a predicate which is true if the object's value is equal to a row in query. If object is a value (e.g. an int value like '2'), the projection of query has to have 1 field and a comparison between object as a value is used and the field returned by query. If the projection contains multiple fields, the values of equally named properties on object are used to compare them against the fields in the projection of query.

Example:

// Returns true if there are customers from the USA
var qf = new QueryFactory();
var result = adapter.FetchScalar<bool>(qf.Create()
                .Select(qf.Customer.Select(CustomerFields.Country).Contains("USA")));

In / NotIn

In is an operator, implemented as an extension method on entity field or IExpression, .In(range/query), which accepts a dynamic query or range of values. It produces a field compare set predicate when the operand is a query, and a field compare range predicate if the operand is a range of values. The negate variant is .NotIn().

There's a helper method called Functions.In and Functions.NotIn for constructing the predicates differently than with extension methods.

Example:

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    string[] ids = new string[] { "CHOPS", "ALFKI" };
    var qf = new QueryFactory();
    var q = qf.Create()
                .Select(() => new
                {
                    CustomerId = OrderFields.CustomerId.ToValue<string>(),
                    OrderId = OrderFields.OrderId.Max().ToValue<int>()
                })
                .Where(OrderFields.CustomerId.In(ids))
                .GroupBy(OrderFields.CustomerId);
    var results = adapter.FetchQuery(q);
}

Between

Between is an operator, implemented as an extension method on an entity field or expression, .Between(valueBegin, valueEnd), which creates a BETWEEN predicate for the expression or field to compare it with two range specifiers and will result in a

field/expression BETWEEN valueBegin AND valueEnd

SQL fragment. See the SQL manual for the database used whether valueBegin and valueEnd are included in the range of valid values. Normally they are.

IIF

The IIF(op1, op2, op3) function is a specific function, defined on the Functions class. It is similar to C#'s ?: and VB.NET's IIF(): op1 has to result in a boolean value (0 for false, 1 for true). If 1, op2 is returned, otherwise op3. This results in a SQL CASE statement (or IIf call in the case of MS Access):

CASE WHEN op1=1 THEN op2 ELSE op3 END

IIF takes care of wrapping op1 in a PredicateWrapper, so you can specify a predicate as op1:

Functions.IIF(OrderFields.ShippedDate.IsNull(), "Null", "Has a value")

The above example has as op1 a predicate (ShippedDate IS NULL), and will return "Null" as value if ShippedDate is indeed NULL, otherwise "Has a value". This results in the SQL CASE statement:

CASE WHEN (CASE WHEN [Orders].[ShippedDate] IS NULL 
     THEN 1 ELSE 0 END)=1 THEN 'Null' ELSE 'Has a value' 
END

(of course, parameters will be used for the literal constants specified, the above example has the values in-lined for easy readability)

Tuple comparisons

Some queries require predicates which compare a subset of the fields of a row to a set of values in-memory or from another query. For example, in-memory a collection of Pair<int, int> objects is stored and a query has to check whether a row in the DB is located in that set. In Linq this is doable with a Contains() operation. In QuerySpec, it's done similarly, using IEnumerable<IEntityFieldCore>.In(IEnumerable<X>).

The source enumerable has to contain fields which names/aliases have to be present as properties on X. Each value in the IEnumerable<X> will be used to produce a predicate which compares the fields in IEnumerable<IEntityFieldCore> with And/Or.

Example:

query.Where(new[] 
    {
        OrderDetailFields.OrderId.As("Value1"), 
        OrderDetailFields.ProductId.As("Value2")
    }
    .In(new List<Pair<int, int>>() {{1, 2}, {2,3}, {3,4}});

This example creates a predicate like the following SQL WHERE clause: (values inlined for readability, they'll be converted to parameters in the actual generated query)

WHERE
    ([OrderDetails].[OrderId]=1 AND [OrderDetails].[ProductId]=2) OR
    ([OrderDetails].[OrderId]=2 AND [OrderDetails].[ProductId]=3)