Free Joins (Query Non-Associated Data & Calculate Aggregations)

• Nov 01, 2024
• 2 minutes to read

Free Joins allows you to build criteria based on persistent objects that are not directly related (don’t have explicitly defined associations). You can:

• join any persistent objects on a condition;
• calculate aggregate functions against matching objects using their properties;
• return aggregate values as the result of joining.

To accomplish this, use JoinOperands in your criteria.

Note

You can use Free Joins in XPO and EF Core-based XAF applications.

Options for creating the JoinOperand include:

• The CriteriaOperator.Parse method.

  The general format of the method’s criteria string parameter is: [<JoinTypeName>][JoinCondition]

  Here, JoinTypeName represents the JoinOperand.JoinTypeName property value, while JoinCondition is a string representation of the CriteriaOperator equivalent of the join condition.

  To indicate properties of the parent object within the join condition, prefix them with a caret and dot (‘^.‘), as shown below:

  [<JoinTypeName>][[^.ParentObjectProperty] = [JoinedObjectProperty]]

  The following code snippet demonstrates how to retrieve employees that closed more than 50 orders.

  C#

  CriteriaOperator criteria = 
      CriteriaOperator.Parse("[<Orders>][^.EmployeeID = EmployeeID].Count() > 50");
  XPCollection<Employee> employees = new XPCollection<Employee>(session, criteria);
  

• The JoinOperand constructors.

  You can use specific constructors to create the JoinOperand, and initialize it with join and aggregate settings via corresponding constructor parameters.

  C#

  CriteriaOperator joinCriteria = 
      new OperandProperty("^.EmployeeID") == new OperandProperty("EmployeeID");
  JoinOperand joinOperand = 
      new JoinOperand("Orders", joinCriteria, Aggregate.Count, new OperandProperty("EmployeeID"));
  BinaryOperator criteria = 
      new BinaryOperator(joinOperand, new OperandValue(50), BinaryOperatorType.Greater);
  XPCollection<Employee> employees = 
      new XPCollection<Employee>(session, criteria);
  

  Note that if a criteria contains types from different namespaces, the JoinOperand.JoinTypeName propery should contain the full type name with its namespace, for example, as follows:

  "[<Application.SomeNamespace.Customers>][^.GroupID = GroupID].Count() > 1 
  and  [<Application.OtherNamespace.Orders>][^.EmployeeID = EmployeeID].Count() > 50"
  

• The Join operation in LINQ to XPO.

  The syntax is the same as the syntax of group joins (the JoinGroup method) in LINQ.

  C#

  XPQuery<Employee> employees = new XPQuery<Employee>(session);
  XPQuery<Orders> orders = new XPQuery<Orders>(session);
  var list = from e in employees
             join o in orders on e.EmployeeID equals o.EmployeeID.EmployeeID into query
             where query.Count() > 50
             select e
  ;