Collection/Joins

◷ Reading Time: 5 minutes

join

Correlates the elements of two sequences based on matching keys, when a key does not exist in the innerList, it will be filtered out. The default equality comparer is used to compare keys

 outerList |join  (innerList, 
                   outerLocalVariable, outerSelector, 
                   innerLocalVariable, innerSelector, 
                   resultLocalVariable = null, resultSelector = null)
  • outerList: Required
  • innerList: Required
  • outerLocalVariable: Required
  • outerSelector: Required
  • innerLocalVariable: Required
  • innerSelector: Required
  • resultLocalVariable: Optional (default = null)
  • resultSelector = Optional (default = null)
  • return: a Tuple<object, object> where Item1 is from outer list and Item2 is from inner list when resultLocalVariable and resultSelector are not set. Otherwise returns a projector that is used by |key monad.

Examples: Let’s consider the following data structure for Animals and Medications:

Animals:

IDBreed
0cat
10dog
22fish

Medication:

IDType
10antibiotic
0sedative
0antihistamine

Join with no result selector:

Sample 1: animal |join (medication,
                        a, a.ID,
                        m, m.ID)
Result: a list with 3 Tuples which in each Item1 is an Animal and Item2 is a Medication

Join with the provided result selector to build a projection:

Sample 2: animal |join (medication,
                        a, a.ID,
                        m, m.ID,
                        r, r|key(Breed: a.Breed, Type: m.Type))
Result: a list with 3 objects that each has Breed and Type properties.
BreedType
catsedative
catantihistamine
dogantibiotic

leftJoin

Correlates the elements of two sequences based on matching keys. When the key does not exist in the innerList it will be null value associated. The result includes all the outer list elements. The default equality comparer is used to compare keys

outerList |leftJoin (innerList,  
                     outerLocalVariable, outerSelector, 
                     innerLocalVariable, innerSelector, 
                     resultLocalVariable = null, resultSelector = null)
  • outerList: Required
  • innerList: Required
  • outerLocalVariable: Required
  • outerSelector: Required
  • innerLocalVariable: Required
  • innerSelector: Required
  • resultLocalVariable: Optional (default = null)
  • resultSelector = Optional (default = null)
  • return: a Tuple<object, object> where Item1 is from outer list and Item2 is from inner list when resultLocalVariable and resultSelector are not set. Otherwise returns a projector that is used by |key monad.

Animals:

IDBreed
0cat
10dog
22fish

Medication:

IDType
10antibiotic
0sedative
0antihistamine

Left join with provided result selector to build a projection:

Sample 1: animal |join (medication,
                        a, a.ID,
                        m, m.ID,
                        r, r|key(Breed: a.Breed, Type: m==null ? null : m.Type))
Result: a list with 4 objects (all animals are included) in which each has Breed and Type properties. Type is null if no key matches from animals to medications.
BreedType
catsedative
catantihistamine
dogantibiotic
fishnull

crossJoin

Joins all the items in the outer list with the items in the inner list.

  outerList |crossJoin  (innerList)
  • outerList: Required
  • innerList: Required

customers:

CodeName
5Sam
6Alex
7Dave

orders:

KeyCodeProduct
7Book
9Game

Cross join the two lists:

Sample: customers|crossJoin (orders)
Result: a list joining each customer with all the orders

[
     {
         "Item1" : {
             "Code" : 5,
             "Name" : "Sam"
         },
         "Item2" : {
             "KeyCode" : 7,
             "Product" : "Book"
         }
     },
     {
         "Item1" : {
             "Code" : 5,
             "Name" : "Sam"
         },
         "Item2" : {
             "KeyCode" : 9,
             "Product" : "Game"
         }
     },
     {
         "Item1" : {
             "Code" : 6,
             "Name" : "Alex"
         },
         "Item2" : {
             "KeyCode" : 7,
             "Product" : "Book"
         }
     },
     {
         "Item1" : {
             "Code" : 6,
             "Name" : "Alex"
         },
         "Item2" : {
             "KeyCode" : 9,
             "Product" : "Game"
         }
     },
     {
         "Item1" : {
             "Code" : 7,
             "Name" : "Dave"
         },
         "Item2" : {
             "KeyCode" : 7,
             "Product" : "Book"
         }
     },
     {
         "Item1" : {
             "Code" : 7,
             "Name" : "Dave"
         },
         "Item2" : {
             "KeyCode" : 9,
             "Product" : "Game"
         }
     }
 ]

groupJoin

Updated on August 24, 2020

Was this article helpful?

Related Articles