Scalar query creation and fetching
Queries can be converted to scalar queries. This can be necessary when a query is guaranteed to return a single value and that value has to be used in e.g. a predicate. To convert a query to a scalar query, use any of the following methods
-
Append the query with .CountRow() (or CountRowBig()). This will
convert the query in a
SELECT COUNT(*) FROM
query construct. For EntityQuery<T> instances, it will simply replace the original entity projection with aCOUNT(*)
. For DynamicQueries, it will wrap the query. -
Append the query with an Aggregate method The following are
aggregate methods, which all require a parameter (field
or expression)
- Avg(parameter). Will produce the average of the parameter element over the query called on. Distinct variant: AvgDistinct
- Count(parameter). Will produce the count of the parameter element over the query called on. Distinct variant: CountDistinct.
-
CountBig(parameter). Same as Count(parameter) but now
uses
COUNT_BIG()
, a SQL Server specific aggregate function - Max(parameter). Will produce the max value of the parameter element over the query called on.
- Min(parameter). As Max() but now will produce the min value of the parameter element.
- StDev(parameter). Will produce the standard deviation of the parameter element over the query called on. Distinct variant: StDevDistinct()
- Sum(parameter). Will produce the summation of the parameter element over the query called on. Distinct variant: SumDistinct()
- Variance(parameter). Will produce the variance of the parameter element over the query called on. Distinct variant VarianceDistinct()
- Call .ToScalar() on the query. This will produce a scalar query with the first element in the projection as the field to return. An overload accepts a boolean whether to specify a row limit clause.
To enforce a row limit, the method .ForceRowLimit() can be called on a scalar query expression. Row limits are required if the scalar query can potentially return multiple elements and just 1 element is required.
Fetching Scalar queries
Fetching scalar queries is possbly by using a wrapping Select() call. Below are several examples of constructing and fetching scalar queries directly.
var qf = new QueryFactory();
// Scalar query which fetches a boolean using Any().
var q = qf.Create().Select(qf.Customer.Where(CustomerFields.CustomerId.Equal("CHOPS")).Any());
var exists = adapter.FetchScalar<bool>(q);
// Another scalar query using Any() fetching a boolean.
var q = qf.Create()
.Select(qf.Customer.Where(CustomerFields.Country.Equal("Germany"))
.Any(CustomerFields.CustomerId.Equal("ALFKI")));
var exists = adapter.FetchScalar<bool>(q);
// Fetching the total of all orders.
var q = qf.Create()
.Select(qf.Customer.As("C")
.Select(CustomerFields.CustomerId,
qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.Select(qf.OrderDetail
.CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId)
.Select(OrderDetailFields.Quantity * OrderDetailFields.UnitPrice)
.Sum().As("OrderTotal"))
.Sum().As("TotalAllOrders"))
.Max());
var result = adapter.FetchScalar<decimal>(q);
// Fetching the rowcount of a query defined separately.
var q = qf.Create()
.Select(CustomerFields.CustomerId, CustomerFields.CompanyName,
OrderFields.OrderId, OrderFields.OrderDate)
.From(qf.Customer.InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId));
var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
var qf = new QueryFactory();
// Scalar query which fetches a boolean using Any().
var q = qf.Create().Select(qf.Customer.Where(CustomerFields.CustomerId.Equal("CHOPS")).Any());
var exists = new TypedListDAO().GetScalar<bool>(q, null);
// Another scalar query using Any() fetching a boolean.
var q = qf.Create()
.Select(qf.Customer.Where(CustomerFields.Country.Equal("Germany"))
.Any(CustomerFields.CustomerId.Equal("ALFKI")));
var exists = new TypedListDAO().GetScalar<bool>(q, null);
// Fetching the total of all orders.
var q = qf.Create()
.Select(qf.Customer.As("C")
.Select(CustomerFields.CustomerId,
qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.Select(qf.OrderDetail
.CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId)
.Select(OrderDetailFields.Quantity.Mul(OrderDetailFields.UnitPrice))
.Sum().As("OrderTotal"))
.Sum().As("TotalAllOrders"))
.Max());
var result = new TypedListDAO().GetScalar<decimal>(q, null);
// Fetching the rowcount of a query defined separately.
var q = qf.Create()
.Select(CustomerFields.CustomerId, CustomerFields.CompanyName,
OrderFields.OrderId, OrderFields.OrderDate)
.From(qf.Customer.InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId));
var count = new TypedListDAO().GetScalar<int>(qf.Create().Select(q.CountRow()), null);