Bad LINQ to SQL translation for filter on non-nullable boolean property

[thomaslevesque]

Describe the bug

LINQ queries with a condition on a non-nullable boolean property are not correctly translated to SQL. The translation doesn't account for the fact that the property could be undefined in the JSON document.

To Reproduce

Assuming an object model like this:

public class Item
{
    [JsonProperty("id")]
    public string Id { get; set; }

    public bool IsDeleted { get; set; }
}

Make the following query:

var query = client.CreateDocumentQuery<Item>(collectionUri).Where(i => !i.IsDeleted);

Expected behavior

Documents where IsDeleted is not defined should be returned, because in the C# model the property is not nullable, so its absence really means that it's false.

The generated SQL should be something like this:

SELECT * FROM root WHERE (NOT IS_DEFINED(root["IsDeleted"]) OR NOT root["IsDeleted"]) 

Or maybe

SELECT * FROM root WHERE (NOT (root["IsDeleted"] ?? false))

Actual behavior

Documents where IsDeleted is not defined are not returned, because NOT (undefined) doesn't evaluate to true.

The generated SQL looks like this:

SELECT * FROM root WHERE (NOT root["IsDeleted"])

Environment summary
SDK Version: .NET Core 2.1, Microsoft.Azure.DocumentDB.Core 2.1.3
OS Version (e.g. Windows, Linux, MacOSX): Windows 10

[thomaslevesque]

Up!

This issue is really annoying. It doesn't follow the semantics of C#. So instead of .Where(i => !i.IsDeleted), I have to write .Where(i => !i.IsDeleted.IsDefined() || !i.IsDeleted). For every single boolean property...

Basically, the translation for .Where(i => i.BooleanProperty) should be ... WHERE (root["BooleanProperty"] ?? false)