3
var collection = from t1 in dt1.AsEnumerable()
                 join t2 in dt2.AsEnumerable()
                 on t1["id"] equals t2["id"]
                 select new { Name = t1["name"], Group = t2["group"] };

I want to select all columns of both table like join in SQL Server inner join query.

In Addition How can i convert whole result of both tables to data-table?

andrew subash
  • 51
  • 1
  • 8
Himanshu Suthar
  • 161
  • 1
  • 5
  • 19

3 Answers3

5
var collection = from t1 in dt1.AsEnumerable()
             join t2 in dt2.AsEnumerable()
             on t1["id"] equals t2["id"]
             select new { T1 = t1, T2 = t2 };

then...

EDIT:

Something along those lines

//clone dt1, copies all the columns to newTable 
DataTable newTable = dt1.Clone();

//copies all the columns from dt2 to newTable 
foreach(var c in dt2.Columns)
    newTable.Columns.Add(c);

//now newTable has all the columns from the original tables combined

//iterates over collection
foreach (var item in collection) {
    //creates newRow from newTable
    DataRow newRow = newTable.NewRow();
    //iterate the columns, gets values from either original table if column name is there
    foreach(var c in newTable.Columns)
        newRow[c.ColumnName] = item.T1.ContainsColumn(c.ColumnName) ?  item.T1[c.ColumnName] : item.T2[c.ColumnName];
    newTable.Rows.Add(newRow);
}

This will work. But if dt1 and dt2 share multiple columns with the exact same name, you might have some loss of data.

DesertFox
  • 768
  • 1
  • 4
  • 6
  • This how i will able to access row one by one. :( i want to convert result to data-table. How can i convert whole result of both tables to data-table? – Himanshu Suthar Apr 20 '17 at 04:56
  • Snippet done. I highly recommend you use a less automated version (manually naming each column from the inner join). As I mentioned in the answer, if dt1 and dt2 have columns with the same name, it will only get the value of one of them (dt1) and not the other. – DesertFox Apr 20 '17 at 05:14
1

While you can't expand them to columns, you can simply return the entities. Eg:

select new { CTLJCRJOB, CTLRFDSTM }

If you need it flattened, then you will have to write out the mapping yourself, but will still be very trivial.

Referenced from:

Select All columns for all tables in join + linq join

ou have to specify each manually if you want to project into a flattened type. Your other option is to just have your combined type contain both objects, and the objects will naturally bring along their properties.

select new 
{
    Object1 = object1,
    Object2 = output
};

And you would work with it like myObj.Object1.Property1, myObj.Object2.Property4, etc.

One final option that still involves some manual work is to define an appropriate type and have a constructor or a builder method that does the work of segmenting out your object properties into a flattened type. You still perform the manual mapping, but you isolate it from your query logic.

select new CombinedType(object1, output);
//or 
select builder.GetCombinedType(object1, output);

Referenced From

Select all columns after JOIN in LINQ

Community
  • 1
  • 1
andrew subash
  • 51
  • 1
  • 8
  • You should never post a link-only answer as it will be invalid if the URL pasted here is dead. Refer [this](https://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers) – PM. Apr 20 '17 at 05:05
  • Thanks for pointing my mistake I never knew such protocol exists. I'll edit my answer even though @DesertFox given the answer already. – andrew subash Apr 20 '17 at 05:11
0
var collection = (from t1 in dt1.AsEnumerable()
                 join t2 in dt2.AsEnumerable()
                 on t1  ["id"] equals t2  ["id"]
                 select new { Name = t1  ["name"], Group = t2  ["group"] }).ToList() ;

Hope this will help

Chad C
  • 1
  • My table containing 62 columns. How can i specify columns like this one by one? name, group etctetc – Himanshu Suthar Apr 20 '17 at 05:09
  • If you can better to retrieve the data using and SP or a basic query and then manipulate them as an object. I don't think it is a good idea to get all the 62 columns in linq query – Chad C Apr 24 '17 at 04:09