Lazily retrieve data for lower memory footprint

[jhewlett]

For cases where you're pulling back lots of data, it would be nice if there was a way to get back typed Records lazily to avoid hydrating all the data into memory at once. I see that there's a DataReader option, which seems like it accomplishes the lazy part, but the data is raw at that point, and as far as I can tell it doesn't generate any record types or a mapping for that option.

Basically, I want something like MapRowValues:

https://github.com/demetrixbio/FSharp.Data.Npgsql/blob/master/src/Runtime/Utils.fs#L17-L23

But without calling Seq.toList like you do in ExecuteList:

https://github.com/demetrixbio/FSharp.Data.Npgsql/blob/master/src/Runtime/ISqlCommand.fs#L202

Is there currently a way to accomplish what I want by using MapRowValues in conjunction with the DataReader option?

If not, would you be opposed to having such support? I have a few prototypes that I've been working on locally, and I would be happy to attempt a PR if I can get approval and some input into the design.

[melanore]

Lazy retrieval of data would be definitely a desired feature. We used to return IEnumerable, however in case of Async it was causing undeterministic problems with cursor disposal (that could in some cases happen before sequence was fully enumerated). After discussions, it was decided to switch to materialized result set instead. For more details, please check this ticket - #32. We will also consider to switch to Enumerable/AsyncEnumerable at some point, however at the moment it's not a high priority problem.

Let me know if you have any other questions, and thanks for the prototypes!

[Swoorup]

Currently have a need for this. Willing to prototype a solution based on AsyncSeq
and have it turned on via a parameter. @melanore Any ideas you have in mind?

[kerams]

Do you have an example of what this would like in plain Npgsql?

[kerams]

It's really as simple as doing the reads in a sequence that is not enumerated in the library. I've implemented a prototype in my fork (behind a switch that lets you choose what kind of collection (list, array, resize array or lazy sequence) you want to get), where the returned sequence is wrapped in and used like

type LazySeq<'a> (s: 'a seq, reader: Common.DbDataReader, cmd: NpgsqlCommand) =
    member val Seq = s

    interface System.IDisposable with
        member _.Dispose () =
            reader.Dispose ()
            cmd.Dispose ()

[<Fact>]
let ``Disposing of the command does not dispose of the underlying Npgsql objects and LazySeq still works`` () =
    use cmd = DvdRentalWithTypeReuse.CreateCommand<"SELECT * from film", CollectionType = CollectionType.LazySeq>(connectionString)
    use actual = cmd.Execute()
    (cmd :> IDisposable).Dispose ()

    Assert.Equal (5, actual.Seq |> Seq.take 5 |> Seq.length)

[<Fact>]
let ``Disposing of the LazySeq disposes of the reader, but the provided connection stays open`` () =
    use conn = openConnection()
    use cmd = DvdRentalWithTypeReuse.CreateCommand<"SELECT * from film", CollectionType = CollectionType.LazySeq, XCtor = true>(conn)
    use actual = cmd.Execute()
    (actual :> IDisposable).Dispose ()

    Assert.Throws<ObjectDisposedException> (fun () -> actual.Seq |> Seq.take 5 |> Seq.length |> ignore) |> ignore
    Assert.Equal (System.Data.ConnectionState.Open, conn.State)

The caller then assumes all responsibility of resource disposal.

[kerams]

This is implemented with the same API as above.