Left Join on Data table with Where Clause using LinQ

Question

I am trying to perform a left join on two data tables using LinQ and there are two WHERE Clause in it. Here is a snippet of the code:

I have tried the below code snippet:

var resultList = (from p in mainlineQuery1.AsEnumerable()
                              join t in mainlineQuery4.AsEnumerable()//.Where(x => x.Field<string>("mainlineidc") == null)
                              on p.Field<string>("globalid") equals t.Field<string>("featureguid")                              
                              into tempJoin
                              from temp in tempJoin.DefaultIfEmpty()
                              where p.Field<string>("circuitid2") != null && temp.Field<string>("mainlineidc") == null
                              select new
                              {
                                  featClass = p.Field<string>("FeatureClass"),
                                  globalId = p.Field<string>("globalid"),
                                  circuitId = p.Field<string>("circuitid"),
                                  circuitId2 = p.Field<string>("circuitid2"),
                                  subtypecd = Convert.ToString(p.Field<decimal>("subtypecd")),
                                  operatingnumber = p.Field<string>("operatingnumber") //== string.Empty ? string.Empty : temp.Field<string>("mainlineidc")
                              }).ToList();

The number of records is not matching with the SQL Developer records.

Any help would be much appreciated!!

Answer 1

The problem was that your query referred to mainlineidc without defining it. Your

mainlineidc= temp == null ? null: temp.Field<string>("mainlineidc")

in the temp definition modifies the result set of the subquery so it will have a field of mainlineidc from now on, so you are no longer referring to undefined fields.