Discussion on: Dirt simple SQL queries in F#

[Kasey Speakman]

Sure. You can manually construct the query to include the data as part of the query. That way, you deal with the types without boxing to construct the query. However, this is considered bad practice because it makes SQL Injection attacks possible.

For parameterized queries, this is currently not possible when using ADO.NET (which is used by Dapper). There is an open issue to use parameters without boxing. I'll have to see what implementation they come up with before I know whether/how it will complicate the functions I posted here.

In practice, I have not found the performance to be a problem... considering that IO is typically slower than boxing perf and there is a hard limit to the number of parameters (~2100). Are you just curious or did you have a specific use case where this was a problem?

[Vladimir Shchur]

It's generally better to avoid additional work for GC, but in this case fsharp type checking forces writing box even for reference types, which is inconvenient, as params are of this type Parameters : (string * obj) list. I have do this for both ints and strings:
[
p "paramInt" (box paramInt)
p "paramString" (box paramString)
]

[Kasey Speakman]

It is possible to avoid boxing in F#, but there is no point because the value is going to get boxed anyway by the database client (SqlParameter only takes objects as values). Avoiding boxing in the F# code will just bloat the code to no benefit.

[Vladimir Shchur]

I see, thank you for the clarifications

[Roland Andrag]

Hi Kasey, thank you for the useful post.

Is there any problem with or disadvantage to defining p as:
let p name value = (name, (box value))

Which allows you to just type
[ p "paramInt" paramInt ]

[Kasey Speakman]

Edit: I just re-read the preceding conversation and realized I answered the wrong question. Both yours and @lanayx previously.

No it is not a problem at all for the p helper to box the value, to avoid repeating box every time. And in fact that is exactly how I defined it in my own library. I believe it was just an oversight in the post. I will update the post.

Leaving the original response below.

---

I created this tiny helper due to a couple of factors.

1. ADO.NET boxes all parameters values to obj anyway
2. F# will not auto-upcast the values in the list, which can lead to type inference compile errors.

I recall there being an issue on a dotnet repo about having the option to not box sql parameters. But I am not sure if typed SQL parameters ever made it into a release.

So without the helper, the parameter list looked like this:

[
    "@SomeParam", box someParam
    "@OtherParam", box otherParam
]

Not terrible, but removing characters , box to add p on the front seemed a favorable trade.

That ended up being fortuitous because later, using a tuple was not quite enough. Sometimes it is necessary to declare the database type of the parameter. For example, when the value is null. (With Npgsql also when the column is jsonb.) So the parameter tuple became a full record type.

    type SqlParam =
        {
            Name : string
            Value : obj
            Type : DbType option
        }

p can be changed to work with this, and in a way that does not break existing defined queries. And it will be drastically shorter than spelling out the full SqlParam record.

    let p name value =
        {
            Name = name
            Value = value
            Type = None
        }


    let pTyped name value type_ =
        {
            Name = name
            Value = value
            Type = Some type_
        }

[Roland Andrag]

Hi Kasey, thank you for the quick and helpful reply.

Moving from the F# type providers to Dapper has been a real pleasure for me. For the price of giving up the compile time type checking, I now have simple reusable data transfer objects (DTOs) and functions to convert these from domain and back.

Best regards

Roland