LINQ Left Join On Not Equal Rows

Question

Im trying to display rows which does not exist from the other table using LINQ. can anyone help me?

Here is the sql im using.

select * from table1 
left join table2
on 
table1.col1 = table2.col1 
and 
table1.col2 = table2.col2
where
table2.col1 is null and table2.col2 is null

Already searched and found some solution. Here is what i did so far.

from t1 in table1
where 
!(from t2 in table1
  join t3 in table2 on
  new { t2.col1, t2.col2 } 
  equals 
  new { t3.col1, t3.col2 }
  select t2.PK).Contains(t1.PK)
  select t1

The above code works well but im just wondering if that is the only solution i can use? I mean, instead of using JOIN and CONTAINS method, can't we use left join linq directly with a where clause?

Answer 1

Well, you could do something like this:

var query = from t1 in table1
            join t2 in table2
            on new { t1.col1, t2.col2} equals { t2.col1, t2.col2 }
            into groups
            where !groups.Any()
            select t1;

Here, groups is the set of rows in t2 which match the "current" t1 - it will be empty if there aren't any groups, which is exactly what you want. The easiest way of checking whether a sequence is empty is to use the Any method.