Nested linq query problem

[dilbert]

The following linq query throws an exception:

Code: Select all

from t1 in DB.Table1
from t2 in DB.Table2.Where(t2 => t2.Id_user == t1.Id_user && t2.Table3.Active == 1).DefaultIfEmpty()
where t2.Id_user == null
select t1.Id_user;

Devart.Data.SQLite.SQLiteException:

Code: Select all

SQLite error
no such column: t2.Id_user

SQL generated:

Code: Select all

SELECT t1.Id_user AS Id_user
FROM "main".table1 t1
LEFT OUTER JOIN ("main".table2 t2
    INNER JOIN "main".table3 t3 ON (t2.Id = t3.Id) OR ((t2.Id IS NULL) AND (t3.Id IS NULL))) ON (t2.Id_user = t1.Id_user) AND (t3.Active = 1)
WHERE t2.Id_user IS NULL

This exception happens in all Sqlite dotConnect versions (3.x and 4.x).

[MariiaI]

We have reproduced this issue. We will investigate it and notify you about the results as soon as possible.
As a workaround, you could try using the join directly, for example, try re-writing your query as follows:

Code: Select all

var q = from t1 in DB.Table1
      from t2 in DB.Table2.Where(t2 => t2.Id_user == t1.Id_user).DefaultIfEmpty()
      join t3 in DB.Table3 on t2.Some_id equals t3.Some_id
      where t2.Id_user == null && t3.Active == 1
      select t1.Id_user;

In this case, the query is generated correctly. Please let us know whether it helped you.

[dilbert]

Thank you for suggested workaround.
Actually, I've already used one similar to this. However it's nearly impossible to remember all these workarounds all the time. That is why I reported it.