Natural Join of two DataTables in C#

Question

I have a DataSet that contains two tables. One is considered to be nested in the other.. All I want is for it to not be nested and for there to be one table. .Merge() and LINQ just aren't doing the trick.

Here is a sample of what the main table would look like

student-id ID
--------------------
123456789 1
654987321 2

But each of these has multiple rows that they correspond to in the next table

ID Col1 Col2 etc.
----------------------
1 fact1 fact2
1 fact3 fact4
2 fact5 fact6

I want to combine them so they would look like this...

student-id    Col1    Col2
-------------------------------
123456789    fact1    fact2
123456789    fact3    fact4
654987321    fact5    fact6

Everytime that I try the merge it doesn't work I get an error that I cant duplicate the primary key which is "ID" and since the merge is based on the primary key(i believe) I cant remove it.

I cant use LINQ because I want to make this generic so that the second table could have any number of columns and I cant get the select to work for that.

UPDATE: MY SOLUTION I ended up cloning the second table to a new data table. Then adding a column called 'student-id' and deleting the ID column. The I looped through the rows of the Main table finding and related them to row in the second table... Combined all the data in an array and created a row in the final table.

look for some examples here [Inner join of DataTables in C#](http://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp) — Roman Pekar, Jul 02 '13 at 20:14
@RomanPekar the only problem I have with that example is that it specifies the columns, I want to do this so that I don't have to select every column... There are going to be a wide variety of number of columns, and they all will have different names — JBlaze, Jul 02 '13 at 20:34

Darren Wainwright · Answer 1 · 2013-07-02T20:30:24.220

0

Sounds like what you need is a Pivot table.

This will essentially allow you to display the data how you want.

Here are a couple of tutorials/projects

http://www.codeproject.com/Articles/25167/Simple-Advanced-Pivots-with-C-and-ASP-NET http://www.codeproject.com/Articles/46486/Pivoting-DataTable-Simplified

Update

you may find yourself better doing the 'pivot' part in MS SQL as stored procedure and then populating your datatable with the results of calling this stored procedure. This example here is a great starting block

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

edited Jul 02 '13 at 20:30

answered Jul 02 '13 at 20:10

Darren Wainwright

30,247
21
76
127

Does it matter that my data isn't numeric and its all strings. Also the final table (the third one) needs to be of the type DataTable is the pivot table still useful? – JBlaze Jul 02 '13 at 20:24
A pivot table isn't just a .net thing - it's a way to display data. You essentially want to be able to display each student and their 'facts' without repeating students. This is what a pivot table can do. You can populate the datatable with the results of the pivot. I will find a good SQL tutorial for you - this might be much easier to have SQL create the pivot and you can just populate the Datatable with it's response. – Darren Wainwright Jul 02 '13 at 20:29
Just to make sure we are clear when it comes to "repeating students" I want the student-id to appear twice in the final table for the example, fact1 & fact2 are separate from fact3 & fact4 – JBlaze Jul 02 '13 at 20:42

score 0 · Accepted Answer · answered Jul 02 '13 at 20:14

0

The LINQ isn't as bad as you suggest. You can just use an anonymous type that holds two DataRows:

var result = from t1 in table1.AsEnumerable()
             join t2 in table2.AsEnumerable() on (int)t1["ID"] equals (int)t2["ID"]
             select new
             {
                 Student = t1,
                 Facts = t2
             };
foreach(var s in result)
    Console.WriteLine("{0} {1} {2}", s.Student["student-id"], s.Facts["Col1"], s.Facts["Col2"]);

That way, you're not including specific columns in your output, so you can access them after the fact.

That being said, the other poster's suggestion of using a pivot table is probably a better direction to go.

answered Jul 02 '13 at 20:14

Curtis Rutland

776
4
12

But will I be able to join those two row to create one row in the final table? I also need the final table to be a DataTable – JBlaze Jul 02 '13 at 20:22
Well, if we're working under the assumption that you don't know the schema (or want it to be generic enough to work without providing the specific columns), then no. If you do know the columns, then it would be much easier to re-constitute these rows into a DataTable. – Curtis Rutland Jul 02 '13 at 20:30
I know the columns, its just that there are about 8 combinations of columns that could come in.. all of which have between 16 and 34 columns so it would be easier to make it generic obviously.. but if it cant work with a generic method then I can do it another way. – JBlaze Jul 02 '13 at 20:38
I do have another question here...is this data coming from a database? If so, you'd probably be better off writing a view that handles the join. Then you can just query your DB view with the exact columns you want, and not have to worry about performing a join in memory. – Curtis Rutland Jul 02 '13 at 20:46
Data is coming from files that are being uploaded – JBlaze Jul 02 '13 at 20:58

score 0 · Answer 3 · answered Jul 02 '13 at 20:21

0

let's try it in SQL.

Let, 1st Table = Table1 and 2nd Table = Table2

SQL:

Select
    X.student-id,Y.Col1,Y.Col2
From Table1 As X Inner Join Table2 As Y On Y.ID=X.ID

I think if you try it in SQL it's easy to do!!!

answered Jul 02 '13 at 20:21

csharpbd

3,786
4
23
32

If you don't know the schema of the two tables (a premise of the question) then no, it's *not* easy to do in SQL. – Servy Jul 02 '13 at 20:22

Natural Join of two DataTables in C#

3 Answers3