Open MangelMaxime opened 2 weeks ago
There is not currently a built-in way to call SQL functions within a SqlHydra query.
However, it is possible to access and manipulate the underlying SqlKata query and create a manual SELECT
clause like this:
type CityRow = { City3: string; Number: int }
let getCities() = task {
use ctx = openContext()
let! reader =
select {
for a in Person.Address do
where (a.City |=| [ "Seattle"; "Denver" ])
kata (fun q -> q.SelectRaw("SUBSTRING(City, 1, 3) AS City3, 123 AS Number"))
}
|> ctx.GetReaderAsync
return
[
while reader.Read() do
{
City3 = reader.Get "City3"
Number = reader.Get "Number"
}
]
}
But you would need to get the results from the reader manually to accommodate the custom column names.
Whether or not that is better than just creating a query string and executing it manually via ADO.NET is up to you.
Thanks for the pointers I will give it a try.
Because, I have a lot of joins for some queries believe SqlHydra still makes senses especially because my client want a dynamic where
clause. For that where
close, the type safety added by SqlHydra can be worth exploring even with a manual reader.
It looks like I don't have any examples in the README that use the GetReader
method, so I should probably add this example.
Also, if you need dynamic where
clauses, you may want to add a whereIf
extension to allow you to pass a bool
argument to dynamically apply.
type SelectBuilder<'Selected, 'Mapped> with
/// Sets the WHERE condition if applyFilter is true.
[<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
member this.WhereIf (state: QuerySource<'T>, (applyFilter: bool), [<ProjectionParameter>] whereExpression) =
let query = (state :?> QuerySource<_, _>).Query
let querySource = QuerySource<'T, SqlKata.Query>(query, state.TableMappings)
if applyFilter
then this.Where(querySource, whereExpression)
else querySource
Usage:
let getMasterItems seriesId = async {
let! rows =
select' cf.OpenContext {
for mi in dbo.MASTER_ITEMS do
join msi in dbo.MASTER_SERIES_ITEMS on (mi.ITEM_NUMBER = msi.ITEM_NUMBER)
join ms in dbo.MASTER_SERIES on (msi.SERIES_ID = ms.SERIES_ID)
leftJoin fd in dbo.MASTER_FITTING_DEDUCTS on (mi.ITEM_NUMBER = fd.Value.ITEM_NUMBER)
whereIf (1 = 1) (
ms.SERIES_ID = seriesId &&
mi.ITEM_STATUS = Some "A" && // Is Active
mi.ITEM_CATEGORY.Value |=| [
"OPEN"; "EC"; "REC"; "EEC"; // "BFSC"; "WBC"
"SC"; "TRANSITION"; // "OFFSET"; "TEE"; "CROSS"
"IC"; "OC"; "45IC"; "45OC"; // "FL"
])
toList
}
This is a feature that was requested twice (#2 and #59), and for some reason I never added it. The extension method is convoluted enough that it should probably be built in.
I have added this example to the readme: https://github.com/JordanMarr/SqlHydra?tab=readme-ov-file#custom-sql-queries
Thanks for the example with the WhereIf
, in my case they want the where
clause to be generated based on an AST so it is a little more different so I just need to pass a normal function. :)
let dynamicCondition (mt : dbo.MemberTenant) =
// Here there will be a logic engine to transform a DSL/AST into actual F# code
if condition = true then
(mt.Id = Guid.NewGuid())
else
(mt.AccountId = Guid.NewGuid())
let data =
selectAsync HydraReader.Read (Create DbContext.create) {
for mt in dbo.MemberTenant do
where dynamicCondition mt
select mt.CompletedTimeStamp
tryHead
}
Ah ok.
Also, you can now pass the DbContext.create
function (or an existing db context) without wrapping it in the DU and it will be implicitly converted for you (see Select Builders for more info.):
let data =
selectAsync HydraReader.Read DbContext.create {
for mt in dbo.MemberTenant do
where dynamicCondition mt
select mt.CompletedTimeStamp
tryHead
}
@JordanMarr You can close this issue if you want depending on if you think calling native SQL functions should be supported or not.
In SQL, I want to write something like
Is it possible to do something similar with SqlHydra or to have access to its internal so the user can
extends
it if needed ?