Retrieve Multiple result sets (UNION ALL) using SqlHydra.Query

[nkosi23]

Hello,

Thank you a lot for this awesome library! It makes transitioning from C# to F# as a daily driver much more reassuring. I am trying to figure out how to achieve something equivalent to the below pseudo-code using computation expression builders rather than raw SQL

open SqlHydra.Query

let query =
    """
        SELECT * FROM Customers
        UNION ALL
        SELECT * FROM Orders
    """

let resultSets =
    query
    |> selectTask

let firstResultSet = resultSets.First()
let secondResultSet = resultSets.Skip(1).First()

The real query of course contains where clauses. The reason I need this is that as we practice Domain Driven Design, we often need to retrieve aggregate roots (ie. an entity and all its depends entities).

Issuing multiple queries would be inefficient, we therefore need to retrieve everything in a single database calls, but since the tables for each subentity have a different schema, rather than tediously flattening the queries by listing every property in a giant select statement using leftJoin clauses that would be a maintenance nightmare, it would be much easier and more maintainable to simply issue multiple select statements and batch them in a single database round trip.

Crossing my fingers that SQLHydra has support for this. I just went through the source code of builders and tests and couldn't find anything related to resultsets and the SQL UNION statement. If we write store procedures instead, we'd be losing the type-safety / refactory conveniences made available by SQLHydra and this kind of friction even be a deal breaker for using F# since we rely on this pattern so heavily. Therefore, if SQLHydra does not have out of the box support for this, I'm looking for a workaround that would maximize developer convenience.

Thanks!

[JordanMarr]

Glad you are liking it!

The query builder currently doesn't have a UNION ALL feature. I would recommend that you keep the custom SQL query in this case and just use the generated HydraReader to handle the results:

let getTop10Products(conn: SqlConnection) = task {
    let sql = "SELECT TOP 10 * FROM Production.Product p"
    use cmd = new SqlCommand(sql, conn)
    use! reader = cmd.ExecuteReaderAsync()
    let hydra = HydraReader(reader)

    return [
        while reader.Read() do
            hydra.``Production.Product``.Read()
    ]
}

More examples here: https://github.com/JordanMarr/SqlHydra/blob/main/src/SampleApp/ReaderExample.fs

If you want make a bit more refactor safe, you could do this:

let query =
    $"""
        SELECT * FROM {nameof dbo.Customers}
        UNION ALL
        SELECT * FROM {nameof dbo.Orders}
    """