join between two tables with linq to datasets

Question

i want to create a join between two tables and that the result will include all the two tables columns. i want to do this without specifying the specific column names, just do select all, because i won't know how many columns will the two tables include and won't know their names.

I JUST WANT TO CREATE JOIN BETWEEN TWO TABLES AND THAT THE RESULT WILL INCLUDE THE BOTH TABLE COLUMNS.

How do i do that ? what is the result type should be?

i tried:

var res = from t1 in ds1.Tables["Table1"].AsEnumerable()
           join
          t2 in ds1.Tables["Table2"].AsEnumerable()
           on t1.Field<string>("TrID") equals t2.Field<string>("TrID")
                select new { t1, t2 };

but i get a strange type as a result that i can't do anything with, i can't convert it to dataTable,Dataset or present it in gridview.

For example, get a table with the columns [TrID],[SP],[SP2] without specificly create an anonymous type with these three columns:

Table1 TrID SP Asaf1 Asaf2 F1 K2 Asaf1 D2 H1 F1

Table2
TrID SP2 Asaf1 Dor2 Asaf1 U1 R1 W1 I1 Y3

Answer 1

There's no way to magically merge your two tables. If you want a new DataTable, you'll need to create a new one with the appropriate columns and add rows accordingly. Alternatively, you can indeed bind an anonymous type to a control, but not with your current anonymous type of two DataRow properties. Instead, you will need to extract the fields you want:

var res = from t1 in ds1.Tables["Table1"].AsEnumerable()
          let id = t1.Field<string>("TrID")
          join t2 in ds1.Tables["Table2"].AsEnumerable()
            on id equals t2.Field<string>("TrID")
                select new {
                    TrID = id,
                    SP = t1.Field<string>("SP"),
                    SP2 = t2.Field<string>("SP2")
                };

Then you can set res as your datasource and bind to TrID, SP and SP2.