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 a COUNT(*). 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.

Important!

A scalar query can also result in a NULL value if there are no rows for instance. To anticipate on this, always make sure you use a nullable return type in your FetchScalar() calls. This is also true for Any() queries, even though it might look the query will always result in a value: due to the way the Any call is converted into SQL it can be that there aren't rows in the targeted table / view and therefore a NULL value is returned.

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


// 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) ?? false;


// 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) ?? 0.0M;


// 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) ?? false;


// 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) ?? false;


// 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) ?? 0.0M;


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