How to make left external connections with DbLinq and SQLite?

Question

I tried

db.Table1.GroupJoin(db.Table2, t1 => t1.Id, t2 => t2.t1Id, (t1,g) => new { t1, g }) .Where(item => !item.g.Any()) .Select(item => item.t1); 

But he returned 0 results. I am trying to write something that generated the following SQL code (or something similar)

 SELECT t1.* FROM Table1 as t1 LEFT OUTER JOIN Table2 as t2 ON t1.Id = t2.t1Id WHERE IsNull(t2.Id); 

Answer 1

It seems like your desired result is to identify elements from Table1 that did not have results during the left outer join using Table2 .

In fluent notation, the approach will be similar to this:

 var query = db.Table1.GroupJoin(db.Table2, t1 => t1.Id, t2 => t2.t1Id, (t1, joined) => new { t1, joined } ) .SelectMany(r => r.joined.DefaultIfEmpty(), (r, j) => new { r.t1, j }) .Where(r => rj == null) .Select(r => r.t1); 

In the query syntax:

 var query = from t1 in db.Table1 join t2 in db.Table2 on t1.Id equals t2.t1Id into joined from j in joined.DefaultIfEmpty() where j == null select t1;