Efcore: UseRelationalNulls does not work with SELECT conditions

[RoshanJeewantha]

I observed a behavioural inconsistency of UseRelationalNulls method when used with Linq WHERE and SELECT conditions. It seems UseRelationalNulls works correctly with WHERE conditions, but has no impact on conditional SELECTs, which leads to incorrect results.

Steps to reproduce

Repro included at https://github.com/avinash-phaniraj-readify/EFCoreTestBed/tree/UseRelationalNullsBehaviourInconsistency

Call UseRelationalNulls:
```c#
new DbContextOptionsBuilder().UseSqlCe(ceConnection, x => x.UseRelationalNulls(true))

WHERE condition:
```c#
from e in employees
join ed in employeeDevices on e.Id equals ed.EmployeeId into x
from y in x.DefaultIfEmpty()
where y.EmployeeId != 0
select y.Device

Generated Sql:

SELECT [ed].[Device]
FROM [Employee] AS [e]
LEFT JOIN [EmployeeDevice] AS [ed] ON [e].[Id] = [ed].[EmployeeId]
WHERE [ed].[EmployeeId] <> 0 --UseRelationalNulls removed OR [ed].[EmployeeId] IS NULL clause

SELECT condition:
```c#
from e in employees
join ed in employeeDevices on e.Id equals ed.EmployeeId into x
from y in x.DefaultIfEmpty()
select y.EmployeeId != 0 ? y.Device : "n/a"

Generated Sql:
```sql
SELECT CASE
     WHEN ([ed].[EmployeeId] <> 0) OR [ed].[EmployeeId] IS NULL --UseRelationalNulls did not remove OR [ed].[EmployeeId] IS NULL clause
     THEN [ed].[Device] ELSE N'n/a'
END
FROM [Employee] AS [e]
LEFT JOIN [EmployeeDevice] AS [ed] ON [e].[Id] = [ed].[EmployeeId]

This brings null as y.Device for employees who do not have a device.

Further technical details

EF Core version: 2.1.3
Database Provider: EntityFrameworkCore.SqlServerCompact35
Operating system: Windows 10.0.17134
IDE: Visual Studio 2017 15.8.0

[ErikEJ]

Have you tested with the SQL Server provider?

[RoshanJeewantha]

Yes.

I verified with the following providers which showed the same behaviour,

• EntityFrameworkCore.SqlServerCompact35 - 2.1.0.5
• Microsoft.EntityFrameworkCore.SqlServer - 2.1.3

[ajcvickers]

Clearing milestone to consider for patch. (🇦🇺)

[divega]

@maumar this is now assigned to you, and it is possible patch issue.

[ajcvickers]

@maumar Heard back from 🇦🇺. Can you investigate for patch? In particular, how risky it would be.

[maumar]

Risk assessment: Fix is relatively easy and isolated as far as query issues are concerned.
However, it changes the result of many queries and could potentially break many applications that were using relational null semantics. To mitigate that we can/should disable it by default and allow customers who want to take advantage of the fix opt-in using AppContext "quirk"

[ajcvickers]

This is fixed for the 2.2.3 release, but the new behavior is off by default. To enable the new behavior use, set the app switch for "Microsoft.EntityFrameworkCore.Issue13285".

The behavior will be enabled by default (and the switch removed) in the 3.0 release.

[RoshanJeewantha]

Thank you. :)

[AlexChongMicrosoft]

Hi, is this fix added into EF core 2.1 which is a LTS version?

[optiks]

It only went into 2.2, not 2.1.

[AlexChongMicrosoft]

It only went into 2.2, not 2.1.

@optiks Thank you for attention. But as I know, .NET core 2.1 is a Long-Term Support release which chose by many users. Is there a plan to fix it in 2.1? Or this is a new feature, which will not be fixed in 2.1?

[optiks]

But as I know, .NET core 2.1 is a Long-Term Support release which chose by many users. Is there a plan to fix it in 2.1? Or this is a new feature, which will not be fixed in 2.1?

I'll let the EF team reply with whether this is going to be fixed in 2.1 (although I suspect the answer is no, unless there is sufficient demand for it)... however a general rule of thumb is that not every bug fix is back-ported to the LTS release. Part of the reason it's LTS is _because_ it needs to be stable; every change that's made introduces the possibility of a regression.

[AlexChongMicrosoft]

But as I know, .NET core 2.1 is a Long-Term Support release which chose by many users. Is there a plan to fix it in 2.1? Or this is a new feature, which will not be fixed in 2.1?

I'll let the EF team reply with whether this is going to be fixed in 2.1 (although I suspect the answer is no, unless there is sufficient demand for it)... however a general rule of thumb is that not every bug fix is back-ported to the LTS release. Part of the reason it's LTS is _because_ it needs to be stable; every change that's made introduces the possibility of a regression.

Yes, thank you for your explanation. I'm also contacting asp.net team for help while waiting for your feedback. Actually, we have customers who have strong demand for it. I will keep my eyes on this ticket. Waiting for your feedback.

Really appreciate your warm hand.

[AlexChongMicrosoft]

But as I know, .NET core 2.1 is a Long-Term Support release which chose by many users. Is there a plan to fix it in 2.1? Or this is a new feature, which will not be fixed in 2.1?

I'll let the EF team reply with whether this is going to be fixed in 2.1 (although I suspect the answer is no, unless there is sufficient demand for it)... however a general rule of thumb is that not every bug fix is back-ported to the LTS release. Part of the reason it's LTS is _because_ it needs to be stable; every change that's made introduces the possibility of a regression.

Hi, Micheal. How are you? Is there any progress?