CODEWITHSUNDEEP
c#linq
Harshit
Harshit

Reputation: 5157

Join Datatables via linq

I am trying to perform LEFT OUTER join 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 am expecting to have all the rows from r0w1 but I am getting less rows. Is this query correct for LEFT OUTER JOIN ?

Upvotes: 4

Views: 81

Answers (1)

Arion
Arion

Reputation: 31249

I think the problem is in this one:

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

This will be translated as a ordinary join. I know that you are not asking about the syntax for a left join. But I would suggest using a different syntax of the join. 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 left join

Upvotes: 4

Related Questions