0

I am trying to join two data tables based on ID column but my problem is one of my data table has variable column count depending on request.

First DataTable (This will always have fixed number of columns)- 

 ID Int,
 firstCol Varchar,
 SecondCol Varchar

Second DataTable (This can have variable number of columns depending of request) -

 ID Int,
 dynamicCol1 varchar,
 dynamicCol2 varchar,
 dynamicCol3 varchar,
 // More or less columns depending on request

Now I want to join these two data Tables which should return me result if possible in the form of dataTable consisting all the columns from both tables.

Resultant Table or enumerable datarow list - 

 ID Int,
 firstCol Varchar,
 SecondCol Varchar
 dynamicCol1 varchar,
 dynamicCol2 varchar,
 dynamicCol3 varchar,
 // More or less columns depending on request

I tried following linq it joins these tables but I am not sure how to select all the columns from both tables. I tried following linq,

//submissionData - First datatable
//responseData  - Second datatable
Dim resultData = From subData In submissionData.AsEnumerable()
                 Join resData In responseData.AsEnumerable()
                 On subData.Field(Of Integer)("ID") Equals resData.Field(Of Integer)("ID")
                 Select result = {resData, subData}
                 // Understandably this returns list of set of data rows separately. Which I have to flatten using more code.

My question is what do I select to get result I want,

    Select result = {// what should do here to get the desired result instead of getting my result which needs more code to flatten it out}

I can off course write more code and flatten the result which I am getting now but I was wondering is there any better way to get this result as I dont have enough knowledge of Linq. Any pointers/help would be really appreciated. C# based help will also work. In case of any confusion query please feel free to comment. Thanks in advance.

Mahesh
  • 8,694
  • 2
  • 32
  • 53
  • 1
    So far I did not found a solution for my idea, but my thoughts are: why not creating the linq string and then invoke it, let' say, like reflection? – muffi Jun 09 '17 at 05:04
  • What is the data type of responseData? – Robert McKee Jun 09 '17 at 05:24
  • @RobertMcKee those both are datatables where `submissionData` is datatable1 and `responseData` is datatable2 from description – Mahesh Jun 09 '17 at 05:25
  • @muffi I am not sure How much efficient that is and how much efforts it need. Rather than doing that isn't it better to just flatten the datarow sets. Irrespective to that do you have any links or pointers regarding that. – Mahesh Jun 09 '17 at 05:27
  • In that case, since your data is already a datatable, I wouldn't use LINQ at all, and just clone the responseData, add the fixed columns, convert the first datatable to a dictionary, then loop through the new datatable and set the values. – Robert McKee Jun 09 '17 at 05:30
  • @RobertMcKee Actually the main reason I used Linq is because of the `JOIN` otherwise I would have to do the join using loops as well. – Mahesh Jun 09 '17 at 05:32
  • LINQ is just going to do something similar anyhow in the background. Ultimately you need to stuff the data into some kind of construct that can contain your variable data. That's typically a `DataTable`, a `IEnumerable`, or perhaps `IEnumerable>` – Robert McKee Jun 09 '17 at 05:40
  • You could probably use https://stackoverflow.com/a/11505884/856353 but the implementation is pretty bad, so if either of these datatables can have a large amount of data, consider optimizing it by using a dictionary/hashset for quicker lookups. For small datatables I'm sure it would be fine, even if suboptimal, however. – Robert McKee Jun 09 '17 at 05:47

1 Answers1

-1

Look for DataTable.Merge. It merges the two tables by the ID of each table.

Twelve
  • 1
  • 2