◷ Reading Time: 6 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:
| ID | Breed |
|---|---|
| 0 | cat |
| 10 | dog |
| 22 | fish |
Medication:
| ID | Type |
|---|---|
| 10 | antibiotic |
| 0 | sedative |
| 0 | antihistamine |
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.
| Breed | Type |
|---|---|
| cat | sedative |
| cat | antihistamine |
| dog | antibiotic |
Join operators also can eliminate result alias to build a projection:
Sample 2: animal |join (medication,
a, a.ID,
m, m.ID,
{Breed: a.Breed, Type: m.Type})
Result: a list with 3 objects that each has Breed and Type properties.
| Breed | Type |
|---|---|
| cat | sedative |
| cat | antihistamine |
| dog | antibiotic |
Join operators can have an array selector e.g. [alias.*, alias.PropertyName]:
Inside the array selector, you can specify properties of the object or if you want to project all the properties, you can use alias.*
Sample 2: animal |join (medication,
a, a.ID,
m, m.ID,
[a.Breed, m.Type])
Result: a list with 3 objects that each has Breed and Type properties.
| Breed | Type |
|---|---|
| cat | sedative |
| cat | antihistamine |
| dog | antibiotic |
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:
| ID | Breed |
|---|---|
| 0 | cat |
| 10 | dog |
| 22 | fish |
Medication:
| ID | Type |
|---|---|
| 10 | antibiotic |
| 0 | sedative |
| 0 | antihistamine |
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.
| Breed | Type |
|---|---|
| cat | sedative |
| cat | antihistamine |
| dog | antibiotic |
| fish | null |
crossJoin
Joins all the items in the outer list with the items in the inner list.
outerList |crossJoin (innerList)
- outerList: Required
- innerList: Required
customers:
| Code | Name |
|---|---|
| 5 | Sam |
| 6 | Alex |
| 7 | Dave |
orders:
| KeyCode | Product |
|---|---|
| 7 | Book |
| 9 | Game |
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
Joins the elements of two lists based on key equality, and groups the results.
outerList |groupJoin (innerList,
outerLocalVariable, outerSelector,
innerLocalVariable, innerSelector)
- outerList: Required
- innerList: Required
- outerLocalVariable: Required
- outerSelector: Required
- innerLocalVariable: Required
- innerSelector: Required
Example:
customers:=[{Code: 5, Name: "Sam"}, {Code: 6, Name: "Dave"}, {Code: 7, Name: "Julia"}, {Code: 8, Name: "Sue"}, {Code:8077, Name: "Arash"}]
orders := [{KeyCode: 5, Product: 'Book'}, {KeyCode: 6, Product: 'Game'}, {KeyCode: 7, Product: 'Computer'}, {KeyCode: 7, Product: 'Mouse'}, {KeyCode: 8, Product: 'Shirt'}, {KeyCode: 5, Product: 'Underwear'}]
customers|groupJoin (orders,
c, c.Code,
o, o.KeyCode,
r, r|key(c:c, r:r))
|selectMany(z, z.r|defaultIfEmpty()
|select(m, m|key(Customer:z.c, Order:m))
)