Help with linq lambda groupJoin

[Jose_G (José G)]

Hi everyone,

I need to join two diferent datatables with one column in common. I need almost all columns from one, and all columns from another. Im trying with groupJoin Lambda, but i can get it. It trows an anonimous object, how can i get a datarow to add it to the output datatable?

query
dtUno.AsEnumerable.GroupJoin(dtDos.AsEnumerable, Function(lef) lef(“id”), function(rig) rig(“id”), function(lef, rig) new With {.id = lef, .key = rig } )

dt1
id fecha precio tamaño
1 2024 56$ xxl
2 2024 86$ xl
3 2024 76$ l

dt2
id provedor sku tienda
1 mkp 45632 izcalli
2 rko 48632 portobello
3 lok 75632 puebla

Output
id fecha precio tamaño provedor sku
1 2024 56$ xxl mkp 45632
2 2024 86$ xl rko 48632
3 2024 76$ l lok 75632

[ashokkarale]

@Jose_G,

I haven’t tried it and it’s from LLM but give it a try. Use this in Invoke code. activity

Dim result As New DataTable()
result.Columns.Add("id", GetType(Integer))
result.Columns.Add("fecha", GetType(String))
result.Columns.Add("precio", GetType(String))
result.Columns.Add("tamaño", GetType(String))
result.Columns.Add("provedor", GetType(String))
result.Columns.Add("sku", GetType(String))

Dim query = From rowUno In dtUno.AsEnumerable()
            Join rowDos In dtDos.AsEnumerable()
            On rowUno.Field(Of Integer)("id") Equals rowDos.Field(Of Integer)("id")
            Select result.LoadDataRow(New Object() {
                rowUno.Field(Of Integer)("id"),
                rowUno.Field(Of String)("fecha"),
                rowUno.Field(Of String)("precio"),
                rowUno.Field(Of String)("tamaño"),
                rowDos.Field(Of String)("provedor"),
                rowDos.Field(Of String)("sku")
            }, False)

query.CopyToDataTable(result, LoadOption.OverwriteChanges)

[Jose_G (José G)]

thanks for the reply, but this can also be done in linq querysh using an assign activity, but the real dts are way more larger, like 30 and 45 columns. So its kinda complicated to build that output datatable. So im searching a lambda linq solution.

[ppr (Peter Preuss)]

switch to the Query Syntax

[HowTo] - First Start with LINQ (VB.Net)

LINQ is part of the .NET framework and offers capabilities for interacting with data, objects or other sources within a particular syntax. This HowTo introduces on starting with LINQ (Language-Integrated Query). Current Version of this topic is DRAFT and will be finalized / enhanced soon Introduction Typical tasks for implementing parts of an automation project are interacting with e.g. with data in a datatable or items from collections. LINQ can help to implement it into a compact and standa…

@Jose_G

but the real dts are way more larger, like 30 and 45 columns

share with us

• the column structure from left and right table
  and let us know:
• the needed join column
• the needed column from table (left) and table (right)

UPD1 - Preview on options
there are several techniques to construct the row Array without the need to mention each column individually

used on the Datarow.ItemArray:

[HowTo] LINQ - First Start # Skip | Take | SkipWhile | TakeWhile Operator

This HowTo gives an introductory overview of the partition Operators: Skip, Take, SkipWhile, TakeWhile Introduction The partition operators are used to fetch a particular subset from a set of items. The returned subset is formed by the contiguous items that are matching the provided condition. Skip / Take Operator The Skip Operator will omit the subsequent items from the given start for a given length and will return the remaining items The Take Operator will return the subsequent items fr…

[HowTo] LINQ - First Start # Concat | Append | Prepend Operator

This HowTo gives an introductory overview of the conjunction operators: Concat, Append, Prepend Introduction The conjunction operators are used to add the item(s) to a particular set of items. Concat, Append, Prepend Operator The Concat Operator combines a set of items with another set of items The Append Operator adds an item to a set of items on the end The Prepend Operator adds an item to a set of items on the begin Overview I – Input P – Processing O – Output {"A","B"} / {"C",…

[SorenB]

Hello @Jose_G

If the example is a true representation of the datatables in context of the amount of columns, then you could perhaps use the built-in activities for Join data table and Remove data column for the columns you dont want.
Alternatively use Filter data table (after joining) with your desired output columns defined.

If the amount of columns is very large then this of course explains why you would look for another solution.

Regards
Soren