Provide data on request, lazy loading (equivalent of yield return)

Question

I have a stream of data records being loaded from database. I can't store and load all of them into memory because there are millions of them. The caller should process records one by one (of course I have no guarantee).

My first try was to return lazy sequence of IEnumerable<Records> which would be loaded on demand and returned by yield return statement.

But I couldn't use await/async (which was used to get data from a database) in this method because yield return requires a return type of IEnumerable<>. In a result I cannot use async and Task<IEnumerable<>>.

Reading this convinced me to try Reactive Extensions as I can await async methods and return IObservable<>.

But as far as I've figured out as soon as someone subscribes to my observable the method which pulls the data is being invoked and it will pull all the data at once.

This is how part of my method my method looks like:

IList<int> ids = (...);
return Observable.Create<NitemonkeyRegistration>(async obs => 
    {
        using (SqlDataReader reader = await command.ExecuteReaderAsync())
            {
                if (!reader.HasRows)
                    obs.OnCompleted();

                while (await reader.ReadAsync())
                    ids.Add(reader.GetInt32(reader.GetOrdinal("RegistrationId")));

                for (int i = 0; i < ids.Count; i += 1000)
                {
                    //heavy database operations
                    var registrations = await GetRegistrationsByIds(connection, ids.Skip(i).Take(1000));
                    foreach (var pulledReg in registrations)
                    {
                        obs.OnNext(pulledReg);
                    }

                }

            }
     });

Can I put the caller in control so when he calls .Next() on the observable then my code pulls the data on demand?

How can I implement something that is similar to yield return using reactive extensions?

UPDATE

This is my consumer code:

var cancellationTokenSource = new CancellationTokenSource();
await Observable.ForEachAsync<NitemonkeyRegistration>(niteMonkeySales, async (record, i) =>
            {
                try
                {
                     await SomethingAwaitableWhichCanTakeSeconds(record);
                }
                catch(Exception e)
                {
                    // add logging
                    // this cancels the loop but also the IObservable
                    cancellationTokenSource.Cancel();
                    // can't rethrow because line
                    // above will cause errored http response already created
                }
            }, cancellationTokenSource.Token);

Problem with this is that new records are being pushed not waiting for awaitable task to complete. I can do this with .Wait() and not async lambda but the thread will be wasted waiting for a lengthy network operation to complete.

Might be important: this is a ASP.NET WEB API service.

Answer 1

Rx allows describing "push sequences", where the producer pushes values to the observers. If your requirement is to "pull" values from the source, I think what you are looking for is the Interactive Extensions Async Library (check-out this Channel 9 Video). It defines the IAsyncEnumerable<T> type together with a full set of LINQ operators, which allows describing pull-based sequences with asynchronous behavior (the downside though is that yield return does not work with that type (yet at least), so you'll probably need to write your own IAsyncEnumerator<T> implementation).

Answer 2

Do you need to use reactive extensions?

Your first attempt might have been on the right track.

Have a look at this answer to another question.

The problem could be with the query rather than the client side code.

As noted in the linked question, you might have to rewrite the query to make sure it streams the data to the client properly.

---

Update:

You should try to break up GetRegistrationsById into two blocks.

1. Get the SqlDataReader to run the query. You can await this part.
2. Use the SqlDataReader returned and iterate through it using yield return.

Here is an example, which is loosely based on your code sample.

    IList<int> ids = new List<int>();

    private async void doWork()
    {
        var connection = new SqlConnection(...);
        connection.Open();

        SqlCommand command = new SqlCommand("SELECT registrationId FROM someTable", connection);

        using (SqlDataReader reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                ids.Add(reader.GetInt32(reader.GetOrdinal("RegistrationId")));
            }

            reader.Close();

            //heavy database operations
            // Part 1 of whatever GetRegistrationsByIds does would go into GetRegistrationReader().
            var registrationReader = await Task.Run(() => GetRegistrationReader(connection, ids));

            // Part 2 of whatever GetRegistrationsByIds does for each 
            // Registration would go into GetRegistrations().
            var registrationEnumerator = GetRegistrations(orderReader);

            foreach (var registration in registrationEnumerator)
            {
                // Do whatever you need to do for each registration
                listBox1.Items.Add(registration.Id);
            }

        }
    }

    private IEnumerable<Registration> GetRegistrations(SqlDataReader reader)
    {
        while (reader.Read())
        {
            // You would do whatever you need to do to each registration here.
            var registration = new Registration{ Id = reader.GetInt32(reader.GetOrdinal("RegistrationId")) };
            yield return registration;
        }

    }

    private SqlDataReader GetRegistrationReader(SqlConnection connection, IList<int> ints)
    {
        // Some query that returns a lot of rows.
        // Ideally it would written to stream directly from the 
        // database server, rather than buffer the data to the client
        // side.
        SqlCommand command = new SqlCommand("SELECT * from registrations", connection);

        return command.ExecuteReader();
    }

    internal class Registration
    {
        public int Id;
        // ... other fields, etc.
    }

Answer 3

Rx.NET doesn't have a lot of built-in backpressure operators currently.

Using something like TPL Dataflow is probabnly a better fit for your problem.

Anyway, I think you can use a BlockingCollection to limit the rate you pull from the database:

// maximum of 10 items in buffer
var buffer = new BlockingCollection<NitemonkeyRegistration>(10);
niteMonkeySales.Subscribe(t => buffer.Add(t), () => buffer.CompleteAdd());

foreach (var item in buffer.GetConsumingEnumerable())
{
    try
    {
         await SomethingAwaitableWhichCanTakeSeconds(record);
    }
    catch(Exception e)
    {
        // add logging
        // this cancels the loop but also the IObservable
        cancellationTokenSource.Cancel();
        // can't rethrow because line
        // above will cause errored http response already created
    }
}