Free Joins

• Jan 05, 2023
• 3 minutes to read

With XPO, you can build criteria based on persistent objects that are not directly related (don’t have explicitly defined associations) using Free Joins. In essence, XPO allows you to join any persistent objects on a condition, calculate aggregate functions against matching objects using their properties, and return aggregate values as the result of joining. To accomplish this, use JoinOperands in your criteria.

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

  VB.NET

  Dim criteria As CriteriaOperator = _
  CriteriaOperator.Parse("[<Orders>][^.EmployeeID = EmployeeID].Count() > 50")
  Dim employees As New XPCollection(Of 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);
  

  VB.NET

  Dim joinCriteria As CriteriaOperator _
  = New OperandProperty("^.EmployeeID") = New OperandProperty("EmployeeID")
  Dim joinOperand As New JoinOperand( _
  "Orders", joinCriteria, Aggregate.Count, New OperandProperty("EmployeeID"))
  Dim criteria As New BinaryOperator(joinOperand, New OperandValue(50), BinaryOperatorType.Greater)
  Dim employees As New XPCollection(Of 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
  ;
  

  VB.NET

  Dim employees As New XPQuery(Of Employee)(session)
  Dim orders As New XPQuery(Of Orders)(session)
  Dim list = From e In employees _
             Group Join o In orders On e.EmployeeID Equals o.EmployeeID.EmployeeID Into query = Group _
             Where query.Count() > 50 _
             Select e