You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I encountered a difference in the SQL generated when using LeftJoin or LoadWith while I would expect the same output. I have created a sample repository that you can use to reproduce this problem (with an in-memory Sqlite database) but the issue also exist when using a Db2 database. Link to repository.
Let's say you have two tables: Table A with columns C1A and C2A and Table B with columns C1B and C2B. I can write the query like this:
var query = dc.GetTable<TableA>()
.LeftJoin(dc.GetTable<TableB>(),
(JOIN, B) => JOIN.C2A == B.C1B,
(JOIN, B) => new { JOIN, B })
.Where(i => i.B.C1B != 0);
var results = await query.ToListAsync();
Or like this:
var query = dc.GetTable<TableA>()
.LoadWith(i => i.TableBJoin)
.Where(i => i.TableBJoin.C1B != 0);
var results = await query.ToListAsync();
The first Linq2Db query generates:
SELECT
[i].[C1A],
[i].[C2A],
[B_1].[C1B],
[B_1].[C2B]
FROM
[A] [i]
LEFT JOIN [B] [B_1] ON [i].[C2A] = [B_1].[C1B]
WHERE
[B_1].[C1B] <> 0
The second one:
SELECT
[i].[C1A],
[i].[C2A],
[a_TableBJoin].[C1B],
[a_TableBJoin].[C2B]
FROM
[A] [i]
LEFT JOIN [B] [a_TableBJoin] ON [i].[C2A] = [a_TableBJoin].[C1B]
WHERE
([a_TableBJoin].[C1B] <> 0 OR [a_TableBJoin].[C1B] IS NULL)
The difference specifically occurs in the where clause: OR [a_TableBJoin].[C1B] IS NULL). This will potentially give different results. The example repository proves this.
I understand this is a weird query to write because why wouldn't you use an inner join to get the same effect. But still I think the behavior should be consistent.
Affected versions
4.0 => No problem
4.2.0 => No problem
4.3.0 => No problem
>= 4.4.1 => Problem
Environment details
Linq To DB version: From 4.4.1 upwards
Database (with version): Sqlite and Db2
ADO.NET Provider (with version): IBM.Data.Db2 and System.Data.SQlite
Operating system: Windows 11
.NET Version: .NET Framework 4.8
Questions
Is this wanted behavior?
If yes, why?
The text was updated successfully, but these errors were encountered:
It should be fixed with upcoming Version 6 release. Current code has limitation in detecting columns nullablity. Actually second one is the righ SQL [a_TableBJoin].[C1B] is nullable because of LEFT JOIN.
If you don't want to let linq2db generating such queries, you can disable it globally
Configuration.Linq.CompareNullsAsValues =false;
Or via DataOptions (starting from Version 5)
For particular query you can force linq2db to treat nullable as not nullable by Sql.AsNotNull or Sql.ToNotNull:
Steps to reproduce
I encountered a difference in the SQL generated when using LeftJoin or LoadWith while I would expect the same output. I have created a sample repository that you can use to reproduce this problem (with an in-memory Sqlite database) but the issue also exist when using a Db2 database. Link to repository.
Let's say you have two tables: Table A with columns C1A and C2A and Table B with columns C1B and C2B. I can write the query like this:
Or like this:
The first Linq2Db query generates:
The second one:
The difference specifically occurs in the where clause: OR [a_TableBJoin].[C1B] IS NULL). This will potentially give different results. The example repository proves this.
I understand this is a weird query to write because why wouldn't you use an inner join to get the same effect. But still I think the behavior should be consistent.
Affected versions
Environment details
Linq To DB
version: From 4.4.1 upwardsDatabase (with version): Sqlite and Db2
ADO.NET Provider (with version): IBM.Data.Db2 and System.Data.SQlite
Operating system: Windows 11
.NET Version: .NET Framework 4.8
Questions
The text was updated successfully, but these errors were encountered: