How to do left outer joins 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 it returned 0 results. I'm 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 looks like your desired result is to identify the items from Table1 that had no results during the left outer join with Table2.

In fluent notation, the approach would 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 => r.j == null)
            .Select(r => r.t1);

In 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;