2

Lets say I have 2 tables both of them contain dynamic columns and I wish to retrieve a collection of datarow with all the columns from both the tables(later i will bind it to a grid view) after performing left outer join.

Sample Query:

var query = from TableA in ds.Tables[0].AsEnumerable()
            join TableB in ds.Tables[1].AsEnumerable() on new { col1 = TableA.Field<Int32>("colA"), col2 = TableA.Field<DateTime>("colB") }
            equals new { col1 = TableB.Field<Int32>("colA"), col2 = TableB.Field<DateTime>("colB") }
            into GJ
            from sub in GJ.DefaultIfEmpty()
            select TableA;

Problem: I want to select tableA and tableB together. The above sample query works and it populates all columns of tableA after left outer join. But i wish to retrieve all the columns from both the tables. Please advice.

Aliostad
  • 80,612
  • 21
  • 160
  • 208
Alex
  • 23
  • 1
  • 1
  • 3

1 Answers1

3

Just select both parts into an anonymous type:

var query = from TableA in ds.Tables[0].AsEnumerable()
            join TableB in [ ...] on [...] equals [...] into GJ
            from sub in GJ.DefaultIfEmpty()
            select new { RowA = TableA, RowB = sub };

Each element of the result will have two properties: RowA being a row from TableA, and RowB being a matching row from TableB or null if no rows from TableB matches RowA.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks for your answers. Lets say these are the columns of Table A (ColumnA, columnB, ColumnC) and the columns of TableB (ColumnA, ColumnB, ColumnD, ColumnE). So i wish to extract a datarow collection with all columns (ColumnA, ColumnB, ColumnC, ColumnD, ColumnE). A output very similar to select * from tableA left outer join Tableb on [..some join..] ... Can you elaborate on how i can achive it. – Alex Oct 15 '10 at 08:46
  • @Alex: Well you *could* flatten them out in the select clause... but why not just keep the two rows separately? – Jon Skeet Oct 15 '10 at 08:49
  • My reason: got lots of queries I'm adding joins to (which had none before), lots of AutoMapper maps, and not a lot of time :(. – Adam Plocher Aug 02 '17 at 12:29