Can't query table with optional key

[tforkmann]

Hi guys,

I'm trying to query following statement.

let enmsSichtKstEnergiezentrale = table<EnmsSicht_KstEnergiezentrale>
let enmsSichtKstErzeuger = table<EnmsSicht_KstErzeuger>
let! plants =
                select {
                    for ez in enmsSichtKstEnergiezentrale do
                    join erz in enmsSichtKstErzeuger on (ez.KstStelle = erz.USER_KstUebergeordnet.Value)
                    orderBy ez.KstStelle
                }
                |> conn.SelectAsync<EnmsSicht_KstEnergiezentrale,EnmsSicht_KstErzeuger>

Sadly I run into following error: Could not get data "The given key was not present in the dictionary."

I have to join on a optional key. In this case erz.USER_KstUebergeordnet.Value.

Do you guys have an idea what could be the reason for that error message?

Thanks,

Tim

[JordanMarr]

It looks like you are joining on an Option property. Try this: on (Some ez.KstStelle = erz.USER_KstUebergeordnet)

[tforkmann]

I now get following error message:

Could not get data "The method or operation is not implemented.

[tforkmann]

I guess that error message comes from here: https://github.com/Dzoukr/Dapper.FSharp/blob/aa89bd0f311d177472e61f9ad1c1db21475f1fc4/src/Dapper.FSharp/LinqExpressionVisitors.fs#L7

[JordanMarr]

Ah ok, you found a bug then. the bug is that Option types are only unwrapped in the where statement expressions. It should be a pretty easy fix though.

[JordanMarr]

Incoming PR...

[Dzoukr]

Fixed in v2.0.1 thanks to @JordanMarr 👏

[tforkmann]

Ahh thanks. Just wanted to sent a PR in as well^^ But yours is even better

[tforkmann]

Awesome works! Thanks for the help!

[JordanMarr]

Ahh thanks. Just wanted to sent a PR in as well^^ But yours is even better

Well it just occurred to me that group by probably has the same problem, so feel free to a unit test for groupBy and groupByMany that takes an Option type.

[JordanMarr]

Actually there is no groupByMany, just groupBy which can take a tuple, and it does indeed need the same fix if you’re up for it. (Let me know).

Also the xml doc comment for groupBy is wrong on the select builder.

[JordanMarr]

FYI, I just checked and groupBy worked fine with option types (I added a new unit test to make sure). So no other fix is needed.