Subexpressions

When you’re writing a query, you may find that an operation over an associated collection comes up repeatedly.  For example, you may want to perform a calculation over the collection, and both order and filter by the result of this calculation.  To avoid retyping the common expressions each time – and having the database engine re‑evaluate them each time – you can define subexpressions for them, then use these subexpressions as if they were actual attributes of the entity.

To define a subexpression, add it to the Query.Subexpressions collection.  You must specify the name of the subexpression, the query expression it encapsulates, and the column in the target table on which to join to the main table.

query.Subexpressions.Add(
    "TotalFreight", // name
  Entity.Attribute("Orders.Freight").Function("SUM"), // expression
    "CustomerId"); // join column

Once a subexpression is defined, you can use it by specifying its name.  You can use a subexpression name anywhere you would normally use a field name – typically in a query expression, sort order or projection.

Query query = new Query(typeof(Client));
// Define TotalFreight and AverageValue subexpressions
query.Subexpressions.Add(
  "TotalFreight",
    Entity.Attribute("Orders.Freight").Function("SUM"),
  "CustomerId");
query.Subexpressions.Add(
  "AverageValue",
    Entity.Attribute("Orders.Value").Function("AVG"),
  "CustomerId");
// Order by the aggregates
query.Order = Order.By("TotalFreight").AndBy("AverageValue");
// Filter by the aggregates
query.QueryExpression =
  Entity.Attribute("TotalFreight") > 10000
    && Entity.Attribute("AverageValue") < 9000;
// Use the aggregates in a projection
query.Projection.Add("Name");
query.Projection.Add("TotalFreight");
query.Projection.Add("AverageValue");
var clientsPayingHighFreightForLittleValue = _unitOfWork.Project<FreightSummary>(query);

In LINQ, you can create sub expressions using the let keyword; no special APIs are required.