AsyncQueryableAdapterPrototype

Abstract

This is a prototype that enables to use the IAsyncQueryable type (System.Linq.Async.Queryable) with query providers and database drivers that do not support this natively.

The long story: Background and the problem solved

Querying a database is a long-lasting IO-operation that shall at best be done asynchronously to prevent blocking the current thread. Today's database drivers targeting .Net implement support for the IQueryable type. This type allows to record database queries by calling any of the LINQ methods, like Where, Select, OrderBy. When the instance is casted to IEnumerable, the recorded database query is executed. As this cast as well as the operations on the IEnumerable type are inherently synchronously, the database driver has no chance, but blocking the thread, even if it only allocated a database cursor, that is wrapped to implement the IEnumerable type.
Most database drivers implement custom methods to evaluate an IQueryable instance asynchronously, as well as some (or all) of the LINQ functions that return a scalar like int or bool. This however is highly dependent on the database driver used. This prevents to write the data access code in an independent fashion, that a database driver can be plugged into. For example EntityFramework Core implemented these functions in its EntityFrameworkQueryableExtensions, MongoDB defines its own interface that inherits from IQueryable and defined the mentioned functions on this interface in its type MongoQueryable. This makes casts to the database driver dependent type necessary, which makes it impossible to build an independent general purpose data access API using the type IQueryable.

How does this prototype solve the problem?

The user API

The solution, as implemented here builds on top of the IAsyncEnumerable and IAsyncQueryable types. These are the asynchronous equivalents of the IEnumerable and IQueryable types, respectively. The solution provides instances of types IAsyncQueryable that can be accessed for a database driver. The IAsyncQueryable instances make it possible to record a query (via generic LINQ method calls) and execute it in an asynchronous way. Asynchronous execution is done by either casting to IAsyncEnumerable or calling one of the non-chainable methods. The latter are the methods that return an awaitable type resulting in a scalar, like MaxAsync or AnyAsync.

What is needed for a database driver to support this?

To support a database driver in this scenario, the database driver needs at least support for LINQ queries via the IQueryable type. Additionally a database provider needs specific functionality to asynchronously evaluate a query returning a results of type IAsyncEnumerable. For better performance, there are several other functions that the database driver can implement, like MaxAsync or AnyAsync (See section Database driver binding for further information).

How does this work?

We provide the user a puppet instance of type IAsyncQueryable that LINQ methods can be called on. When the query is executed, the recorded query is translated to calls to LINQ methods on type IQueryable that the database driver understands and is executed via the database driver. The set of APIs defined on IAsyncQueryable is much broader than on type IQueryable. For example, it is possible to specify asynchronous predicates and selectors. It is therefore not possible to translate all possible queries. A query forms a tree of expressions, where the leafs of the tree are the raw sets of data. There may be one or multiple leaf nodes. The row sets of data are depending on the type of database and driver used. For example, they are tables for SQL database, documents for MongoDB databases. This tree is translated bottom up. When the translator is not able to translate a call, the already translated sub-tree is replaces with a special node, that performs the actual query execution on the subtree. The result of the tree is executed in-memory in an asynchronous post-processing. This can be disabled, via configuration. In this case an exception is thrown for all post-processors that are not obvious and clearly visible in the user-code, like ToListAsync. Methods that cannot be chained, like MaxAsync or AnyAsync need special attention and are translated in an optimized way.

Database driver binding

To implement support for a database driver, create a class that inherits from AsyncQueryableAdapter.QueryAdapterBase and implement at least the methods GetQueryable<T> and EvaluateAsync<T>. For better performance, implement any of the virtual methods that the database driver has native support for. For the virtual methods, where there is no override, a specialized polyfill is used.

Say you use MongoDB with the official C# driver. You want to use the IAsyncQueryable to build your data-access layer in a database independent fashion. Your MongoDB specific query-adapter may look like in the following snippet.

internal sealed class MongoDBQueryAdapter : QueryAdapterBase
{
    private readonly IMongoDatabase _database;

    public MongoDBQueryAdapter(IMongoDatabase database, AsyncQueryableOptions options) : base(options)
    {
        if (database is null)
            throw new ArgumentNullException(nameof(database));

        _database = database;
    }

    protected override IQueryable<T> GetQueryable<T>()
    {
        return _database.GetCollection<T>(nameof(T)).AsQueryable();
    }

    protected override async IAsyncEnumerable<T> EvaluateAsync<T>(
        IQueryable<T> queryable,
        [EnumeratorCancellation] CancellationToken cancellation)
    {
        var list = await (queryable as IMongoQueryable<T>)
            .ToListAsync(cancellation)
            .ConfigureAwait(false);

        foreach(var entry in list)
        {
            yield return entry;
        }
    }
}

Please note that we assume here, that the collection name is the same as the type name and that the implementation of the EvaluateAsync<T> method is not very optimized.

As the MongoDB driver has special support for some of the virtual operations defined on QueryAdapterBase, we can override these for an improvement in performance. This is done in the following snippet exemplary for the MinAsync methods.

internal sealed class MongoDBQueryAdapter : QueryAdapterBase
{
    // Other (existing) members omitted for brevity.
    // [...]

    protected override ValueTask<TResult> MinAsync<TSource, TResult>(
        IQueryable<TSource> source, 
        Expression<Func<TSource, TResult>> selector, 
        CancellationToken cancellation)
    {
        return new ValueTask<TResult>(
            (source as IMongoQueryable<TSource>).MinAsync(selector, cancellation));
    }

    protected override ValueTask<TSource> MinAsync<TSource>(
        IQueryable<TSource> source, 
        CancellationToken cancellation)
    {
        return new ValueTask<TSource>(
            (source as IMongoQueryable<TSource>).MinAsync(cancellation));
    }

    // Override all the other virtual members that the database driver has special support for.
    // [...]
}

How to use

When there is a query adapter for the database driver that you use, it can be used to access an instance (or multiple instances) of type IAsyncQueryable via a call to the method GetAsyncQueryable<T>(). The result can be used to perform custom queries. This is demonstrated in the following sample using the MongoDB specific implementation from above.

static async Task Main() 
{
    var database = ... // Get your MongoDB database instance via the driver

    // Build the database driver-specific query adapter
    var queryAdapter = new MongoDBQueryAdapter(database, AsyncQueryableOptions.Default);

    // Create the generic data access layer
    var dal = new DataAccessLayer(queryAdapter);

    var names = dal.GetOrderedMajorPersonNamesAsync();

    await foreach(var name in names)
    {
        Console.WriteLine(name);
    }
}

sealed class DataAccessLayer
{
    private readonly QueryAdapterBase _queryAdapter;

    public DataAccessLayer(QueryAdapterBase queryAdapter)
    {
        if (queryAdapter is null)
            throw new ArgumentNullException(nameof(queryAdapter));

        _queryAdapter = queryAdapter;
    }

    private const int MAJOR_MIN_AGE = 18;

    public IAsyncEnumerable<string> GetOrderedMajorPersonNamesAsync()
    {
        // Access the async query-able from the query adapter
        var queryable = _queryAdapter.GetAsyncQueryable<Person>();

        // Record the LINQ query
        var result = from person in queryable
                     where person.Age >= MAJOR_MIN_AGE
                     select person.Name;

        // Execute query
        // Result can be cast to IAsyncEnumerable<string> implicitly
        return result;
    }
}

record Person(string Name, int Age);

Please note, that the data access layer is generic and does not in any way depend on the database driver used.

Feature roadmap

This is a prototype. It is not feature complete. There are currently no releases, as this is a work in progress and in no way near to feature complete. Please do not use in productive code.

CatoLeanTruetschel/AsyncQueryableAdapterPrototype