Difference in resulting SQL when using LoadWith and LeftJoin

[aarondk-dev]

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:

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?

[sdanyliv]

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:

var query = dc.GetTable<TableA>()
    .LoadWith(i => i.TableBJoin)
    .Where(i => Sql.AsNotNull(Si.TableBJoin.C1B) != 0);