How to get columns of both datatable after innerjoining them using linq

Question

My linq is like below

from dt1 in dsResults.Tables[0].AsEnumerable()
join dt2 in dsResults.Tables[1].AsEnumerable()
 on dt1 .Field<decimal>("RecordId") equals dt2.Field<decimal>("RecordId2")
 select dt1 ;

This will retrieve only columns of dt1 . How can i make columns of both table appear ?

score 2 · Accepted Answer · edited May 23 '17 at 11:58

I could do this as below follwing the thread Create combined DataTable from two DataTables joined with LINQ. C#

     DataTable targetTable = dsResults.Tables[0].Clone();
        var dt2Columns = dsResults.Tables[1].Columns.OfType<DataColumn>().Select(dc =>
            new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
        targetTable.Columns.AddRange(dt2Columns.ToArray());
        var rowData =
            from row1 in dsResults.Tables[0].AsEnumerable()
            join row2 in dsResults.Tables[1].AsEnumerable()
                on row1.Field<decimal>("RecordId") equals row2.Field<decimal>("RecordId2")
            select row1.ItemArray.Concat(row2.ItemArray).ToArray();
        foreach (object[] values in rowData)
            targetTable.Rows.Add(values);

Massimiliano Peluso · Answer 2 · 2012-09-13T09:33:13.360

0

you could create an object representing the columns you want to show then you can do the below:

public class YourObject
{
 public string Pror1 {get;set;}
 public string Pror2 {get;set;}
}



List<YourObject> result=from row1 in dsResults.Tables[0].AsEnumerable()
    join row2 in dsResults.Tables[1].AsEnumerable()
     on row1.Field<decimal>("RecordId") equals row2.Field<decimal>("RecordId2")
     select new YourObject()
     {
       Pror1=row1.prop1,
       Prop2=row2.prop2,
       ......
     }.ToList();

if you want to select all the column you can do the below

 from row1 in dsResults.Tables[0].AsEnumerable()
        join row2 in dsResults.Tables[1].AsEnumerable()
         on row1.Field<decimal>("RecordId") equals row2.Field<decimal>("RecordId2")
         select new { RowTable1 = row1, RowTable2 = row2}; //anonymous type

In this case each element of the result will have two properties: RowTable1 will be a row from row1, and RowTable2 will be the matching row from row2

edited Sep 13 '12 at 09:33

answered Sep 13 '12 at 09:25

Massimiliano Peluso

26,379
6
61
70

i know this . But i cant write each columns der since there are more than 200 and column names change based on parameter to stored procedure – Kuntady Nithesh Sep 13 '12 at 09:27
How can i combine them and have as one ? – Kuntady Nithesh Sep 13 '12 at 09:36
basically wat i want is join two data tables and get the columns of both similar to select * in sql . Is there any way to do this ? – Kuntady Nithesh Sep 13 '12 at 09:38
I got what you are trying to achieve but you need to write some code to do that : a type which will represent your row item – Massimiliano Peluso Sep 13 '12 at 09:39

Aghilas Yakoub · Answer 3 · 2012-09-13T09:45:18.217

0

1 You can adjust you select clause with anonymous type , and inclue your needed columns with Field properties

from dt1 in dsResults.Tables[0].AsEnumerable()
join dt2 in dsResults.Tables[1].AsEnumerable()
on dt1 .Field<decimal>("RecordId") equals dt2.Field<decimal>("RecordId2")
 select new
 {
   Property1 = dt1 .Field<decimal>("RecordId"),
   Property2 = dt2 .Field<decimal>("RecordId2")
   ...... 

  }
;

2 If you don't want specify column name

from dt1 in dsResults.Tables[0].AsEnumerable()
join dt2 in dsResults.Tables[1].AsEnumerable()
on dt1 .Field<decimal>("RecordId") equals dt2.Field<decimal>("RecordId2")
 select new
 {
   dt1,
   dt2 
  }
;

3 If you want set Union between your sequence

Link : http://msdn.microsoft.com/en-us/library/bb386993.aspx

edited Sep 13 '12 at 09:45

answered Sep 13 '12 at 09:26

Aghilas Yakoub

28,516
5
46
51

still i need to mention each particular name here . I dont want that – Kuntady Nithesh Sep 13 '12 at 09:30
This will give two properties for each row . How can i Merge them into one ? – Kuntady Nithesh Sep 13 '12 at 09:36
You can use Union operator of Linq – Aghilas Yakoub Sep 13 '12 at 09:40

score 0 · Answer 4 · edited May 20 '14 at 08:59

  public string ShowNew(string myName)
        {
            RENTALEntities objD = new RENTALEntities();
            var NewObj =
                (from t in objD.TENANTs.ToList()
                 join pt in objD.PROP_TYPE.ToList()
                    on t.Prop_ID equals pt.Prop_ID
                 where t.Surname == myName
                 select new { t.Surname, t.PropAdress, pt.DESCRIPTION, t.RentalIncome }).First();

            return "SWurname" + NewObj.Surname + "<br/>"
                + "Address" + NewObj.PropAdress + "<br/>"
                + "Description" + NewObj.DESCRIPTION + "<br/>"
                + "Rental income" + NewObj.RentalIncome;
        }

How to get columns of both datatable after innerjoining them using linq

4 Answers4