Left Outer Join in Linq not working

Question

I'm trying to do a left outer join in Linq but the below code is not working

 var result = from dataRows1 in agdt.AsEnumerable()
              join dataRows2 in hwt.AsEnumerable()
              on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("HWID") 
              where ((dataRows2.Field<string>("HWID") == null) && 
                    (dataRows1.Field<string>("TYPE")=="a"))
              select dataRows1;

Without the where clauses I receive about 37000 rows and with it I receieve 0. The agdt table has 12000 rows and the hwt table has 6000. This is getting very frustrating. Can someone please help?

Hubert, thats not making any difference. Almo, here is the test row that I am inserting. The table structure is the same for both tables: DataRow dr = agdt.NewRow(); dr["ID"] = "atest"; dr["TYPE"] = "a"; dr["TITLE"] = "outlier"; dr["URL"] = "url" ; dr["STAMP"] = changestamp; agdt.Rows.Add(dr); — jason, Aug 28 '15 at 20:26

score 1 · Accepted Answer · answered Aug 28 '15 at 20:28

You are missing the DefaultIfEmpty method call.

From what I understand from your query, it should look something like:

var result = from dataRows1 in agdt.AsEnumerable()
          join dataRows2 in hwt.AsEnumerable()
          on dataRows1.Field<string>("ID") equals dataRows2.Field<string>("HWID") 
          into groupJoin
          from leftOuterJoinedTable in groupJoin.DefaultIfEmpty()
          where (leftOuterJoinedTable == null && 
                (dataRows1.Field<string>("TYPE")=="a"))
          select dataRows1;

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

It seems to me that this in essence would be the same as running the following SQL query

SELECT DR1.* FROM DataRows1 DR1 INNER JOIN DataRows2 DR2 ON DR1.ID=DR2.HWID WHERE DR2.HWID IS NULL AND DR1.Type='a'

Essentially your LINQ is doing an inner join and then executing the where. To truly do a left join, see the link

LEFT OUTER JOIN in LINQ

edited May 23 '17 at 12:22

Community

1
1

answered Aug 28 '15 at 20:30

Cityonhill93

83
2
9

Left Outer Join in Linq not working

2 Answers2