`where` clause with direct boolean column does not work

[jwosty]

It would be nice if you could write the following (where is_latest is a boolean column):

selectAsync {
    for foo in someTable do
    where foo.is_latest
    select foo
    tryHead
}

instead, you have to manually check for true:

selectAsync {
    for foo in someTable do
    where (foo.is_latest = true)
    select foo
    tryHead
}

[JordanMarr]

I have had the same thought as well!

[JordanMarr]

Implementation started: https://github.com/JordanMarr/SqlHydra/compare/main...where-bool

• [x] bool true - where user.TermsAccepted

• [x] bool false - where (not user.TermsAccepted)

• [x] bool option true - where user.TermsAccepted.Value

• [x] bool option false - where (not user.TermsAccepted.Value)

• When negating a bool option: given that SqlHydra is intended to act as a strongly typed SQL DSL (and not an ORM), I think it should only check only for false (and not null). I think that user should be responsible for manually checking for null with an || clause if that is their intention.

    test "Where bool option is false" {
        let query =
            select {
                for o in table<OptionalBoolEntity> do
                where (not o.QuestionAnswered.Value)
            }

        let sql = query.ToKataQuery() |> toSql
        Expect.isTrue (sql.Contains("WHERE ([o].[QuestionAnswered] = cast(0 as bit))")) ""
    }

    test "Where bool option is false or null" {
        let query =
            select {
                for o in table<OptionalBoolEntity> do
                where (not o.QuestionAnswered.Value || o.QuestionAnswered = None)
            }

        let sql = query.ToKataQuery() |> toSql
        Expect.isTrue (sql.Contains("WHERE (([o].[QuestionAnswered] = cast(0 as bit)) OR ([o].[QuestionAnswered] IS NULL))")) ""
    }

[JordanMarr]

Released! https://github.com/JordanMarr/SqlHydra/releases/tag/v2.1.0

[jwosty]

I think the implementation sounds sane. If I were using a not on a bool option, I'm not actually sure what I would expect it to do, so I feel anything is fine. I would probably tend to be explicit if I wanted different behavior. It would probably be good to call out this edge case in the docs.

In an ideal world, it would arguably even emit a warning (which I'm assuming isn't really possible currently). Perhaps a SqlHydra analyzer would be useful when that SDK drops someday! Just a pipe dream :)