Attach datatables via linq

Question

I am trying to do a LEFT OUTER here.

var row = from r0w1 in dt.AsEnumerable()
          join r0w2 in curdt.AsEnumerable()
          on r0w1.Field<string>("B") equals r0w2.Field<string>("cr_B")
          join r0w3 in tbmdt.AsEnumerable()
          on r0w1.Field<string>("B") equals r0w3.Field<string>("tb_B") into ps
          from r0w3 in ps.DefaultIfEmpty()
          select new string[] { serial_number++.ToString() }
          .Concat(r0w1.ItemArray.Concat
          (r0w2 != null ? r0w2.ItemArray.Skip(1) : new object[] { "", "", "", "" })
          .Concat(r0w3 != null ? r0w3.ItemArray.Skip(1) : new object[] { "", "", "", "" })).ToArray();

In the above query, I expect all rows to be from r0w1, but I get fewer rows. Is this query correct for LEFT OUTER JOIN?

Answer 1

I think the problem is this:

join r0w2 in curdt.AsEnumerable()
on r0w1.Field<string>("B") equals r0w2.Field<string>("cr_B")

This will be translated as a regular connection. I know that you are not asking about the syntax for the left join. But I would suggest using a different join syntax. Like this:

from r0w1 in dt.AsEnumerable()
from r0w2 in curdt.AsEnumerable()
    .Where(w=>w.Field<string>("cr_B")==r0w1.Field<string>("B")).DefaultIfEmpty()
from r0w3 in tbmdt.AsEnumerable()
    .Where(w=>w.Field<string>("tb_B")==r0w1.Field<string>("B")).DefaultIfEmpty()

This will both be translated as a left join.