Linq Join datatables on multiple columns

Question

I have a linq statements that takes two datatables and joins them together on the id column of each table. My issue is that sometimes the value that I am joining is in a different column in the datatable and I would like to be able to have the statement as is and to look at the other column as well:

Existing:

Datatable1.AsEnumerable()
           .Join(Datatable2.AsEnumerable(),
               dt1Row => Datatable1.Field<string>(rowid),
               dt2Row=> Datatable2.Field<string>(rowid),
               (dt1Row , dt2Row) => new { dt1Row , dt2Row}).ToList()
           .ForEach(o => {
               o.dt1Row.SetField(o.dt1Row.Table.Columns["name"].Ordinal, o.dt2Row.Field<string>("name1"));

           });

Datatable1.AsEnumerable()
           .Join(Datatable2.AsEnumerable(),
              //Trying to work out?
              ( dt1Row => Datatable1.Field<string>("rowid"),
               dt2Row=> Datatable2.Field<string>("rowid"))
              || ( dt1Row => Datatable1.Field<string>("rowid"),
               dt2Row=> Datatable2.Field<string>("name")),
               (dt1Row , dt2Row) => new { dt1Row , dt2Row}).ToList()

           .ForEach(o => {
               o.dt1Row.SetField(o.dt1Row.Table.Columns["name"].Ordinal, o.dt2Row.Field<string>("name1"));

           });

Amir Sherafatian · Accepted Answer · 2014-05-08 16:44:00Z

2

sorry for delay, you can use this approach:

var dt1 = DataTable1.AsEnumerable();
var dt2 = DataTable2.AsEnumerable();

 var query = (from dt1Row in dt1
              from dt2Row in dt2
              where dt1Row["rowid"] == dt2Row["rowid"] ||
                    dt1Row["rowid"] == dt2Row["name"]

              select new
              {
                  dt1Row,
                  dt2Row
              });

answered May 8, 2014 at 16:44

Amir Sherafatian

2,0932 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amir Sherafatian · Accepted Answer · 2014-05-07 12:44:28Z

0

you can use iif statement:

.Join(Datatable2.AsEnumerable(),
      dt1Row => Datatable1.Field<string>("rowid"),
      dt2Row => condition ? Datatable2.Field<string>("rowid") : Datatable2.Field<string>("name"),
      (dt1Row , dt2Row) => new { dt1Row , dt2Row})

condition must return a boolean value

edited May 7, 2014 at 12:44

answered May 7, 2014 at 12:37

Amir Sherafatian

2,0932 gold badges21 silver badges32 bronze badges

5 Comments

AC25 Over a year ago

can the condition be Datatable1.Field<string>("rowid") == Datatable2.Field<string>("rowid")

Amir Sherafatian Over a year ago

there will not syntax error or compilation error with what you wrote, if you can compare them, you can use that instead of condition, but i don't know about logical error, try to check

Amir Sherafatian Over a year ago

i think you want to join with second DataTable on its rowid, if its equal to rowid from first DataTable, if not, you want to join first DataTable with second DataTable on rowid from first DataTable with name from second DataTable, if i got right, i think you need to change your linq statements probably

AC25 Over a year ago

yes thats right, is their a way to do that in that linq ststement

AC25 Over a year ago

any idea how to change that statement with out having another statement with a join?

AC25 · Accepted Answer · 2014-05-07 14:48:33Z

0

Added another Linq statement in the Join for Datatable2 using any dt.Any() as the condition

Join(Datatable2.AsEnumerable(),
  dt1Row => Datatable1.Field<string>("rowid"),
  dt2Row => Datatable1.AsEnumerable.Any(x => x.Field<string>("rowid") == Datatable2.Field<string>("rowid"))
? Datatable2.Field<string>("rowid") : Datatable2.Field<string>("name"),
  (dt1Row , dt2Row) => new { dt1Row , dt2Row})

answered May 7, 2014 at 14:48

AC25

4231 gold badge7 silver badges24 bronze badges

Collectives™ on Stack Overflow

Linq Join datatables on multiple columns

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related