auto res = db(select(foo.name, foo.hasFun)
.from(foo)
.where(foo.id > 17 and foo.name.like("%bar%")));
this is code -> sql string -> exec -> code (mapping results)
best approach (ihmo) is using:
sql string -> exec -> results
with type for input/output directly generated sql string (and check for types)
like F# sql type provider ( http://fsprojects.github.io/FSharp.Data.SqlClient/ )
[<Literal>]
let query = "
SELECT TOP(@TopN) FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE CountryRegionName = @regionName AND SalesYTD > @salesMoreThan
ORDER BY SalesYTD
"
type SalesPersonQuery = SqlCommandProvider<query, connectionString>
let cmd = new SalesPersonQuery()
cmd.AsyncExecute(TopN = 3L, regionName = "United States", salesMoreThan = 1000000M)
|> Async.RunSynchronously
//output
//seq
// [("Pamela", "Ansman-Wolfe", 1352577.1325M);
// ("David", "Campbell", 1573012.9383M);
// ("Tete", "Mensa-Annan", 1576562.1966M)]
- without the need to define class for input / output
- without learn a new functions
- easy add database specific syntax
I've always hated how easy it is to push SQL bugs into production, since it's so often just a string literal stuck in your code. AFAIC you don't really need the typing help, you just need to know that you didn't forget a damn comma somewhere.
[0]: https://github.com/couchand/just-sql